Lorenzo Alberton

London, UK   ·   Contact me   ·  

« Articles

Extracting META information from Oracle (INFORMATION_SCHEMA)

Abstract: Extracting META information from an Oracle database using the Oracle Data Dictionary views (INFORMATION_SCHEMA)


While Oracle doesn't implement the INFORMATION_SCHEMA SQL-92 standard, there are several methods to get metadata information, such as using SQL *Plus and DESCRIBE, or using the the PL/SQL metadata API and the DBMS_METADATA package (available since Oracle 9i), or using the data dictionary views.
In this tutorial, we're going to see how to get metadata information from our database using the Oracle Data Dictionary views ("select * from dict" for a full listing).

Oracle INFORMATION_SCHEMA - extracting Oracle metadata, by Lorenzo Alberton

Test data

We need a few sample tables, indices and views to test the following queries, so let's create them. We also create a sample TRIGGER, a STORED PROCEDURE and a FUNCTION.

-- sample data to test Oracle INFORMATION_SCHEMA
 
-- TABLE TEST
CREATE TABLE TEST (
  TEST_NAME CHAR(30) NOT NULL,
  TEST_ID INTEGER DEFAULT 0 NOT NULL,
  TEST_DATE TIMESTAMP NOT NULL
);
ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (TEST_ID);
 
-- TABLE TEST2 with some CONSTRAINTs and an INDEX
CREATE TABLE TEST2 (
  ID INTEGER NOT NULL,
  FIELD1 INTEGER,
  FIELD2 CHAR(15),
  FIELD3 VARCHAR(50),
  FIELD4 INTEGER,
  FIELD5 INTEGER,
  ID2 INTEGER NOT NULL
);
ALTER TABLE TEST2 ADD CONSTRAINT PK_TEST2 PRIMARY KEY (ID2);
ALTER TABLE TEST2 ADD CONSTRAINT TEST2_FIELD1ID_IDX UNIQUE (ID, FIELD1);
ALTER TABLE TEST2 ADD CONSTRAINT TEST2_FIELD4_IDX UNIQUE (FIELD4);
CREATE INDEX TEST2_FIELD5_IDX ON TEST2(FIELD5);
 
-- TABLE NUMBERS
CREATE TABLE NUMBERS (
  NUM INTEGER DEFAULT '0' NOT NULL,
  EN CHAR(100) NOT NULL,
  FR CHAR(100) NOT NULL
);
 
-- TABLE NEWTABLE
CREATE TABLE NEWTABLE (
  ID INT DEFAULT 0 NOT NULL,
  SOMENAME VARCHAR (12),
  SOMEDATE TIMESTAMP NOT NULL
);
ALTER TABLE NEWTABLE ADD CONSTRAINT PKINDEX_IDX PRIMARY KEY (ID);
CREATE SEQUENCE NEWTABLE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
 
-- VIEW on TEST
CREATE VIEW testview(
  TEST_NAME,
  TEST_ID,
  TEST_DATE
) AS
SELECT *
FROM TEST
WHERE TEST_NAME LIKE 't%';
 
-- VIEW on NUMBERS
CREATE VIEW numbersview(
  NUM,
  TRANS_EN,
  TRANS_FR
) AS
SELECT *
FROM NUMBERS
WHERE NUM > 100;
 
-- TRIGGER on NEWTABLE
CREATE OR REPLACE TRIGGER TEST_TRIGGER
AFTER UPDATE ON NEWTABLE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  INSERT INTO NEWTABLE (id, somename, somedate) VALUES (:new.id+1, :new.somename, :new.somedate);
END TEST_TRIGGER;
 
-- SAMPLE FUNCTION
CREATE FUNCTION sum_two_integers(a IN INT, b IN INT)
RETURN INT AS
BEGIN
    RETURN a + b;
END;
 
-- SAMPLE STORED PROCEDURE
CREATE OR REPLACE PROCEDURE count_numbers_by_lang(var_lang in varchar2, var_num out integer)
IS
var_temp_n INTEGER;
BEGIN
SELECT COUNT(DISTINCT var_lang)
  INTO var_temp_n
  FROM NUMBERS
WHERE var_lang IS NOT NULL;
var_num := var_temp_n;
return;
END;
 
-- TABLEs for testing CONSTRAINTs
CREATE TABLE testconstraints (
  someid integer NOT NULL,
  somename character varying(10) NOT NULL,
  CONSTRAINT testconstraints_id_pk PRIMARY KEY (someid)
);
CREATE TABLE testconstraints2 (
  ext_id INTEGER NOT NULL,
  modified DATE,
  uniquefield VARCHAR(10) NOT NULL,
  usraction INTEGER NOT NULL,
  CONSTRAINT unique_2_fields_idx UNIQUE (modified, usraction),
  CONSTRAINT uniquefld_idx UNIQUE (uniquefield),
  CONSTRAINT testconstraints_id_fk FOREIGN KEY (ext_id)
    REFERENCES TESTCONSTRAINTS (someid) ON DELETE CASCADE
);

List TABLEs

Here's the query that will return the names of the tables defined in the current database:

SELECT table_name
  FROM user_tables;
  
-- or
 
SELECT table_name
  FROM tabs;

List VIEWs

Here's the query that will return the names of the VIEWs defined in the current database:

SELECT view_name
  FROM user_views;

List users

NB: you'll need DBA access rights to execute the following query:

SELECT username
  FROM dba_users;

List table fields

Here's the query that will return the names of the fields of the TEST2 table:

SELECT column_name
    FROM user_tab_columns
   WHERE table_name='TEST'
ORDER BY column_id

Detailed table field info

If you want some more info about the field definitions, you can retrieve a larger subset of the fields available in the schema:

SELECT column_id "ordinal_position",
         column_name,
         data_type,
         nullable,
         data_default "default",
         COALESCE(data_precision, data_length) "length",
         data_scale "scale"
    FROM all_tab_columns
   WHERE table_name = 'TEST2'
ORDER BY column_id;

List INDICES

Here's the query that will return the names of the INDICES defined in the TEST2 table. NB: the CONSTRAINTs are not listed

SELECT index_name name
  FROM user_indexes
 WHERE table_name = 'TEST2'
   AND generated = 'N';

Detailed INDEX info

If you want to know which table columns are referenced by an index, try with this query:

SELECT aic.index_name,
          aic.column_name,
          aic.column_position,
          aic.descend,
          aic.table_owner,
          CASE alc.constraint_type
            WHEN 'U' THEN 'UNIQUE'
            WHEN 'P' THEN 'PRIMARY KEY'
            ELSE ''
          END AS index_type
     FROM all_ind_columns aic
LEFT JOIN all_constraints alc
       ON aic.index_name = alc.constraint_name
      AND aic.table_name = alc.table_name
      AND aic.table_owner = alc.owner
    WHERE aic.table_name = 'TEST2'            -- table name
    --AND aic.table_owner = 'HR'              -- table owner
    --AND aic.index_name = 'TEST2_FIELD5_IDX' -- index name
 ORDER BY column_position;

List CONSTRAINTs

Here's the query that will return the names of the CONSTRAINTs defined in the TEST2 table:

SELECT constraint_name name
  FROM user_constraints
 WHERE table_name = 'TEST2';

Detailed CONSTRAINT info

If you want to retrieve detailed info from any constraint (fields, type, rules, referenced table and fields for FOREIGN KEYs, etc.) given its name and table, here's the query to do so:

SELECT alc.constraint_name,
          CASE alc.constraint_type
            WHEN 'P' THEN 'PRIMARY KEY'
            WHEN 'R' THEN 'FOREIGN KEY'
            WHEN 'U' THEN 'UNIQUE'
            WHEN 'C' THEN 'CHECK'
          END "constraint_type",
          alc.DELETE_RULE "on_delete",
          CASE alc.deferrable WHEN 'NOT DEFERRABLE' THEN 0 ELSE 1 END "deferrable",
          CASE alc.deferred WHEN 'IMMEDIATE' THEN 1 ELSE 0 END "initially_deferred",
          alc.search_condition,
          alc.table_name,
          cols.column_name,
          cols.position,
          r_alc.table_name "references_table",
          r_cols.column_name "references_field",
          r_cols.position "references_field_position"
     FROM all_cons_columns cols
LEFT JOIN all_constraints alc
       ON alc.constraint_name = cols.constraint_name
      AND alc.owner = cols.owner
LEFT JOIN all_constraints r_alc
       ON alc.r_constraint_name = r_alc.constraint_name
      AND alc.r_owner = r_alc.owner
LEFT JOIN all_cons_columns r_cols
       ON r_alc.constraint_name = r_cols.constraint_name
      AND r_alc.owner = r_cols.owner
      AND cols.position = r_cols.position
    WHERE alc.constraint_name = cols.constraint_name
      AND alc.constraint_name = 'TESTCONSTRAINTS_ID_FK'
      AND alc.table_name = 'TESTCONSTRAINTS2';

The above query has the small drawback of being slow, with the multiple self-join on the all_cons_columns and all_constraints views. If you know a faster query to get the same information, please let me know. If you have DBA access rights, you can run the following query instead, it should be considerably faster:

SELECT c.name "constraint_name",
       decode(cd.type#, 1, 'CHECK', 2, 'PRIMARY KEY', 3, 'UNIQUE', 4, 'FOREIGN KEY', 5, 'V', 6, 'O', 7, 'C', '?') "constraint_type",
       decode(cd.type#, 4, decode(cd.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION'), NULL) "delete_rule",
       decode(cd.type#, 5, 'ENABLED', decode(cd.enabled, NULL, 'DISABLED', 'ENABLED')) "status",
       decode(bitand(cd.defer, 1), 1, 'DEFERRABLE', 'NOT DEFERRABLE') "deferrable",
       decode(bitand(cd.defer, 2), 2, 'DEFERRED', 'IMMEDIATE') "deferred",
       cd.condition "search_condition",
       o.name "table_name",
       decode(ac.name, null, col.name, ac.name) "column_name",
       cc.pos# "position",
       rc.name "r_constraint_name",
       ro.name "references_table",
       decode(rac.name, null, rcol.name, rac.name) "references_field",
       rcc.pos# "references_field_position"
  FROM sys.con$ c,
       sys.col$ col,
       sys.ccol$ cc,
       sys.cdef$ cd,
       sys.obj$ o,
       sys.con$ rc,
       sys.attrcol$ ac,
       sys.col$ rcol,
       sys.ccol$ rcc,
       sys.cdef$ rcd,
       sys.obj$ ro,
       sys.attrcol$ rac
 WHERE c.con# = cd.con#
   AND cd.type# != 12 /* don't include log groups */
   AND cd.con# = cc.con#
   AND cc.obj# = col.obj#
   AND cc.intcol# = col.intcol#
   AND cc.obj# = o.obj#
   AND c.owner# = userenv('SCHEMAID')
   AND col.obj# = ac.obj#(+)
   AND col.intcol# = ac.intcol#(+)
   AND cd.rcon# = rc.con#(+)
   AND rc.con# = rcd.con#
   AND rcd.type# != 12 /* don't include log groups */
   AND rcd.con# = rcc.con#
   AND rcc.obj# = rcol.obj#
   AND rcc.intcol# = rcol.intcol#
   AND rcc.obj# = ro.obj#
   AND rc.owner# = userenv('SCHEMAID')
   AND rcol.obj# = rac.obj#(+)
   AND rcol.intcol# = rac.intcol#(+)
   AND c.name = 'TESTCONSTRAINTS_ID_FK'

List sequences

A SEQUENCE is an object that automatically generate sequence numbers. A SEQUENCE is often used to ensure a unique value in a PRIMARY KEY that must uniquely identify the associated row.

SELECT sequence_name
  FROM user_sequences;

List TRIGGERs

SELECT trigger_name
  FROM user_triggers;

-- List only the triggers for a given table:

SELECT DISTINCT trigger_name
  FROM user_triggers
 WHERE table_name = 'NEWTABLE';

Detailed TRIGGER info

Show more information about the trigger definition:

SELECT trigger_name,
       table_name,
       trigger_body,
       trigger_type,
       triggering_event trigger_event,
       description trigger_comment,
       1 trigger_enabled,
       when_clause
  FROM user_triggers
 WHERE trigger_name = 'TEST_TRIGGER';

List FUNCTIONs

SELECT name
  FROM user_source
 WHERE line = 1
   AND type = 'FUNCTION';

List STORED PROCEDUREs

SELECT DISTINCT procedure_name
 FROM all_procedures;
 
-- list only the SP owned by current user:
 
SELECT *
  FROM all_procedures
 WHERE owner = 'username';

What else?

If you'd like to see some other example queries, or have some comments and/or suggestions, just drop me a mail (you can find my address in the footer of this page) and I'll add them to this list.
HTH.



8 responses to "Extracting META information from Oracle (INFORMATION_SCHEMA)"

Paul Roche, 09 July 2009 21:22

Just what I was looking for to determine if I had any more Cascade deletes. Thanks

Josef, 29 September 2010 09:19

Great reference posts about metainformation in different databases. Thanks!

Mario Majcica, 22 December 2010 09:57

Problems with data types. If you declare an integer, and then you try to get the schema of that table, you get numeric 38. Is it somehow possible to get Integer back from schema?
Thanks

EssescalL, 19 February 2011 01:26

hi, new to the site, thanks.

Sergey, 23 December 2011 13:59

Thank you. It is very helpful and clear!

djanahana, 09 March 2012 22:47

thanks for this explanation ,i am askign you sir to tell me more detail or tell me i can find more information about metadata oracle i have a specific project where i need the detail of detail :) thanks once again

worried, 20 March 2012 20:25

How do I add a owner to a table audit_user_login? This was created by a person who is no longer here.
Thank you

Martin, 22 March 2012 15:03

Hi,
I\'d like to be able to retrieve the parameter information on a Stored Procedure. This is how i do it in SQLServer & MySQL:
SELECT PARAMETER_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,PARAMETER_MODE,NUMERIC_PRECISION,NUMERIC_SCALE FROM FORMATION_SCHEMA.Parameters WHERE SPECIFIC_NAME = \'TestSP\' ORDER BY ORDINAL_POSITION
This is used by a reporting suite to list all the SPs and their parameters for users to execute via ADO dynamically executing any SP selected with up to 10 parms accepted. So this is pretty good as a reporting mechanism where you create SP that are just views in essence but accept parms to make the view more refined. I just need to get the stats on the parms like name, datatype, length, in/out
Thanks Martin


Related articles

Latest articles

Filter articles by topic

AJAX, Apache, Book Review, Charset, Cheat Sheet, Data structures, Database, Firebird SQL, Hadoop, Imagick, INFORMATION_SCHEMA, JavaScript, Kafka, Linux, Message Queues, mod_rewrite, Monitoring, MySQL, NoSQL, Oracle, PDO, PEAR, Performance, PHP, PostgreSQL, Profiling, Scalability, Security, SPL, SQL Server, SQLite, Testing, Tutorial, TYPO3, Windows, Zend Framework


Back