About
A table name must follow the naming convention of database object.
Articles Related
Alter
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)
Create a table
tablespace
ALTER TABLE TABLE_NAME MOVE TABLESPACE TABLESPACE_NAME
Grants
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";