Script to generate ddl script of table and its index/trigger:

Script to generate ddl script of table and its index/trigger:

set long 1000000
SET PAGESIZE 3000
set lines 200
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
set echo on
set timing off
set wrap On

ACCEPT TABLE_NAME CHAR PROMPT 'Enter Table Name : '
ACCEPT TABLE_OWNER CHAR PROMPT 'Enter Table Owner : '

select DBMS_METADATA.GET_DDL('TABLE',OBJECT_NAME,OWNER)
FROM Dba_objects
where owner = UPPER('&TABLE_OWNER') and object_name = UPPER('&TABLE_NAME')
and object_type = 'TABLE'
union all
select dbms_metadata.GET_DEPENDENT_DDL ('COMMENT', TABLE_NAME, OWNER )
FROM (select table_name,owner
from Dba_col_comments
where owner = UPPER('&TABLE_OWNER')
and table_name = UPPER('&TABLE_NAME')
and comments is not null
union
select table_name,owner
from sys.Dba_TAB_comments
where owner = UPPER('&TABLE_OWNER')
and table_name = UPPER('&TABLE_NAME')
and comments is not null)
union all
select DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',TABLE_NAME, TABLE_OWNER)
FROM (select table_name,table_owner
FROM Dba_indexes
where table_owner = UPPER('&TABLE_OWNER')
and table_name = UPPER('&TABLE_NAME')
and index_name not in (select constraint_name
from sys.Dba_constraints
where table_name = table_name
and constraint_type = 'P' )
and rownum = 1)
union all
select dbms_metadata.GET_DDL ('TRIGGER', trigger_name ,owner )
from Dba_triggers
where table_owner = UPPER('&TABLE_OWNER')
and table_name = UPPER('&TABLE_NAME')
.
SET CONCAT +
spool &TABLE_NAME+_ddl.txt
/
spool off

Ref : How To Generate A DDL Script For A Table [ID 1320598.1]


UR's Key Run ;-) 

Primary Key

Steps of find the type of Primary Key Involved :

1. Find out the type of the object

select object_name, object_type from all_objects where object_name = upper('<p_object_name>');

2. To fetch the primary key name corresponding to the index/table name from the error message.

Index

select primary_key_name
from fnd_primary_keys
where application_id=283
and table_id = (select table_id from fnd_indexes
where application_id=283 and index_name = upper('<p_object_name>'));

or

Table

select primary_key_name
from fnd_primary_keys
where application_id=283
and table_id = (select table_id from fnd_tables
where application_id=283 and table_name = upper('<p_object_name>'));

3. Check the involved primary key
select primary_key_type
from fnd_primary_keys where primary_key_name = upper('<p_primary_key_name>');


To get Primary Keys info on set of tables:

select pk.primary_key_type TYPE,
pk.application_id APPID,
pk.primary_key_name
from fnd_primary_keys pk,fnd_tables t
where t.application_id >= 0
and t.table_name in ('ABC','DEF',
'GHI','KLM')
and pk.application_id = t.application_id
and pk.table_id = t.table_id;

TYPE    APPID         PRIMARY_KEY_NAME             
____   _______         ___________________    
S           123             PQR_PK    
D           123             LMN_PK
D           123             XYZ_PK 
D           123             123_PK

If in case required to change/update the type of  key

UPDATE fnd_primary_keys
SET primary_key_type = 'D'
WHERE primary_key_name = upper('PQR_PK');

COMMIT;

UR's Key Run ;-)