adminschool.net wiki

이 문서는 읽기 전용입니다. 원본을 볼 수는 있지만 바꿀 수는 없습니다. 문제가 있다고 생각하면 관리자에게 문의하세요.

====== Oracle SQL Tips ======

===== Table 생성시기를 알아오는 쿼리 =====
<code oracle8> 
SQL> SELECT OWNER,OBJECT_NAME,CREATED
FROM DBA_OBJECTS
WHERE CREATED > '2006-08-31' AND OWNER NOT IN ('SYS') AND OBJECT_TYPE = 'TABLE'
ORDER BY OWNER,CREATED ASC;
</code>

===== 전체테이블 TRUNCATE하는 쿼리 만들기  =====
<code oracle8>
SQL> select 'truncate table '||table_name||';' from user_tables
where table_name not in ('TEMPLATE_XML','QUESTION_XML')
order by table_name asc;
</code>

===== 테이블 정의를 출력해주는 쿼리 =====
<code oracle8>
SQL> SELECT TC.TABLE_NAME
	   ,TCOMMENT.COMMENTS AS KOR_TNAME
	   ,TC.COLUMN_NAME
	   ,CCOMMENT.COMMENTS AS KOR_CNAME
	   ,TC.DATA_TYPE||'('||TC.DATA_LENGTH||')'
	   ,DECODE(PK.CT,'P','PK','') AS PK
	   ,DECODE(TC.NULLABLE,'N','NOT NULL','') AS NOT_NULL
	   ,TC.DATA_DEFAULT,'' AS ETC
FROM USER_TAB_COLUMNS TC 
	 ,USER_COL_COMMENTS CCOMMENT 
	 ,USER_TAB_COMMENTS TCOMMENT
	 ,(
	   	SELECT CCOL.TABLE_NAME,CCOL.COLUMN_NAME,UC.CONSTRAINT_TYPE CT
	 	FROM USER_CONS_COLUMNS CCOL,USER_CONSTRAINTS UC
		WHERE CCOL.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
			  AND CCOL.TABLE_NAME = UC.TABLE_NAME
			  AND UC.CONSTRAINT_TYPE = 'P'
	   ) PK
WHERE TC.TABLE_NAME = TCOMMENT.TABLE_NAME
	 AND TC.TABLE_NAME = CCOMMENT.TABLE_NAME
	 AND TC.COLUMN_NAME = CCOMMENT.COLUMN_NAME
	 AND TC.TABLE_NAME = PK.TABLE_NAME(+)
	 AND TC.COLUMN_NAME = PK.COLUMN_NAME(+)
	 AND TC.TABLE_NAME LIKE 'TB_%' AND TC.TABLE_NAME NOT LIKE '%_HIST'
	 AND TCOMMENT.TABLE_TYPE = 'TABLE'
ORDER BY TC.TABLE_NAME,TC.COLUMN_ID;
</code>

adminschool.net wiki

사용자 도구

사이트 도구

문서 도구