DDL 정보

SELECT 
      C.USER_NAME||'.'||B.TABLE_NAME VIEW_NAME
    , A.VIEW_ID
    , DECODE(A.STATUS,0,'VALID','INVALID') STATUS 
FROM 
      SYSTEM_.SYS_VIEWS_ A
    , SYSTEM_.SYS_TABLES_ B
    , SYSTEM_.SYS_USERS_ C 
WHERE 
        A.VIEW_ID = B.TABLE_ID 
    AND A.USER_ID=C.USER_ID 
ORDER BY VIEW_NAME 

SELECT  table_name, table_type, comments  FROM USER_TAB_COMMENTS WHERE comments IS NOT NULL;     

코멘트 추가/삭제
COMMENT ON TABLE [테이블명] IS [Comment];
COMMENT ON [테이블명] IS '';

SELECT table_name, column_name, comments FROM USER_COL_COMMENTS WHERE comments IS NOT NULL;

컬럼 코멘트 추가
COMMENT ON COLUMN 테이블명.컬럼명 IS '코멘트';

SELECT TEXT, NAME, TYPE FROM ALL_SOURCE WHERE OWNER='사용자명' AND TYPE IN('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TRIGGER');

SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='사용자명'; 
SELECT DBMS_METADATA.GET_DDL('TABLE','테이블명') FROM DUAL;

SELECT * FROM ALL_VIEWS;
select text from ALL_VIEWS where upper(view_name) like upper(<view_name>;

SELECT INDEX_NAME FROM ALL_INDEXES WHERE OWNER = '사용자명';
SELECT DBMS_METADATA.GET_DDL('INDEX','인덱스명') FROM DUAL;

SELECT SEQUENCE_NAME from ALL_sequences WHERE IF_AVAIL='Y' AND SEQUENCE_OWNER='사용자명';

SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE' AND STATUS='VALID' AND OWNER='사용자명';
SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'PACKAGE' AND STATUS='VALID' AND OWNER='사용자명';
SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION' AND STATUS='VALID' AND OWNER='사용자명';

SELECT DBMS_METADATA.GET_DDL('TABLE','테이블명') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('INDEX','인덱스명') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('PROCEDURE','프로시저명') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('SEQUENCE','시퀀스명') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('FUNCTION','함수명') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TRIGGER','트리거명') FROM DUAL;

select a.object_name as e_name  
      ,nvl(b.comments,' ') as k_name 
from 
     all_objects a  
    ,all_tab_comments b
where
      lower(a.owner)=lower(?)  
  and lower(a.object_type)=lower(?)  
  and a.owner=b.owner  
  and a.object_name=b.table_name  
order by a.object_name 

select a.object_name as e_name  
      ,nvl(b.comments,' ') as k_name
from 
     all_objects a  
    ,all_tab_comments b
where
      lower(a.owner)=lower(?)  
  and lower(a.object_type)=lower(?) 
  and a.owner=b.owner  
  and a.object_name=b.table_name  
order by a.object_name 

select
    a.column_id
   ,a.column_name
   ,b.comments
   ,a.data_type
   ,decode(a.data_precision,null,to_char(a.data_length),0,to_char(a.data_length),to_char(a.data_precision)||'.'||to_char(a.data_scale)) as length
   ,decode(c.column_name,null,' ','P') pkey
   ,a.nullable
   ,a.data_default
from
         all_tab_cols a
        ,all_col_comments b
        ,  (select distinct column_name
            from all_cons_columns 
            where constraint_name = (
                  select constraint_name from all_constraints
                  where lower(owner)=lower(?) and lower(table_name) = lower(?) 
                  and constraint_type= 'P' 
               ) ) c
where
          lower(a.owner)=lower(?) 
    and lower(a.table_name)=lower(?)
    and a.owner=b.owner 
    and a.table_name=b.table_name 
    and a.column_name=b.column_name  
    and a.column_name=c.column_name(+)  
order by a.column_id