Oracle Database - Table

> Database > Oracle Database

1 - About

A table name must follow the naming convention of database object.

Advertising

3 - Alter

3.1 - Column

  • Add
ALTER TABLE MyTable ADD (MyColumnName VARCHAR(60) DEFAULT 'My Default Value' NOT NULL)
  • DataType
ALTER TABLE MyTable MODIFY (MyColumnName VARCHAR(60) DEFAULT 'My Default Value' NOT NULL)

3.2 - Create a table

3.3 - tablespace

ALTER TABLE TABLE_NAME MOVE TABLESPACE TABLESPACE_NAME

3.4 - Grants

3.4.1 - GET_DEPENDENT_DDL

SELECT 
    DBMS_METADATA.GET_DEPENDENT_DDL(
          'OBJECT_GRANT',
          'TABLE_NAME',
          'OWNER_NAME')
    FROM DUAL;

Example:

GRANT SELECT ON "OWNER_NAME"."TABLE_NAME" TO "REPORT_ADMINS"
GRANT SELECT ON "OWNER_NAME"."TABLE_NAME" TO "SHAREPOINT"
Advertising

3.4.2 - OPEN, TRANSFORM, FETCH

SET serveroutput ON;
DECLARE
v_handle NUMBER;
v_transform_handle NUMBER;
-- CREATE TYPE sys.ku$_ddls IS TABLE OF sys.ku$_ddl
-- CREATE TYPE sys.ku$_ddl AS OBJECT (
--     ddlText        CLOB,
--     parsedItem     sys.ku$_parsed_items )
v_ddls  sys.ku$_ddls;
v_ddl   sys.ku$_ddl;
BEGIN
  v_handle := DBMS_METADATA.OPEN ('OBJECT_GRANT');
  v_transform_handle := DBMS_METADATA.ADD_TRANSFORM(v_handle, 'DDL');
  DBMS_METADATA.SET_TRANSFORM_PARAM(v_transform_handle,'PRETTY', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(v_transform_handle,'SQLTERMINATOR',TRUE);
  DBMS_METADATA.SET_FILTER(v_handle, 'BASE_OBJECT_NAME', 'TABLE_NAME' );
  DBMS_METADATA.SET_FILTER(v_handle, 'BASE_OBJECT_SCHEMA', 'OWNER_NAME' );
  DBMS_METADATA.SET_COUNT (v_handle, 10); -- 10 object by call to fetch
  v_ddls := DBMS_METADATA.FETCH_DDL(v_handle);
  WHILE (v_ddls IS NOT NULL) 
  LOOP
    FOR indx IN 1 .. v_ddls.COUNT
    LOOP
       v_ddl := v_ddls(indx);
      DBMS_OUTPUT.put_line ('Output: ' || v_ddl.ddlText);
    END LOOP;
    v_ddls := DBMS_METADATA.FETCH_DDL(v_handle);
    -- The FETCH_DDL function returns the DDL (to create the object) 
    -- in a sys.ku$_ddls nested table. 
    -- Each row of the sys.ku$_ddls nested table contains a single DDL statement in the ddlText column; 
    -- if requested, parsed items for the DDL statement will be returned in the parsedItems column. 
  END LOOP;
  DBMS_METADATA.CLOSE (v_handle);
END;
/
PL/SQL procedure successfully completed.
Output: GRANT SELECT ON "OWNER_NAME"."TABLE_NAME" TO "REPORT_ADMINS";
Output: GRANT SELECT ON "OWNER_NAME"."TABLE_NAME" TO "SHAREPOINT";