Table of Contents

Oracle Database - Table

About

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

Alter

Column

alter table MyTable add (MyColumnName VARCHAR(60) DEFAULT 'My Default Value' NOT NULL)

alter table MyTable modify (MyColumnName VARCHAR(60) DEFAULT 'My Default Value' NOT NULL)

Create a table

Create Table

tablespace

ALTER TABLE TABLE_NAME MOVE TABLESPACE TABLESPACE_NAME

Grants

Oracle Database - Grant with PL/SQL - DBMS_METADATA

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"

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";