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:
- Metadata of the table.
- Unique Indexes.
- Supplemental logging information.
- 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;