dbms:oracle:admin:oracletip:alltabletruncate

차이

문서의 선택한 두 판 사이의 차이를 보여줍니다.

차이 보기로 링크

--- dbms:oracle:admin:oracletip:alltabletruncate [2007/06/22 02:14]
starlits 새로 만듬
+++ dbms:oracle:admin:oracletip:alltabletruncate [2007/06/22 02:15] (현재)
starlits
@@ 줄 1: / 줄 1: @@
-===== 테이블 정의를 출력해주는 쿼리 =====
+===== 전체테이블 TRUNCATE하는 쿼리 만들기  =====
 <code oracle8>
-SQL> SELECT TC.TABLE_NAME
+SQL> select 'truncate table '||table_name||';' from user_tables
-	   ,TCOMMENT.COMMENTS AS KOR_TNAME
+where table_name not in ('TEMPLATE_XML','QUESTION_XML')
-	   ,TC.COLUMN_NAME
+order by table_name asc;
-	   ,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>

dbms/oracle/admin/oracletip/alltabletruncate.1182446089.txt.gz · 마지막으로 수정됨: 2007/06/22 02:14 저자 starlits