EcalCondDB/sql/ASTEP1.sql

0001 q/* initial setup */
0002 SET NEWPAGE 0
0003 SET SPACE 0
0004 SET LINESIZE 250
0005 SET PAGESIZE 0
0006 SET ECHO OFF
0007 SET FEEDBACK OFF
0008 SET HEADING OFF
0009 SET MARKUP HTML OFF
0010 SET LONG 90000;
0011
0012 /* create instructions to recreate the tables */
0013 spool ddl_list.sql
0014   select 'select dbms_metadata.get_ddl(''TABLE'', ',
0015           concat(concat('''', regexp_replace(table_name, ' *', '')),''''),
0016           ', ''CMS_ECAL_COND'') from dual;'
0017    from         user_tables;
0018   select 'spool pks.data' from dual;
0019   select 'select t.table_name, column_name from user_tab_columns t join ',
0020          'user_constraints c on t.table_name = c.table_name where ',
0021          'column_name like ''%IOV_ID%'' and constraint_type = ''P'';' from dual;
0022   select 'spool off' from dual;
0023 spool off;
0024
0025 /* list triggers: not used but we profit to save them */
0026 SPOOL triggers.data;
0027 SELECT TRIGGER_NAME, TRIGGER_TYPE, TABLE_NAME, REFERENCING_NAMES, ACTION_TYPE, \
0028 TRIGGER_BODY FROM USER_TRIGGERS;
0029
0030 SPOOL OFF;
0031 /
0032
0033 /* list functions: not used but we profit to save them */
0034 SPOOL functions.data;
0035 SELECT NAME, LINE, TEXT FROM USER_SOURCE WHERE TYPE = 'FUNCTION';
0036
0037 SPOOL OFF;
0038 /
0039
0040 /* list procedures: not used but we profit to save them */
0041 SPOOL procedures.data;
0042 SELECT NAME, LINE, TEXT FROM USER_SOURCE WHERE TYPE = 'PROCEDURE';
0043
0044 SPOOL OFF;
0045 /
0046 /* list indexes */
0047 SPOOL indexes.data;
0048 COLUMN COLUMN_NAME FORMAT A35;
0049 COLUMN TABLE_NAME FORMAT A35;
0050 COLUMN INDEX_NAME FORMAT A35;
0051 SELECT I.INDEX_NAME, I.TABLE_NAME, C.COLUMN_NAME FROM USER_INDEXES I JOIN
0052         USER_IND_COLUMNS C ON I.INDEX_NAME = C.INDEX_NAME;
0053 SPOOL OFF;
0054 /
0055
0056 /*
0057  get the current size of each user table as well as
0058  the name of the IOV type column
0059 */
0060
0061 CREATE TABLE TSIZE AS
0062 SELECT T.TABLE_NAME TNAME, T.COLUMN_NAME COLNAME,
0063         (S.BYTES/1024/1024/1024) GB
0064              FROM USER_TAB_COLS T, USER_ALL_TABLES A, USER_SEGMENTS S
0065              WHERE T.TABLE_NAME = A.TABLE_NAME
0066                 AND
0067                (T.COLUMN_NAME LIKE '%IOV%' AND T.DATA_TYPE LIKE '%NUMBER%')
0068                AND S.SEGMENT_NAME = T.TABLE_NAME ORDER BY BYTES ASC
0069 /
0070
0071 SELECT * FROM TSIZE;
0072
0073 /* create a script that gets info from the tables */
0074 SPOOL GETIOVS.sql
0075 SELECT 'SELECT ''', TNAME, ' ', COLNAME, ' ', GB, ''', MAX(', COLNAME, ') S ',
0076         'FROM ',
0077         TNAME , ';'
0078         FROM TSIZE;
0079 SPOOL OFF
0080
0081 DROP TABLE TSIZE;
0082
0083 /* write results to a text file */
0084 SPOOL partition.data
0085 @GETIOVS
0086 SPOOL OFF
0087
0088 /* create script to recreate the tables */
0089 spool recreate.sql
0090 @ddl_list
0091 spool off
0092 /
0093
0094 /* disable constraints */
0095 SPOOL DISABLECONSTRAINTS.sql
0096
0097 SELECT 'SET ECHO ON;' FROM DUAL;
0098 SELECT 'ALTER TABLE ', TABLE_NAME, ' DISABLE CONSTRAINT ', CONSTRAINT_NAME,
0099         ' CASCADE;' FROM
0100         USER_CONSTRAINTS WHERE TABLE_NAME NOT LIKE 'BIN%' AND CONSTRAINT_NAME
0101         LIKE '%PK';
0102
0103 SELECT 'ALTER TABLE ', TABLE_NAME, ' DISABLE CONSTRAINT ', CONSTRAINT_NAME,
0104         ' CASCADE;' FROM
0105         USER_CONSTRAINTS WHERE TABLE_NAME NOT LIKE 'BIN%' AND CONSTRAINT_NAME
0106         LIKE '%FK';
0107 SELECT 'SET ECHO OFF;' FROM DUAL;
0108
0109 SPOOL OFF;
0110 /
0111
0112 /* re-enable constraints */
0113 SPOOL ENABLECONSTRAINTS.sql
0114
0115 SELECT 'SET ECHO ON;' FROM DUAL;
0116 SELECT 'ALTER TABLE ', TABLE_NAME, ' ENABLE CONSTRAINT ', CONSTRAINT_NAME,
0117         ';' FROM
0118         USER_CONSTRAINTS WHERE TABLE_NAME NOT LIKE 'BIN%' AND CONSTRAINT_NAME
0119         LIKE '%PK';
0120
0121 SELECT 'ALTER TABLE ', TABLE_NAME, ' ENABLE CONSTRAINT ', CONSTRAINT_NAME,
0122         ';' FROM
0123         USER_CONSTRAINTS WHERE TABLE_NAME NOT LIKE 'BIN%' AND CONSTRAINT_NAME
0124         LIKE '%FK';
0125 SELECT 'SET ECHO OFF;' FROM DUAL;
0126
0127 SPOOL OFF;
0128 /
0129