Oracle RDBMS: Extracting the Table, Index & View Definitions (DDL) and Indexed Columns

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;

eg.,

SQL> set long 1000SQL> set pagesize 0SQL> 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;

eg.,

SQL> create index PERSON_IDX on PERSON ( SSN ); Index created.SQL> set long 1000SQL> set pagesize 0SQL> 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>';

eg.,

SQL> column COLUMN_NAME format A15SQL> 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 10000SQL> select TEXT  2  FROM DBA_VIEWS  3  where OWNER = '<owner_name>'  4  and VIEW_NAME  = '<view_name>';

Here is an example:

% sqlplus fs890/fs890@fs890SQL> 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 1000SQL> 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 tablePredefined Parameters of Namespace USERENV for the list of parameters and the expected return values.

eg.,

SQL> column SESSION_USER format A15SQL> column CURRENT_SEHEMA format A15SQL> select sys_context('USERENV', 'SESSION_USER') SESSION_USER,   2  sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA from dual;SESSION_USER    CURRENT_SCHEMA--------------- ---------------FS890           FS890SQL> column USER format A6SQL> select USER from DUAL;USER------FS890

---

Note:

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.