Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

How to get full metadata of Oracle tables?

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
David_Fergen
Former Employee
Former Employee

How to get full metadata of Oracle tables?

Last Update:

Aug 28, 2020 5:38:36 AM

Updated By:

Sonja_Bauernfeind

Created date:

Jul 21, 2020 11:11:56 AM

Oracle has a very rich syntax for creating tables and related artifacts. When analyzing complicated issues regarding the Oracle Source or Target, full metadata of the Oracle tables is required.

 

RESOLUTION:

The dbms_metadata package includes several options to retrieve the full metadata of tables and their artifacts. At the bottom of this article is a script called Code to get Metadata that can be executed to retrieve this info which includes:

  1. Metadata of the table.
  2. Unique Indexes.
  3. Supplemental logging information.
  4. Information from all_objects.

Before executing the script you need to modify:

  • <tablename> -- The table name.
  • <owner> -- The owner of the table.

Furthermore, some cases the tablespace definition itself maybe required. The same dbms_metadata package can be used as follows:
 

 

 

 

select dbms_metadata.get_ddl('TABLESPACE', '<tablespacename>') from dual;

 

 

 


Modify the following parameter:

 

 

 

<tablespacename> -- The tablespace name you want to get the metadata for.

 

 

 

 

 Code to get Metadata:

 

 

 

SET HEADING OFF;
SET ECHO OFF;
SET PAGES 999;
SET LONG 90000;

spool ddl_<tablename>.txt

select dbms_metadata.get_ddl('TABLE','<tablename>','<owner>')      
from dual;

select dbms_metadata.get_dependent_ddl('INDEX','<tablename>','<owner>')     
from dual;

select g.table_name, c.column_name, g.always, c.log_group_name
from ALL_LOG_GROUP_COLUMNS c, ALL_LOG_GROUPS g where c.owner = g.owner
and c.log_group_name = g.log_group_name and c.table_name = g.table_name
and (c.owner = '<owner>' and c.table_name in ('<tablename>'));

select * from all_log_groups where table_name in ('<tablename>');

select * from all_objects where (OWNER = '<owner>' and OBJECT_NAME in ('<tablename>'));

spool off;

 

 

 

Labels (1)
Version history
Last update:
‎2020-08-28 05:38 AM
Updated by: