RETRIEVING AN OBJECT’S DDL STATEMENT AND USER GRANTS
QUESTIONS
How to extract a DDL for any object in a database?
How to get an object definition?
How to get a user or an object DDL using the DBMS_METADATA package?
REQUEST
RELATED
TESTCASE
To begin with I’ll create a user for this test case and grant privileges on several SYS objects to it
CREATE USER TESTCASE IDENTIFIED BY TESTCASE DEFAULT TABLESPACE USERS;
GRANT CONNECT, RESOURCE TO TESTCASE;
GRANT CREATE ANY DIRECTORY TO TESTCASE;
GRANT UNLIMITED TABLESPACE TO TESTCASE;
GRANT SELECT ON V_$SESSION TO TESTCASE;
GRANT SELECT ON V_$SESSION_EVENT TO TESTCASE;
Connect as a new user and create some simple objects
SQL> CONNECT TESTCASE/TESTCASE
Connected.
SQL> SHO USER
USER is "TESTCASE"
CREATE TABLE MYTABLE(DIGIT NUMBER, TEXT VARCHAR2(10));
INSERT INTO MYTABLE VALUES (1,'One');
INSERT INTO MYTABLE VALUES (2,'Two');
INSERT INTO MYTABLE VALUES (3,'Three');
COMMIT;
COL DIGIT FOR 99999
COL TEXT FOR A5
SELECT * FROM MYTABLE;
DIGIT TEXT
------ -----
1 One
2 Two
3 Three
CREATE INDEX MYINDEX ON MYTABLE(DIGIT);
CREATE OR REPLACE DIRECTORY MYDIRECTORY AS '/tmp';
At this point there are 3 manually created objects in the database. But before retrieving any object definitions, it is necessary to customize the package output. By default the output of this package is too verbose and the next piece of code will modify output in the way It will be nice formatted and can be used to recreate an object
SET PAGES 0
SET LINES 3000
SET LONG 2000000000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SET SERVEROUTPUT ON SIZE 1000000
COLUMN TXT FORMAT a2500 WORD_WRAPPED
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
Now let’s get ddl statements of 3 objects by get_ddl procedure
DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
Issue the following queries under SYS user to get the objects DDL’s statements
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','MYTABLE','TESTCASE') AS TXT FROM DUAL;
CREATE TABLE "TESTCASE"."MYTABLE" ("DIGIT" NUMBER, "TEXT" VARCHAR2(10)) ;
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','MYINDEX','TESTCASE') AS TXT FROM DUAL;
CREATE INDEX "TESTCASE"."MYINDEX" ON "TESTCASE"."MYTABLE" ("DIGIT") ;
SQL> SELECT DBMS_METADATA.GET_DDL('DIRECTORY','MYDIRECTORY') AS TXT FROM DUAL;
CREATE OR REPLACE DIRECTORY "MYDIRECTORY" AS '/tmp';
As a next step I retrieve all grants that user has (object, system, role)
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','TESTCASE') AS TXT FROM DUAL;
GRANT UNLIMITED TABLESPACE TO "TESTCASE";
GRANT CREATE ANY DIRECTORY TO "TESTCASE";
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','TESTCASE') AS TXT FROM DUAL;
GRANT SELECT ON "SYS"."V_$SESSION" TO "TESTCASE";
GRANT SELECT ON "SYS"."V_$SESSION_EVENT" TO "TESTCASE";
GRANT EXECUTE ON DIRECTORY "MYDIRECTORY" TO "TESTCASE" WITH GRANT OPTION;
GRANT READ ON DIRECTORY "MYDIRECTORY" TO "TESTCASE" WITH GRANT OPTION;
GRANT WRITE ON DIRECTORY "MYDIRECTORY" TO "TESTCASE" WITH GRANT OPTION;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','TESTCASE') AS TXT FROM DUAL;
GRANT "CONNECT" TO "TESTCASE";
GRANT "RESOURCE" TO "TESTCASE";
As you can see objects DDL statements and grants are the same as it was issued before.
This package also retrieves object DDL statements for other object types like user, tablespace, db_links, constraints and so on. To know a full list of all supported objects, please refer to the official documentation
SQL> SELECT DBMS_METADATA.GET_DDL('USER','TESTCASE') AS TXT FROM DUAL;
CREATE USER "TESTCASE" IDENTIFIED BY VALUES 'S:91DE979C538FC1E4AF7578DECD5D74D03C8E942454EBEA8341D416655697;T:544CD8A7EFA8B1D080B4A07892F7207587B0A4BFBD59B1F945F0F8D5A41FF76CB00F1EACF0ACF04742C5E69A3A2265AB93FB4B0F4C15407D8F06E4C08C352C7E2CF57B9CC0C50A660D926555AF3E4B20' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);
SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','USERS') AS TXT FROM DUAL;
CREATE TABLESPACE "USERS" DATAFILE
'/oracle/oradata/DBPILOT/users01.dbf' SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
Drop testcase user
SQL> DROP USER TESTCASE CASCADE;
User dropped.
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0
Tags In
Related Posts
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (63)
- Account (2)
- AWR (2)
- Database Errors (6)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Privileges (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)