https://blogs.oracle.com/mandalika/entry/oracle_extracing_the_table_index
(Reproducing a 30 month old blog post from my other blog at blogger. Source URL:
http://technopark02.blogspot.com/2007/05/oracle-how-to-get-tableview-definition.html)
Q: How to extract the table definition (DDL statement) from an Oracle database without having to go through a stack of dictionary views?
A: By calling the GET_DDL()
function of metadata package DBMS_METADATA
.
Syntax:
select DBMS_METADATA.GET_DDL('TABLE','<table_name>') from DUAL;
SQL> set long 1000 SQL> set pagesize 0 SQL> select DBMS_METADATA.GET_DDL('TABLE','PERSON') from DUAL; CREATE TABLE "FS890"."PERSON" ( "SSN" VARCHAR2(12), "FIRST_NAME" VARCHAR2(25), "LAST_NAME" VARCHAR2(25), "STREET" VARCHAR2(40), "CITY" VARCHAR2(30), "STATE" VARCHAR2(30), "ZIP" VARCHAR2(15), "COUNTRY" VARCHAR2(35) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) TABLESPACE "PSDEFAULT"
Q: How to extract the index definition (DDL statement) from an Oracle database for a given index nam?
A: By calling the GET_DDL()
function of metadata package DBMS_METADATA
.
Syntax:
select DBMS_METADATA.GET_DDL('INDEX','<index_name>') from DUAL;
SQL> create index PERSON_IDX on PERSON ( SSN ); Index created. SQL> set long 1000 SQL> set pagesize 0 SQL> select DBMS_METADATA.GET_DDL('INDEX','PERSON_IDX') from DUAL; CREATE INDEX "FS890"."PERSON_IDX" ON "FS890"."PERSON" ("SSN") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PSDEFAULT"
Note:
If the interest is only to get the indexed column names for an index, simply query COLUMN_NAME of table USER_IND_COLUMNS.
Syntax:
select COLUMN_NAME from USER_IND_COLUMNS where INDEX_NAME = '<index_name>';
SQL> column COLUMN_NAME format A15 SQL> select COLUMN_NAME from USER_IND_COLUMNS where INDEX_NAME = 'PERSON_IDX'; COLUMN_NAME --------------- SSN
Q: Given a view name, how do we get the definition of the view? i.e., how to get the corresponding DDL statement that was used to create the view?
A: Query the TEXT column of table DBA_VIEWS.
Syntax:SQL> set long 10000 SQL> select TEXT 2 FROM DBA_VIEWS 3 where OWNER = '<owner_name>' 4 and VIEW_NAME = '<view_name>';Here is an example:
% sqlplus fs890/fs890@fs890 SQL> create table PERSON ( 2 SSN VARCHAR2(12), 3 FIRST_NAME VARCHAR2(25), 4 LAST_NAME VARCHAR2(25), 5 STREET VARCHAR2(40), 6 CITY VARCHAR2(30), 7 STATE VARCHAR2(30), 8 ZIP VARCHAR2(15), 9 COUNTRY VARCHAR2(35)); Table created. SQL> create view PERSON_VW as 2 select SSN, FIRST_NAME, LAST_NAME from PERSON; View created. SQL> set long 1000 SQL> select TEXT 2 from DBA_VIEWS 3 where OWNER = 'FS890' 4 and VIEW_NAME = 'PERSON_VW'; TEXT -------------------------------------------------------------------------------- SELECT SSN, FIRST_NAME, LAST_NAME FROM PERSON
Q: How to find the schema name and the DB user name from an active session?
A: Run the following query:
select sys_context('USERENV', 'SESSION_USER') SESSION_USER, sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA from dual;
Alternatively run select USER from DUAL;
to find the current {session} user name.
sys_context()
function returns the value of parameter associated with the context namespace. USERENV
is an Oracle provided namespace that describes the current session. Check the table Predefined Parameters of Namespace USERENV for the list of parameters and the expected return values.
SQL> column SESSION_USER format A15 SQL> column CURRENT_SEHEMA format A15 SQL> select sys_context('USERENV', 'SESSION_USER') SESSION_USER, 2 sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA from dual; SESSION_USER CURRENT_SCHEMA --------------- --------------- FS890 FS890 SQL> column USER format A6 SQL> select USER from DUAL; USER ------ FS890
Be aware that there are multiple ways of extracting the same piece of information from an Oracle database. I just provided the ones that I frequently use as part of my work.