001: // Defines application wide constants
002: package com.pk;
003:
004: //import java.awt.*;
005:
006: public interface Constants {
007: // Element type definitions
008: int LINE = 101;
009: int RECTANGLE = 102;
010: int CIRCLE = 103;
011: int CURVE = 104;
012:
013: // Initial conditions
014: //String DEFAULT_ELEMENT_TYPE = " ";
015: String DEFAULT_ELEMENT_USER = "";
016:
017: // SQL Statements
018:
019: String QUERYPLANSQL = //"SELECT id||' ' id,parent_id||' ' p_id, level||' ' lev, lpad(operation, length(operation) + 4*(level-1)) ||\n" +
020: "SELECT ' '||level||' ' lev, lpad(operation, length(operation) + 4*(level-1)) ||\n"
021: + "decode(id, 0, ' Optimizer='||optimizer, null) ||' '||options||\n"
022: + "decode(object_name,null,null,' OF ')||object_name||\n"
023: + "decode(object_type,'UNIQUE', ' (U) ', 'NON-UNIQUE',\n"
024: + "'(NU)',null) plan \n"
025: +
026: //'(Cost = '||COST||' Card='||CARDINALITY||' Bytes='||BYTES||')') plan \n"+
027: "FROM PLAN_TABLE \n"
028: + "START with ID = 0 and STATEMENT_ID = 'pk00001' \n"
029: + "CONNECT by prior ID = PARENT_ID and STATEMENT_ID = 'pk00001' \n";
030:
031: String DELETEPLANSQL = "DELETE FROM PLAN_TABLE";
032:
033: String DBINFOSQL = /* DB info(controlfile, logfile, datafile, tablespace)¸¦ º¸¿©ÁÜ(size : Mbyte) */
034: "SELECT 'DATA' AS KIND,TABLESPACE_NAME AS NAME, \n"
035: + "FILE_NAME AS FILENAME, BYTES/(1024*1024) AS SZ_MB \n"
036: + "FROM DBA_DATA_FILES \n" + "UNION ALL \n"
037: + "SELECT 'LOG ' AS KIND,TO_CHAR(V1.GROUP#) AS NAME, \n"
038: + "V1.MEMBER AS FILENAME, V2.BYTES/(1024*1024) AS SZ_MB \n"
039: + "FROM V$LOGFILE V1, V$LOG V2 \n"
040: + "WHERE V1.GROUP# = V2.GROUP# \n" + "UNION ALL \n"
041: + "SELECT 'CTL ' AS KIND,'Control' AS NAME, \n"
042: + "NAME AS FILENAME , 0 AS SZ_MB \n"
043: + "FROM V$CONTROLFILE \n";
044:
045: String DFINFOSQL = /* datafileº°·Î size¸¦ Mbyte´ÜÀ§·Î º¸¿©ÁÜ */
046: "SELECT TABLESPACE_NAME AS TBS_NAME, \n"
047: + "FILE_ID AS ID, FILE_NAME, ROUND(BYTES/(1024*1024),1) AS SZ_MB \n"
048: + "FROM DBA_DATA_FILES " + "ORDER BY TABLESPACE_NAME,ID";
049:
050: String DFUSAGESQL = /* datafileº° usage(Àüü°ø°£, ¿©À¯°ø°£, »ç¿ëÀ²) */
051: "SELECT A.TABLESPACE_NAME, A.FILE_NAME, \n"
052: + "ROUND(SUM(A.BYTES)/(1024.*1024.),1) AS TOT_MB, \n"
053: + "ROUND(NVL(SUM(SZ_MB),0),1) AS FREE_MB, \n"
054: + "ROUND(NVL(( 1. - SUM(SZ_MB)/(SUM(A.BYTES)/(1024.*1024.)) ) * 100,100),1) AS \"USAGE%\" \n"
055: + "FROM DBA_DATA_FILES A, \n"
056: + "( SELECT FILE_ID , SUM(BYTES)/(1024.*1024.) AS SZ_MB \n"
057: + "FROM DBA_FREE_SPACE \n" + "GROUP BY FILE_ID ) B \n"
058: + "WHERE A.FILE_ID = B.FILE_ID (+) \n"
059: + "GROUP BY A.TABLESPACE_NAME,A.FILE_NAME";
060:
061: String DBOPTIONSQL = /* ¾î¶² DB optionÀÌ installµÇ¾î ÀÖ´ÂÁö º¸¿©ÁÜ */
062: "SELECT PARAMETER,VALUE \n" + "FROM V$OPTION \n"
063: + "ORDER BY PARAMETER";
064:
065: String TBSGENERALSQL = /* tablespaceÅ©±â,»óÅ ¹× ÇØ´ç datafileÀÇ °¹¼ö¸¦ º¸¿©ÁÜ */
066: "SELECT A.TABLESPACE_NAME, \n"
067: + "COUNT(*) AS DF_CNT, SUM(B.BYTES/(1024*1024)) AS SZ_MB, \n"
068: + "MAX(A.STATUS) AS STATUS, MAX(A.CONTENTS) AS CONTENTS \n"
069: + "FROM DBA_TABLESPACES A, DBA_DATA_FILES B "
070: + "WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME "
071: + "GROUP BY A.TABLESPACE_NAME ";
072:
073: String TBSUSAGESQL = /* tablespaceº° usage(Àüü°ø°£, ¿©À¯°ø°£, »ç¿ëÀ²) */
074: /* ġȯ! »ç¿ëÀ²(&vLIMIT)À» Á¶°ÇÀ¸·Î Á¶È¸ */
075: "SELECT A.TABLESPACE_NAME, \n"
076: + "SUM(A.BYTES)/(1024*1024) AS TOT_MB, \n"
077: + "NVL(SUM(SZ_MB),0) AS FREE_MB, \n"
078: + "NVL(( 1. - SUM(SZ_MB)/(SUM(A.BYTES)/(1024.*1024.)) ) * 100,100) AS \"USAGE%\" \n"
079: + "FROM DBA_DATA_FILES A, "
080: + "( SELECT FILE_ID , SUM(BYTES)/(1024.*1024.) AS SZ_MB \n"
081: + "FROM DBA_FREE_SPACE \n"
082: + "GROUP BY FILE_ID ) B \n"
083: + "WHERE A.FILE_ID = B.FILE_ID (+) \n"
084: + "GROUP BY A.TABLESPACE_NAME \n"
085: + "HAVING NVL(( 1. - SUM(SZ_MB)/(SUM(A.BYTES)/(1024.*1024.)) ) * 100,100) >= &vLIMIT ";
086:
087: String SEGLISTSQL = /* tablespaceº°·Î segmentÀÇ Á¤º¸ */
088: /* (extent¼ö,segmentÅ©±â,tablespace¿¡¼ÀÇ °ø°£Â÷ÁöºñÀ²)À» º¸¿©ÁÜ */
089: /* ¸¶Áö¸·¿¡ tablespaceÀÇ »ç¿ëÀ²À» º¸¿©ÁÜ */
090: /* tablespaceº°·Î Á¶°Ç Á¶È¸ °¡´É */
091: "SELECT NULL DUMMY, \n" + "B.TABLESPACE_NAME AS TBS_NM, \n"
092: + "A.OWNER, \n" + "A.SEGMENT_TYPE AS TYPE, \n"
093: + "A.SEGMENT_NAME AS NAME, \n" + "A.EXTENTS as Ext, \n"
094: + "A.BYTES/1024 AS TOT_KB, \n"
095: + "( A.BYTES/1024 / B.TBS_KB ) * 100 AS \"TBS USAGE%\" \n"
096: + "FROM DBA_SEGMENTS A, \n"
097: + "( SELECT TABLESPACE_NAME ,SUM(BYTES)/1024 AS TBS_KB \n"
098: + "FROM DBA_DATA_FILES \n"
099: + "WHERE TABLESPACE_NAME LIKE UPPER('&TBS_NAME')||'%' \n"
100: + "GROUP BY TABLESPACE_NAME ) B \n"
101: + "WHERE B.TABLESPACE_NAME = A.TABLESPACE_NAME (+) \n"
102: + "ORDER BY TBS_NM, OWNER, SEGMENT_NAME";
103:
104: String SEGUSAGESQL = /* table, indexÀÇ storageÁ¤º¸ ¹× ÃÑ»çÀÌÁî, extent¹ß»ýÁ¤µµ¸¦ º¸¿©ÁÜ*/
105: /* extent¹ß»ýÁ¤µµ, segment_nameÀ¸·Î Á¶°Ç Á¶È¸ °¡´É */
106: "SELECT DECODE(SIGN(EXTENTS+10-MAX_EXTENTS),1,'V') as MK, \n"
107: + "OWNER, SEGMENT_NAME OBJECT_NAME,SEGMENT_TYPE OBJECT_TYPE, \n"
108: + "INITIAL_EXTENT/1024 AS INI_KB, \n"
109: + "NEXT_EXTENT/1024 AS NXT_KB , \n"
110: + "PCT_INCREASE AS PCT, MAX_EXTENTS AS MAXT, \n"
111: + "EXTENTS AS EXT, \n" + "BYTES/1024 AS TOT_KB, \n"
112: + "TABLESPACE_NAME \n" + "FROM DBA_SEGMENTS \n"
113: + "WHERE OWNER NOT IN ('SYS') \n"
114: + "AND SEGMENT_TYPE IN ( 'TABLE','INDEX') \n"
115: + "AND EXTENTS >= &extent_num \n"
116: + "AND SEGMENT_NAME LIKE UPPER('&seg_name')||'%' \n"
117: + "ORDER BY OWNER, SEGMENT_NAME";
118:
119: String SEGSTORAGESQL = /* tbs_usage.sql + seg_usage */
120: /* »ç¿ëÀ²·Î Á¶°Ç Á¶È¸ + extent¹ß»ýÁ¤µµ·Î Á¶°Ç Á¶È¸ °¡´É */
121: "SELECT A.OWNER, \n" + "A.SEGMENT_NAME, \n"
122: + "A.INITIAL_EXTENT/1024 AS INI_KB, \n"
123: + "A.NEXT_EXTENT/1024 AS NXT_KB , \n"
124: + "A.PCT_INCREASE AS PCT, \n"
125: + "B.NUM_ROWS, A.EXTENTS AS EXT, \n"
126: + "A.BYTES/1024 AS TOT_KB, \n" + "A.TABLESPACE_NAME \n"
127: + "FROM DBA_SEGMENTS A, DBA_TABLES B \n"
128: + "WHERE A.OWNER NOT IN ('SYS') \n"
129: + "AND A.OWNER = B.OWNER(+) \n"
130: + "AND A.SEGMENT_NAME = B.TABLE_NAME(+) \n"
131: + "AND EXTENTS >= &extent_num \n"
132: + "ORDER BY A.SEGMENT_NAME ";
133:
134: String SEGSHORTAGEDETECTIONSQL = /* next extent¸¦ ÀÏÀ¸Å³ ¶§, tablespace°¡ ºÎÁ·ÇÑ segmentÀÇ Á¤º¸¸¦ º¸¿©ÁÜ */
135: "SELECT SEGMENT_NAME, SEGMENT_TYPE, EXTENTS, BYTES/1024 AS SZ_KB, TABLESPACE_NAME, \n"
136: + "NEXT_EXTENT/1024 NEXT_KB, PCT_INCREASE PCT, INITIAL_EXTENT/1024 INI_KB, \n"
137: + "MAX_EXTENTS MAXEXT \n"
138: + "FROM DBA_SEGMENTS A \n"
139: + "WHERE NOT EXISTS (SELECT 'X' \n"
140: + " FROM DBA_FREE_SPACE B \n"
141: + " WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME \n"
142: + " AND B.BYTES >= A.NEXT_EXTENT)";
143:
144: String SEGRBSUSAGESQL = "SELECT A.SEGMENT_NAME AS RB_SEG, \n"
145: + "A.STATUS, C.XACTS AS xT, \n"
146: + "B.INITIAL_EXTENT/1024 AS INI_KB, \n"
147: + "B.NEXT_EXTENT/1024 AS NXT_KB , \n"
148: + "B.EXTENTS AS EXT, \n"
149: + "B.BYTES/1024 AS TOT_KB, \n"
150: + "A.TABLESPACE_NAME AS TBS_NM \n"
151: + "FROM DBA_ROLLBACK_SEGS A, DBA_SEGMENTS B, V$ROLLSTAT C \n"
152: + "WHERE A.SEGMENT_NAME = B.SEGMENT_NAME \n"
153: + "AND A.SEGMENT_ID = C.USN \n"
154: + "ORDER BY A.SEGMENT_NAME";
155:
156: String SEGRBSWAITRATIOSQL = "SELECT NAME AS RBS_NAME, \n"
157: + "RSSIZE, WRITES, XACTS, STATUS, \n"
158: + "TRUNC(WAITS/GETS*100, 5)||' %' MISS_RATIO \n"
159: + "FROM V$ROLLSTAT, V$ROLLNAME \n"
160: + "WHERE V$ROLLSTAT.USN = V$ROLLNAME.USN \n"
161: + "ORDER BY WAITS/GETS DESC";
162:
163: String SEGTABLEUSAGESQL = "SELECT DECODE(SIGN(EXTENTS+10-MAX_EXTENTS),1,'V') as MK, \n"
164: + "OWNER, SEGMENT_NAME OBJECT_NAME, \n"
165: + "INITIAL_EXTENT/1024 AS INI_KB, \n"
166: + "NEXT_EXTENT/1024 AS NXT_KB , \n"
167: + "PCT_INCREASE AS PCT, MAX_EXTENTS AS MAXT, \n"
168: + "EXTENTS AS EXT, \n"
169: + "BYTES/1024 AS TOT_KB, \n"
170: + "TABLESPACE_NAME \n"
171: + "FROM DBA_SEGMENTS \n"
172: + "WHERE OWNER NOT IN ('SYS') \n"
173: + "AND SEGMENT_TYPE = 'TABLE' \n"
174: + "AND EXTENTS >= &extent_num \n"
175: + "AND SEGMENT_NAME LIKE UPPER('&table_name')||'%' \n"
176: + "ORDER BY OWNER, SEGMENT_NAME";
177:
178: String USERGENERALSQL = "SELECT USERNAME, \n"
179: + "DEFAULT_TABLESPACE AS DEF_TBS, \n"
180: + "TEMPORARY_TABLESPACE AS TEMP_TBS, \n" + "PROFILE, \n"
181: + "TO_CHAR(CREATED,'YYYY.MM.DD') AS CREATED \n"
182: + "FROM DBA_USERS";
183:
184: String OBJECTSSTATUSPERUSERSQL = "SELECT A.OWNER, NVL(A.TAB,0) AS TAB, NVL(A.IDX,0) AS IDX, NVL(A.VEW,0) AS VEW, \n"
185: + " NVL(A.SYN,0) AS SYN, NVL(A.SEQ,0) AS SEQ, NVL(A.TRG,0) AS TRG, \n"
186: + " NVL(A.PRO,0) AS PRO, NVL(B.PK,0) AS PK#, NVL(B.FK,0) AS FK#, \n"
187: + " NVL(A.DBL,0) AS DBL, NVL(A.PRO,0) AS PRO, NVL(A.FUN,0) AS FUN, \n"
188: + " NVL(A.PKG,0) AS PKG, NVL(A.CLU,0) AS CLU, NVL(A.PKB,0) AS PKB \n"
189: + "FROM \n"
190: + "(SELECT OWNER, \n"
191: + " COUNT(DECODE(OBJECT_TYPE,'TABLE',1)) AS TAB, \n"
192: + " COUNT(DECODE(OBJECT_TYPE,'INDEX',1)) AS IDX, \n"
193: + " COUNT(DECODE(OBJECT_TYPE,'VIEW',1)) AS VEW, \n"
194: + " COUNT(DECODE(OBJECT_TYPE,'SYNONYM',1)) AS SYN, \n"
195: + " COUNT(DECODE(OBJECT_TYPE,'SEQUENCE',1)) AS SEQ, \n"
196: + " COUNT(DECODE(OBJECT_TYPE,'TRIGGER',1)) AS TRG, \n"
197: + " COUNT(DECODE(OBJECT_TYPE,'DATABASE LINK',1)) AS DBL, \n"
198: + " COUNT(DECODE(OBJECT_TYPE,'PROCEDURE',1)) AS PRO, \n"
199: + " COUNT(DECODE(OBJECT_TYPE,'FUNCTION',1)) AS FUN, \n"
200: + " COUNT(DECODE(OBJECT_TYPE,'PACKAGE',1)) AS PKG, \n"
201: + " COUNT(DECODE(OBJECT_TYPE,'PACKAGE BODY',1)) AS PKB, \n"
202: + " COUNT(DECODE(OBJECT_TYPE,'CLUSTER',1)) AS CLU \n"
203: + "FROM DBA_OBJECTS \n"
204: + "GROUP BY OWNER ) A, \n"
205: + "(SELECT OWNER, \n"
206: + " SUM(DECODE(CONSTRAINT_TYPE,'P',1)) AS PK, \n"
207: + " SUM(DECODE(CONSTRAINT_TYPE,'R',1)) AS FK \n"
208: + " FROM DBA_CONSTRAINTS \n"
209: + " GROUP BY OWNER ) B \n"
210: + "WHERE A.OWNER = B.OWNER(+) ";
211:
212: String OBJECTSTABLESTATUSSQL = "SELECT DECODE(SIGN(EXTENTS+10-MAX_EXTENTS),1,'V') as MK, \n"
213: + " OWNER, SEGMENT_NAME, \n"
214: + " INITIAL_EXTENT/1024 AS INI_KB, \n"
215: + " NEXT_EXTENT/1024 AS NXT_KB , \n"
216: + " PCT_INCREASE AS PCT, MAX_EXTENTS, \n"
217: + " EXTENTS, \n"
218: + " INITIAL_EXTENT/1024 + NEXT_EXTENT/1024 * ( EXTENTS - 1 ) AS TOT_KB, \n"
219: + " TABLESPACE_NAME \n"
220: + "FROM DBA_SEGMENTS \n"
221: + "WHERE OWNER NOT IN ('SYS') \n"
222: + "AND SEGMENT_TYPE = 'TABLE' \n"
223: + "AND EXTENTS >= &extent_num \n"
224: + "AND SEGMENT_NAME LIKE UPPER('&table_name')||'%' \n"
225: + "ORDER BY OWNER, SEGMENT_NAME";
226:
227: String OBJECTSINDEXCOLUMNSTATUSSQL = "SELECT OWNER,TABLE_NAME,INDEX_NAME, TABLESPACE_NAME \n"
228: + "FROM DBA_INDEXES \n"
229: + "WHERE OWNER NOT IN ('SYS') \n"
230: + "AND INDEX_NAME NOT LIKE 'PK%' \n"
231: + "ORDER BY OWNER,TABLE_NAME";
232:
233: String OBJECTSVIEWSTATUSPERUSERSQL = "SELECT OWNER, OBJECT_NAME, \n"
234: + "OBJECT_TYPE, STATUS \n"
235: + "FROM DBA_OBJECTS \n"
236: + "WHERE OWNER NOT IN ('SYS') \n"
237: + "AND OBJECT_TYPE = 'VIEW' \n"
238: + "ORDER BY OWNER,OBJECT_NAME";
239:
240: String OBJECTSSTOREDPROCEDURESTATUSSQL = "SELECT OWNER, OBJECT_NAME, OBJECT_TYPE AS TYPE, STATUS \n"
241: + "FROM DBA_OBJECTS \n"
242: + "WHERE OWNER NOT IN ('SYS') \n"
243: + "AND OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY' ) \n"
244: + "ORDER BY OWNER,OBJECT_TYPE, OBJECT_NAME";
245:
246: String OBJECTSTRIGGERSTATUSSQL = "SELECT OWNER,TRIGGER_NAME, TABLE_OWNER,TABLE_NAME,STATUS \n"
247: + "FROM DBA_TRIGGERS \n"
248: + "WHERE OWNER NOT IN ('SYS') \n"
249: + "ORDER BY OWNER,TRIGGER_NAME \n";
250:
251: String OBJECTSSEQUENCESTATUSSQL = "SELECT SEQUENCE_OWNER,SEQUENCE_NAME, MIN_VALUE, MAX_VALUE,INCREMENT_BY, \n"
252: + "LAST_NUMBER, CYCLE_FLAG \n"
253: + "FROM DBA_SEQUENCES \n"
254: + "WHERE SEQUENCE_OWNER NOT IN ('SYS') \n"
255: + "ORDER BY SEQUENCE_OWNER,SEQUENCE_NAME";
256:
257: String OBJECTSSYNONYMSTATUSSQL = "SELECT A.OWNER,A.SYNONYM_NAME, \n"
258: + "A.TABLE_OWNER ,A.TABLE_NAME , \n"
259: + "MAX(DECODE(B.PRIVILEGE,'SELECT','S '))|| \n"
260: + "MAX(DECODE(B.PRIVILEGE,'INSERT','I '))|| \n"
261: + "MAX(DECODE(B.PRIVILEGE,'UPDATE','U '))|| \n"
262: + "MAX(DECODE(B.PRIVILEGE,'DELETE','D '))|| \n"
263: + "MAX(DECODE(B.PRIVILEGE,'REFERENCES','R ')) AS PRIVS, \n"
264: + "MAX(TO_CHAR(C.CREATED,'YYYY.MM.DD')) AS CREATED \n"
265: + "FROM DBA_SYNONYMS A, DBA_TAB_PRIVS B, DBA_OBJECTS C \n"
266: + "WHERE A.OWNER NOT IN ('SYS') \n"
267: + "AND A.OWNER = B.GRANTEE \n"
268: + "AND A.OWNER = C.OWNER AND A.SYNONYM_NAME = C.OBJECT_NAME \n"
269: + "AND C.OBJECT_TYPE = 'SYNONYM' \n"
270: + "AND A.TABLE_NAME = B.TABLE_NAME \n"
271: + "AND A.OWNER LIKE UPPER('&user_name')||'%' \n"
272: + "AND A.SYNONYM_NAME LIKE UPPER('&synonym_name')||'%' \n"
273: + "GROUP BY A.OWNER, A.SYNONYM_NAME, \n"
274: + " A.TABLE_OWNER,A.TABLE_NAME";
275:
276: String OBJECTSPKCONSTRAINTSQL = "SELECT A.OWNER, A.TABLE_NAME, A.CONSTRAINT_NAME, \n"
277: + " 'PK' AS TYPE, B.TABLESPACE_NAME \n"
278: + "FROM DBA_CONSTRAINTS A, DBA_INDEXES B \n"
279: + "WHERE A.OWNER NOT IN ('SYS') \n"
280: + "AND A.CONSTRAINT_TYPE IN ('P') \n"
281: + "AND A.CONSTRAINT_NAME = B.INDEX_NAME(+) \n"
282: + "AND A.OWNER = B.OWNER(+) \n"
283: + "ORDER BY A.OWNER,A.TABLE_NAME ";
284:
285: String OBJECTSFKCONSTRAINTSQL = "SELECT A.OWNER, A.TABLE_NAME, A.CONSTRAINT_NAME, \n"
286: + " 'FK' AS TYPE, \n"
287: + " DECODE(A.CONSTRAINT_TYPE,'R',A.R_OWNER||'.'|| \n"
288: + " B.TABLE_NAME ) AS REF_TABLE, \n"
289: + " A.STATUS \n"
290: + "FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B \n"
291: + "WHERE A.OWNER NOT IN ('SYS') \n"
292: + "AND A.CONSTRAINT_TYPE IN ('R') \n"
293: + "AND A.R_OWNER = B.OWNER(+) \n"
294: + "AND A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME(+) \n"
295: + "ORDER BY A.OWNER, A.TABLE_NAME ";
296:
297: String OBJECTSDBLINKINFOSQL = "SELECT OWNER, \n"
298: + " DB_LINK, \n"
299: + " ' USER> '||SUBSTR(NVL(USERNAME,' ')|| \n"
300: + " ', HOST> '||HOST,1,22) AS CONN_DB, \n"
301: + " TO_CHAR(CREATED,'YYYY.MM.DD') AS CREATED \n"
302: + "FROM DBA_DB_LINKS \n" + "ORDER BY OWNER, DB_LINK ";
303:
304: String TABLEESTIMATEDSIZESQL = "SELECT A.OWNER,A.TABLE_NAME, A.AVG_ROW_LEN AS ,A.NUM_ROWS , \n"
305: + "A.TABLESPACE_NAME, \n"
306: + "A.CLUSTER_NAME , \n"
307: + "TO_CHAR(B.CREATED,'YYYY.MM.DD') AS CREATED \n"
308: + "FROM DBA_TABLES A, DBA_OBJECTS B \n"
309: + "WHERE A.OWNER NOT IN ('SYS') \n"
310: + "AND A.OWNER = B.OWNER AND A.TABLE_NAME = B.OBJECT_NAME \n"
311: + "AND A.TABLE_NAME LIKE UPPER('&table_name')||'%' \n"
312: + "ORDER BY A.OWNER,A.TABLE_NAME";
313:
314: String TABLEDESCSQL = "SELECT OWNER,ID,COLUMN_NAME,PK,NN,DATA_TYPE,DATA_LENG,PI,I1,I2,I3,I4,ICNT \n"
315: + "FROM ( \n"
316: + " SELECT MAX(DECODE(FLAG,'P','P',' ')) AS PK, \n"
317: + " OWNER,COLUMN_NAME, \n"
318: + " MAX(DECODE(NULLABLE,'N','Y',NULL)) as NN, \n"
319: + " MAX(DATA_TYPE) AS DATA_TYPE, \n"
320: + " MIN(DATA_LENG) AS DATA_LENG, \n"
321: + " MAX(DECODE(FLAG,'P','I')) AS FLAG, \n"
322: + " MIN(DECODE(FLAG,'P',IDX_ID)) AS PI, \n"
323: + " MIN(DECODE(FLAG||IX,'I1',IDX_ID)) AS I1, \n"
324: + " MIN(DECODE(FLAG||IX,'I2',IDX_ID)) AS I2, \n"
325: + " MIN(DECODE(FLAG||IX,'I3',IDX_ID)) AS I3, \n"
326: + " MIN(DECODE(FLAG||IX,'I4',IDX_ID)) AS I4, \n"
327: + " MAX(COLUMN_ID) AS ID, \n"
328: + " COUNT(DECODE(FLAG,'I',1,'P',1)) AS ICNT \n"
329: + " FROM ( SELECT OWNER , COLUMN_NAME, '' AS FLAG, DATA_TYPE, \n"
330: + " '('||DECODE(DATA_TYPE,'NUMBER',NVL(DATA_PRECISION,DATA_LENGTH),DATA_LENGTH) \n"
331: + " ||DECODE(DATA_TYPE,'NUMBER', \n"
332: + " DECODE(DATA_SCALE,NULL,')',','||TO_CHAR(DATA_SCALE)||')'),')') \n"
333: + " DATA_LENG, \n"
334: + " NULLABLE,COLUMN_ID, NULL AS IX, 0 AS IDX_ID \n"
335: + " FROM DBA_TAB_COLUMNS \n"
336: + " WHERE TABLE_NAME = UPPER('&table_name') \n"
337: + " UNION ALL \n"
338: + " SELECT B.INDEX_OWNER,B.COLUMN_NAME, \n"
339: + " DECODE(A.CONSTRAINT_TYPE,'P','P','I' ) AS FLAG,'', \n"
340: + " '0' , '' , 0, \n"
341: + " SUBSTR(INDEX_NAME,-1,1) AS IX,COLUMN_POSITION AS IDX_ID \n"
342: + " FROM DBA_CONSTRAINTS A, DBA_IND_COLUMNS B \n"
343: + " WHERE A.CONSTRAINT_NAME(+) = B.INDEX_NAME \n"
344: + " AND A.OWNER (+) = B.INDEX_OWNER \n"
345: + " AND B.TABLE_NAME = UPPER('&table_name') \n"
346: + " ) \n"
347: + " GROUP BY OWNER,COLUMN_NAME ) \n"
348: + " ORDER BY OWNER,ID ";
349:
350: String TABLEREALSIZESQL = "SELECT A.TABLE_NAME AS TNAME, B.BYTES/1024. AS TOTAL_KB, B.EXTENTS , \n"
351: + " ROUND((A.BLOCKS * A.AVG_SPACE + A.EMPTY_BLOCKS * &db_block_size)/1024.,1) FREE_SZ_KB, \n"
352: + " ROUND((B.BYTES - (A.BLOCKS * A.AVG_SPACE + A.EMPTY_BLOCKS * &db_block_size))/1024.,1) REAL_SZ_KB \n"
353: + "FROM DBA_TABLES A, DBA_SEGMENTS B \n"
354: + "WHERE A.OWNER = B.OWNER \n"
355: + "AND A.TABLE_NAME = B.SEGMENT_NAME \n"
356: + "AND A.OWNER LIKE UPPER('&user_name') \n"
357: + "AND A.TABLE_NAME LIKE UPPER('&table_name')";
358:
359: String TABLEFREESPACESQL = "SELECT TABLE_NAME AS TNAME, EMPTY_BLOCKS, BLOCKS, AVG_SPACE, \n"
360: + " ROUND((BLOCKS * AVG_SPACE + EMPTY_BLOCKS * &db_block_size)/1024.,1) FREE_KB \n"
361: + "FROM DBA_TABLES \n"
362: + "WHERE OWNER LIKE UPPER('&user_name') \n"
363: + "AND TABLE_NAME LIKE UPPER('&table_name')";
364:
365: String INDEXLISTSQL = "SELECT A.OWNER,A.TABLE_NAME,A.INDEX_NAME, A.TABLESPACE_NAME, \n"
366: + "TO_CHAR(B.CREATED,'YYYY.MM.DD') AS CREATED \n"
367: + "FROM DBA_INDEXES A, DBA_OBJECTS B \n"
368: + "WHERE A.OWNER NOT IN ('SYS') \n"
369: + "AND A.OWNER = B.OWNER AND A.INDEX_NAME = B.OBJECT_NAME \n"
370: + "AND A.OWNER LIKE UPPER('&user_name')||'%' \n"
371: + "AND A.TABLE_NAME LIKE UPPER('&table_name')||'%' \n"
372: + "ORDER BY A.OWNER,A.TABLE_NAME";
373:
374: String INDEXCOLUMNLISTSQL = "SELECT TABLE_OWNER AS OWNER,TABLE_NAME,INDEX_NAME, \n"
375: + "COLUMN_POSITION AS ID,COLUMN_NAME \n"
376: + "FROM DBA_IND_COLUMNS \n"
377: + "WHERE TABLE_OWNER NOT IN ('SYS') \n"
378: + "AND TABLE_OWNER LIKE UPPER('&user_name')||'%' \n"
379: + "AND TABLE_NAME LIKE UPPER('&table_name')||'%' \n"
380: + "ORDER BY TABLE_OWNER,TABLE_NAME,INDEX_NAME,COLUMN_POSITION";
381:
382: String INDEXUSAGESQL = "SELECT DECODE(SIGN(EXTENTS+10-MAX_EXTENTS),1,'V') as MK, \n"
383: + "OWNER, SEGMENT_NAME AS OBJECT_NAME, \n"
384: + "INITIAL_EXTENT/1024 AS INI_KB, \n"
385: + "NEXT_EXTENT/1024 AS NXT_KB , \n"
386: + "PCT_INCREASE AS PCT, MAX_EXTENTS AS MAXT, \n"
387: + "EXTENTS AS EXT, \n"
388: + "BYTES/1024 AS TOT_KB, \n"
389: + "TABLESPACE_NAME \n"
390: + "FROM DBA_SEGMENTS \n"
391: + "WHERE OWNER NOT IN ('SYS') \n"
392: + "AND SEGMENT_TYPE = 'INDEX' \n"
393: + "AND EXTENTS >= &extent_num \n"
394: + "AND SEGMENT_NAME LIKE UPPER('&index_name')||'%' \n"
395: + "ORDER BY OWNER, SEGMENT_NAME ";
396:
397: String CONSTRAINTCOLUMNLISTSQL = "SELECT A.OWNER, A.TABLE_NAME, A.CONSTRAINT_NAME AS CONS, A.COLUMN_NAME, \n"
398: + "B.CONSTRAINT_TYPE AS CONS_TYPE, B.R_OWNER, B.R_CONSTRAINT_NAME AS R_CONS, \n"
399: + "B.DELETE_RULE, B.STATUS, B.SEARCH_CONDITION AS CONDITION \n"
400: + "FROM DBA_CONS_COLUMNS A, DBA_CONSTRAINTS B \n"
401: + "WHERE A.OWNER = B.OWNER \n"
402: + "AND A.TABLE_NAME = B.TABLE_NAME \n"
403: + "AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME \n"
404: + "AND A.OWNER LIKE UPPER('&user_name') \n"
405: + "AND A.TABLE_NAME LIKE UPPER('&table_name') \n"
406: + "ORDER BY POSITION";
407:
408: String CONSTRAINTREFERENCINGTABLESQL = "SELECT A.OWNER, A.OBJECT_NAME, C.NAME CONSTRAINT_NAME \n"
409: + "FROM DBA_OBJECTS A, SYS.CDEF$ B, SYS.CON$ C \n"
410: + "WHERE A.OWNER LIKE UPPER('&owner') \n"
411: + "AND A.OBJECT_NAME LIKE UPPER('&tname') \n"
412: + "AND A.OBJECT_ID = B.ROBJ# \n"
413: + "AND B.CON# = C.CON#";
414:
415: String SESSIONSQLSQL = "SELECT S.USERNAME, S.OSUSER, A.SQL_TEXT \n"
416: + "FROM V$SQLTEXT A, V$SESSION S \n"
417: + "WHERE S.SID = &sid \n" + "AND S.SERIAL# = &serial \n"
418: + "AND A.ADDRESS = S.SQL_ADDRESS \n"
419: + "AND A.HASH_VALUE = S.SQL_HASH_VALUE \n"
420: + "ORDER BY A.PIECE";
421:
422: String SESSIONOBJECTSQL = "SELECT SUBSTR(B.PROCESS,1,5) AS PGM_PS, SUBSTR(B.MACHINE,1,12) AS MACHINE, \n"
423: + " SUBSTR(B.PROGRAM,1,15) AS PROGRAM, B.SID, B.SERIAL# AS SER#, \n"
424: + " B.STATUS, \n"
425: + " DECODE(C.OWNER,NULL,' X ',SUBSTR(C.OWNER||'.'||C.OBJECT,1,22)) as OWN_OBJECT \n"
426: + "FROM V$SESSION B, V$ACCESS C \n"
427: + "WHERE B.SID = C.SID(+) \n"
428: + "AND B.USERNAME IS NOT NULL \n"
429: + "AND B.SID LIKE '&S_ID'||'%' \n"
430: + "AND B.PROCESS = DECODE(&PGM_PROCS,'0',B.PROCESS,&PGM_PROCS) \n"
431: + "AND B.MACHINE LIKE '&vMACHINE'||'%' \n"
432: + "AND ( C.OBJECT LIKE UPPER('&vOBJECT')||'%' OR C.OBJECT IS NULL ) \n"
433: + "ORDER BY PROGRAM";
434:
435: String SESSIONSTATUSSQL = "SELECT SUBSTR(MACHINE,1,12) AS MACHINE,substr(PROGRAM,1,15) AS PROGRAM, \n"
436: + " TO_CHAR(LOGON_TIME,'MM.DD HH24:MI') AS LOGON_TIME, \n"
437: + " SERVER, \n"
438: + " STATUS, \n"
439: + " PROCESS AS PGM_PS, SID,SERIAL# \n"
440: + "FROM V$SESSION \n"
441: + "WHERE TYPE != 'BACKGROUND' \n"
442: + "AND PROCESS = DECODE(&PGM_PROCS,'0',PROCESS,&PGM_PROCS) \n"
443: + "AND SID LIKE '&S_ID'||'%' \n" + "order by 3 ";
444:
445: }
|