DDL Commands


Sl. No.  Task         Command
1. TABLE DDL     select dbms_metadata.get_ddl('TABLE','&TABLE_NAME','&OWNER') from dual;
2. INDEX DDLselect dbms_metadata.get_ddl('INDEX','&INDEX_NAME','&OWNER') from dual;
3.VIEW DDL select dbms_metadata.get_ddl('VIEW','&VIEW_NAME','&OWNER') from dual;    
4.SYNONYM DDLselect dbms_metadata.get_ddl('SYNONYM','&SYNONYM_NAME','&OWNER') from dual;
5.  DB LINK DDLselect dbms_metadata.get_ddl('DB_LINK','&DB_LINK_NAME','&OWNER') from dual;
6.USER DDLselect dbms_metadata.get_ddl('USER','&USERNAME') from dual;
7.TABLESPACE DDLselect dbms_metadata.get_ddl('TABLESPACE','&TABLESPACENAME') from dual;
8.PROCEDURE DDLselect dbms_metadata.get_ddl('PROCEDURE','&PROCEDURE_NAME','&OWNER') from dual;
9.SEQUENCE DDLselect dbms_metadata.get_ddl('SEQUENCE','&SEQUENCE_NAME','&OWNER') from dual;
10.TRIGGER DDLselect dbms_metadata.get_ddl('TRIGGER','&TRIGGER_NAME','&OWNER') from dual;
11.CONSTRAINTT DDLselect dbms_metadata.get_ddl('CONSTRAINT','&CONSTRAINT_NAME','&OWNER') from dual;
12.SYSTEM GRANTS DDLselect dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&USERNAME') from dual;
13.OBJECT GRANTS DDLselect dbms_metadata.get_granted_ddl('OBJECT_GRANT','&USERNAME') from dual;
14.ROLE GRANTS DDLselect dbms_metadata.get_granted_ddl('ROLE_GRANT','&USERNAME') from dual;

Comments

Post a Comment

Popular posts from this blog

 Exadata  Oracle Exadata Smart Flash Cache * Exadata cell -> Flash LUN -> Cell Disk -> Flash Cache/Flash Log  * CELL_FLASH_CACHE - – DEFAULT (LRU), KEEP, NONE * ALTER TABLE <TABLE_NAME> STORAGE(FLASH_CACHE [NONE|DEFAULT|KEEP]) * ALTER INDEX <INDEX NAME> STORAGE(FLASH_CACHE [NONE|DEFAULT|KEEP]) Smart Flash Cache Mode: 1. Write Through Flash Cache Mode (Default):  Write on Disk -> Copy to Flash Cache  * excellent for absorbing repeated random reads  * commonly found in OLTP applications. * dcli -g cell_servers -l celladmin cellcli -e "list cell attributes flashcachemode" * Steps to enable Write-Through Mode CellCLI> ALTER FLASHCACHE ALL FLUSH CellCLI> DROP FLASHCACHE CellCLI> ALTER CELL SHUTDOWN SERVICES CELLSRV CellCLI> ALTER CELL flashCacheMode=WriteThrough CellCLI> ALTER CELL STARTUP SERVICES CELLSRV CellCLI> CREATE FLASHCACHE ALL 2. Write Back Flash Cache Mode:  Write on Flash -> Copy to Disk  * best ...
Read more

Database Network : Configuration Parameters

LSNRCTL> command [listener_name] lsnrctl @file_name  #to execute in batch mode. REM or # to identify comments in the batch script Listener.ora file parameters: ·          PASSWORDS_listener_name  #an unencrypted password ·          LSNRCTL> change_password #to establish an encrypted password ·          LSNRCTL> SAVE_CONFIG  #Any changes are stored in listener.ora ·          LSNRCTL> SET PASSWORD  #If a password is set, then issue SET PASSWORD command before changing the password. Use the command SET PASSWORD prior to privileged Listener Control utility commands ·          Reload : the database services, instances, service handlers, and listening endpoints that were dynamically registered with the listener will be unregistered and subseque...
Read more

Undo Segments : Drop Problematic Undo Segments

ERROR about to recover undo segment 98 status:6 inst:0 mark undo segment 98 as available status:6 ret:1  about to recover undo segment 46 status:6 inst:0 mark undo segment 46 as available status:6 ret:1   SQL> select * from V$FAST_START_TRANSACTIONS; USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS CON_ID ---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------- ---------- ---------- 46 46 2313064 RECOVERING 505 24992423 77 5586 0 0 0 10001000684B2300 0000000000000000 1 0 98 25 1352150 RECOVERING 0 226231 78 5586 0 0 0 30001900D6A11400 0000000000000000 1 0   SQL> SELECT segment_name, tablespace_name  FROM dba_rollback_segs  WHERE segment_id IN (98, 46); SEGMENT_NAME               TABLESPACE_NAME ------------------------------ ----------------------------...
Read more