Extracting META information from Oracle (INFORMATION_SCHEMA)
Post date: 15-Jul-2010 12:01:57
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).
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.
001.-- sample data to test Oracle INFORMATION_SCHEMA
002.
003.-- TABLE TEST
004.CREATE TABLE TEST (
005. TEST_NAME CHAR(30) NOT NULL,
006. TEST_ID INTEGER DEFAULT 0 NOT NULL,
007. TEST_DATE TIMESTAMP NOT NULL
008.);
009.ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (TEST_ID);
010.
011.-- TABLE TEST2 with some CONSTRAINTs and an INDEX
012.CREATE TABLE TEST2 (
013. ID INTEGER NOT NULL,
014. FIELD1 INTEGER,
015. FIELD2 CHAR(15),
016. FIELD3 VARCHAR(50),
017. FIELD4 INTEGER,
018. FIELD5 INTEGER,
019. ID2 INTEGER NOT NULL
020.);
021.ALTER TABLE TEST2 ADD CONSTRAINT PK_TEST2 PRIMARY KEY (ID2);
022.ALTER TABLE TEST2 ADD CONSTRAINT TEST2_FIELD1ID_IDX UNIQUE (ID, FIELD1);
023.ALTER TABLE TEST2 ADD CONSTRAINT TEST2_FIELD4_IDX UNIQUE (FIELD4);
024.CREATE INDEX TEST2_FIELD5_IDX ON TEST2(FIELD5);
025.
026.-- TABLE NUMBERS
027.CREATE TABLE NUMBERS (
028. NUM INTEGER DEFAULT '0' NOT NULL,
029. EN CHAR(100) NOT NULL,
030. FR CHAR(100) NOT NULL
031.);
032.
033.-- TABLE NEWTABLE
034.CREATE TABLE NEWTABLE (
035. ID INT DEFAULT 0 NOT NULL,
036. SOMENAME VARCHAR (12),
037. SOMEDATE TIMESTAMP NOT NULL
038.);
039.ALTER TABLE NEWTABLE ADD CONSTRAINT PKINDEX_IDX PRIMARY KEY (ID);
040.CREATE SEQUENCE NEWTABLE_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
041.
042.-- VIEW on TEST
043.CREATE VIEW testview(
044. TEST_NAME,
045. TEST_ID,
046. TEST_DATE
047.) AS
048.SELECT *
049.FROM TEST
050.WHERE TEST_NAME LIKE 't%';
051.
052.-- VIEW on NUMBERS
053.CREATE VIEW numbersview(
054. NUM,
055. TRANS_EN,
056. TRANS_FR
057.) AS
058.SELECT *
059.FROM NUMBERS
060.WHERE NUM > 100;
061.
062.-- TRIGGER on NEWTABLE
063.CREATE OR REPLACE TRIGGER TEST_TRIGGER
064.AFTER UPDATE ON NEWTABLE
065.REFERENCING NEW AS NEW OLD AS OLD
066.FOR EACH ROW
067.BEGIN
068. INSERT INTO NEWTABLE (id, somename, somedate) VALUES (:new.id+1, :new.somename, :new.somedate);
069.END TEST_TRIGGER;
070.
071.-- SAMPLE FUNCTION
072.CREATE FUNCTION sum_two_integers(a IN INT, b IN INT)
073.RETURN INT AS
074.BEGIN
075. RETURN a + b;
076.END;
077.
078.-- SAMPLE STORED PROCEDURE
079.CREATE OR REPLACE PROCEDURE count_numbers_by_lang(var_lang in varchar2, var_num out integer)
080.IS
081.var_temp_n INTEGER;
082.BEGIN
083.SELECT COUNT(DISTINCT var_lang)
084. INTO var_temp_n
085. FROM NUMBERS
086.WHERE var_lang IS NOT NULL;
087.var_num := var_temp_n;
088.return;
089.END;
090.
091.-- TABLEs for testing CONSTRAINTs
092.CREATE TABLE testconstraints (
093. someid integer NOT NULL,
094. somename character varying(10) NOT NULL,
095. CONSTRAINT testconstraints_id_pk PRIMARY KEY (someid)
096.);
097.CREATE TABLE testconstraints2 (
098. ext_id INTEGER NOT NULL,
099. modified DATE,
100. uniquefield VARCHAR(10) NOT NULL,
101. usraction INTEGER NOT NULL,
102. CONSTRAINT unique_2_fields_idx UNIQUE (modified, usraction),
103. CONSTRAINT uniquefld_idx UNIQUE (uniquefield),
104. CONSTRAINT testconstraints_id_fk FOREIGN KEY (ext_id)
105. REFERENCES TESTCONSTRAINTS (someid) ON DELETE CASCADE
106.);
List TABLEs
Here's the query that will return the names of the tables defined in the current database:
1.SELECT table_name
2. FROM user_tables;
3.
4.-- or
5.
6.SELECT table_name
7. FROM tabs;
List VIEWs
Here's the query that will return the names of the VIEWs defined in the current database:
1.SELECT view_name
2. FROM user_views;
List users
NB: you'll need DBA access rights to execute the following query:
1.SELECT username
2. FROM dba_users;
List table fields
Here's the query that will return the names of the fields of the TEST2 table:
1.SELECT column_name
2. FROM user_tab_columns
3. WHERE table_name='TEST'
4.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:
01.SELECT column_id "ordinal_position",
02. column_name,
03. data_type,
04. nullable,
05. data_default "default",
06. COALESCE(data_precision, data_length) "length",
07. data_scale "scale"
08. FROM all_tab_columns
09. WHERE table_name = 'TEST2'
10.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
1.SELECT index_name name
2. FROM user_indexes
3. WHERE table_name = 'TEST2'
4. AND generated = 'N';
Detailed INDEX info
If you want to know which table columns are referenced by an index, try with this query:
01.SELECT aic.index_name,
02. aic.column_name,
03. aic.column_position,
04. aic.descend,
05. aic.table_owner,
06. CASE alc.constraint_type
07. WHEN 'U' THEN 'UNIQUE'
08. WHEN 'P' THEN 'PRIMARY KEY'
09. ELSE ''
10. END AS index_type
11. FROM all_ind_columns aic
12.LEFT JOIN all_constraints alc
13. ON aic.index_name = alc.constraint_name
14. AND aic.table_name = alc.table_name
15. AND aic.table_owner = alc.owner
16. WHERE aic.table_name = 'TEST2' -- table name
17. --AND aic.table_owner = 'HR' -- table owner
18. --AND aic.index_name = 'TEST2_FIELD5_IDX' -- index name
19. ORDER BY column_position;
List CONSTRAINTs
Here's the query that will return the names of the CONSTRAINTs defined in the TEST2 table:
1.SELECT constraint_name name
2. FROM user_constraints
3. 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:
01.SELECT alc.constraint_name,
02. CASE alc.constraint_type
03. WHEN 'P' THEN 'PRIMARY KEY'
04. WHEN 'R' THEN 'FOREIGN KEY'
05. WHEN 'U' THEN 'UNIQUE'
06. WHEN 'C' THEN 'CHECK'
07. END "constraint_type",
08. alc.DELETE_RULE "on_delete",
09. CASE alc.deferrable WHEN 'NOT DEFERRABLE' THEN 0 ELSE 1 END "deferrable",
10. CASE alc.deferred WHEN 'IMMEDIATE' THEN 1 ELSE 0 END "initially_deferred",
11. alc.search_condition,
12. alc.table_name,
13. cols.column_name,
14. cols.position,
15. r_alc.table_name "references_table",
16. r_cols.column_name "references_field",
17. r_cols.position "references_field_position"
18. FROM all_cons_columns cols
19.LEFT JOIN all_constraints alc
20. ON alc.constraint_name = cols.constraint_name
21. AND alc.owner = cols.owner
22.LEFT JOIN all_constraints r_alc
23. ON alc.r_constraint_name = r_alc.constraint_name
24. AND alc.r_owner = r_alc.owner
25.LEFT JOIN all_cons_columns r_cols
26. ON r_alc.constraint_name = r_cols.constraint_name
27. AND r_alc.owner = r_cols.owner
28. AND cols.position = r_cols.position
29. WHERE alc.constraint_name = cols.constraint_name
30. AND alc.constraint_name = 'TESTCONSTRAINTS_ID_FK'
31. 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:
01.SELECT c.name "constraint_name",
02. decode(cd.type#, 1, 'CHECK', 2, 'PRIMARY KEY', 3, 'UNIQUE', 4, 'FOREIGN KEY', 5, 'V', 6, 'O', 7, 'C', '?') "constraint_type",
03. decode(cd.type#, 4, decode(cd.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION'), NULL) "delete_rule",
04. decode(cd.type#, 5, 'ENABLED', decode(cd.enabled, NULL, 'DISABLED', 'ENABLED')) "status",
05. decode(bitand(cd.defer, 1), 1, 'DEFERRABLE', 'NOT DEFERRABLE') "deferrable",
06. decode(bitand(cd.defer, 2), 2, 'DEFERRED', 'IMMEDIATE') "deferred",
07. cd.condition "search_condition",
08. o.name "table_name",
09. decode(ac.name, null, col.name, ac.name) "column_name",
10. cc.pos# "position",
11. rc.name "r_constraint_name",
12. ro.name "references_table",
13. decode(rac.name, null, rcol.name, rac.name) "references_field",
14. rcc.pos# "references_field_position"
15. FROM sys.con$ c,
16. sys.col$ col,
17. sys.ccol$ cc,
18. sys.cdef$ cd,
19. sys.obj$ o,
20. sys.con$ rc,
21. sys.attrcol$ ac,
22. sys.col$ rcol,
23. sys.ccol$ rcc,
24. sys.cdef$ rcd,
25. sys.obj$ ro,
26. sys.attrcol$ rac
27. WHERE c.con# = cd.con#
28. AND cd.type# != 12 /* don't include log groups */
29. AND cd.con# = cc.con#
30. AND cc.obj# = col.obj#
31. AND cc.intcol# = col.intcol#
32. AND cc.obj# = o.obj#
33. AND c.owner# = userenv('SCHEMAID')
34. AND col.obj# = ac.obj#(+)
35. AND col.intcol# = ac.intcol#(+)
36. AND cd.rcon# = rc.con#(+)
37. AND rc.con# = rcd.con#
38. AND rcd.type# != 12 /* don't include log groups */
39. AND rcd.con# = rcc.con#
40. AND rcc.obj# = rcol.obj#
41. AND rcc.intcol# = rcol.intcol#
42. AND rcc.obj# = ro.obj#
43. AND rc.owner# = userenv('SCHEMAID')
44. AND rcol.obj# = rac.obj#(+)
45. AND rcol.intcol# = rac.intcol#(+)
46. 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.
1.SELECT sequence_name
2. FROM user_sequences;
List TRIGGERs
1.SELECT trigger_name
2. FROM user_triggers;
3.
4.-- List only the triggers for a given table:
5.
6.SELECT DISTINCT trigger_name
7. FROM user_triggers
8. WHERE table_name = 'NEWTABLE';
Detailed TRIGGER info
Show more information about the trigger definition:
01.SELECT trigger_name,
02. table_name,
03. trigger_body,
04. trigger_type,
05. triggering_event trigger_event,
06. description trigger_comment,
07. 1 trigger_enabled,
08. when_clause
09. FROM user_triggers
10. WHERE trigger_name = 'TEST_TRIGGER';
List FUNCTIONs
1.SELECT name
2. FROM user_source
3. WHERE line = 1
4. AND type = 'FUNCTION';
List STORED PROCEDUREs
1.SELECT DISTINCT procedure_name
2. FROM all_procedures;
3.
4.-- list only the SP owned by current user:
5.
6.SELECT *
7. FROM all_procedures
8. WHERE owner = 'username';