DBA script (4)

From: Paultje Bakker <bakker_at_cs.uq.oz.au>
Date: 21 Feb 1994 01:04:10 GMT
Message-ID: <2k91aa$3q6_at_uqcspe.cs.uq.oz.au>

Hi people,

Here are some scripts to recreate tables (structure only), indexes and grants. I hope it is of some use - esp. to new DBAs.

REM RECREATE_TABLES
REM Created 31-OCT-93 by R. Bakker
REM Format: SQL> _at_RECREATE_TABLES table-string REM Writes the file CREATE_TABLES.SQL which contains SQL statements to REM CREATE the given table or group of tables (wildcards are allowed). REM Useful for complicated modifications of a table. REM Restrictions: Can't deduce a table name from its synonym, and REM ignores clusters and constraints.
REM CREATE_TABLES.SQL is empty if the table name given is unknown. REM Handles all (currently) known datatypes (in V6). REM
SET ECHO OFF
SET TERMOUT OFF
SET VERIFY OFF
SET HEADING OFF
SET FEEDBACK OFF
SET EMBEDDED ON
SET PAGESIZE 0
SET LINESIZE 80
SET RECSEP OFF
REM
REM May have to change this to CHAR(10)||CHAR(13) for some configurations. REM
DEFINE CR = "CHR(10)"
SPOOL CREATE_TABLES.SQL
SELECT DECODE(COLS1.COLUMN_ID

        ,1,'CREATE TABLE '||TAB.OWNER||'.'||TAB.TABLE_NAME||' ('||&CR||
           '             '||SUBSTR(RPAD(' ',30),1,LENGTH(TAB.OWNER)-2)||' '
        ,  '             '||SUBSTR(RPAD(' ',30),1,LENGTH(TAB.OWNER)-2)||','
             )||
       RPAD(COLS1.COLUMN_NAME,31)||
       RPAD(
       COLS1.DATA_TYPE||
            DECODE(COLS1.DATA_TYPE


                   ,'NUMBER',DECODE(COLS1.DATA_PRECISION,NULL,NULL,


                                    '('||COLS1.DATA_PRECISION||
                                    DECODE(SIGN(COLS1.DATA_SCALE)
                                           ,1,','||COLS1.DATA_SCALE
                                           ,  NULL
                                          )
                                    ||')')


                   ,'CHAR',    '('||COLS1.DATA_LENGTH||')'

                   ,'DATE',    NULL

                   ,'VARCHAR', '('||COLS1.DATA_LENGTH||')'

                   ,'LONG',    NULL

                   ,'RAW',     '('||COLS1.DATA_LENGTH||')'


                   ,'LONG RAW',NULL


                   ,       '   ???'
                  )
        ,15)||
       DECODE(COLS1.NULLABLE,'Y',NULL,'  NOT NULL')||
       DECODE(COLS1.COLUMN_ID
              ,COLS2.COLUMN_ID,
                ')'||&CR||
                'PCTFREE '||TAB.PCT_FREE||' PCTUSED '||TAB.PCT_USED||
                ' INITRANS '||TAB.INI_TRANS||' MAXTRANS '||TAB.MAX_TRANS||&CR||
                'TABLESPACE '||TAB.TABLESPACE_NAME||&CR||
                'STORAGE (INITIAL '||
                     DECODE(MOD(INITIAL_EXTENT,(1024*1024))
                            ,0,TO_CHAR(INITIAL_EXTENT/(1024*1024))||'M'
                            ,  DECODE(MOD(INITIAL_EXTENT,1024)
                                      ,0,TO_CHAR(INITIAL_EXTENT/1024)||'K'
                                      ,  TO_CHAR(INITIAL_EXTENT)
                                     )
                           )||
                        ' NEXT '||
                     DECODE(MOD(NEXT_EXTENT,(1024*1024))
                            ,0,TO_CHAR(NEXT_EXTENT/(1024*1024))||'M'
                            ,  DECODE(MOD(NEXT_EXTENT,1024)
                                      ,0,TO_CHAR(NEXT_EXTENT/1024)||'K'
                                      ,  TO_CHAR(NEXT_EXTENT)
                                     )
                           )||
                ' MINEXTENTS '||MIN_EXTENTS||' MAXEXTENTS '||MAX_EXTENTS||&CR||
                'PCTINCREASE '||PCT_INCREASE||');'||&CR||&CR
              ,&CR)

FROM DBA_TAB_COLUMNS COLS2,DBA_TAB_COLUMNS COLS1,DBA_TABLES TAB WHERE TAB.OWNER = NVL(UPPER(SUBSTR('&1',1,INSTR('&1','.')-1)),TAB.OWNER) AND TAB.TABLE_NAME LIKE UPPER(SUBSTR('&1',INSTR('&1','.')+1))

AND    COLS1.OWNER = TAB.OWNER AND COLS2.OWNER = TAB.OWNER
AND    COLS1.TABLE_NAME = TAB.TABLE_NAME AND COLS2.TABLE_NAME = TAB.TABLE_NAME
AND    COLS2.COLUMN_ID =
       (SELECT MAX(COLS3.COLUMN_ID)
        FROM   DBA_TAB_COLUMNS COLS3
        WHERE  COLS3.OWNER = TAB.OWNER
        AND    COLS3.TABLE_NAME = TAB.TABLE_NAME)

ORDER BY COLS1.OWNER,COLS1.TABLE_NAME,COLS1.COLUMN_ID; SPOOL OFF
SET EMBEDDED OFF
CLEAR COLUMNS
SET RECSEP WRAP
SET NEWPAGE 1
SET PAGESIZE 999
SET HEADING ON
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON REM RECREATE_INDEXES.SQL
REM Format: SQL> _at_RECREATE_INDEXES table REM Writes the file CREATE_INDEXES.SQL which contains SQL statements REM to CREATE all the indexes on the given tables (wildcards are allowed, REM as is specifying the owner). Useful when dropping and REM recreating a table.
REM Restrictions: Can't deduce a table name from its synonym. REM CREATE_INDEXES.SQL is empty if the table name given is unknown. REM ASC/DESC, PCTFREE and NOSORT not taken into account because I REM don't know how! CLUSTER left out on purpose. REM
REM Leave out as many blanks as possible if there's only one index column, REM as it has to carry the opening and closing clause, and can thus run REM into the 255-char limit on VMS.
REM
SET ECHO OFF
SET TERMOUT OFF
SET VERIFY OFF
SET HEADING OFF
SET FEEDBACK OFF
SET EMBEDDED ON
SET PAGESIZE 0
SET LINESIZE 80
SET RECSEP OFF
REM
REM May have to change this to CHAR(10)||CHAR(13) for some configurations REM
DEFINE CR = "CHR(10)"
SPOOL CREATE_INDEXES.SQL
SELECT DECODE(COLS1.COLUMN_POSITION

        ,1,'CREATE '||DECODE(IND.UNIQUENESS,'UNIQUE','UNIQUE ','')||
           'INDEX '||IND.OWNER||'.'||IND.INDEX_NAME||' ON '||&CR||
           DECODE(COLS2.COLUMN_POSITION
                  ,1,''
                  ,  '              ')||
           IND.TABLE_OWNER||'.'||IND.TABLE_NAME||' ('||&CR||
           DECODE(COLS2.COLUMN_POSITION
                  ,1,''
                  ,  '              '||
                     SUBSTR(RPAD(' ',30),1,LENGTH(IND.TABLE_OWNER))||' ')
        ,  '              '||
           SUBSTR(RPAD(' ',30),1,LENGTH(IND.TABLE_OWNER))||','
             )||
       COLS1.COLUMN_NAME||
       DECODE(COLS1.COLUMN_POSITION
              ,COLS2.COLUMN_POSITION,
                ' )'||&CR||
                'INITRANS '||IND.INI_TRANS||' MAXTRANS '||IND.MAX_TRANS||&CR||
                'TABLESPACE '||IND.TABLESPACE_NAME||&CR||
                'STORAGE (INITIAL '||
                      DECODE(MOD(INITIAL_EXTENT,(1024*1024))
                             ,0,TO_CHAR(INITIAL_EXTENT/(1024*1024))||'M'
                             ,  DECODE(MOD(INITIAL_EXTENT,1024)
                                       ,0,TO_CHAR(INITIAL_EXTENT/1024)||'K'
                                       ,  TO_CHAR(INITIAL_EXTENT)
                                      )
                            )||
                         ' NEXT '||
                      DECODE(MOD(NEXT_EXTENT,(1024*1024))
                             ,0,TO_CHAR(NEXT_EXTENT/(1024*1024))||'M'
                             ,  DECODE(MOD(NEXT_EXTENT,1024)
                                       ,0,TO_CHAR(NEXT_EXTENT/1024)||'K'
                                       ,  TO_CHAR(NEXT_EXTENT)
                                      )
                            )||
                ' MINEXTENTS '||MIN_EXTENTS||' MAXEXTENTS '||MAX_EXTENTS||
                ' PCTINCREASE '||PCT_INCREASE||');'||&CR||&CR
              , &CR)

FROM DBA_IND_COLUMNS COLS2,DBA_IND_COLUMNS COLS1,DBA_INDEXES IND WHERE IND.TABLE_OWNER = NVL(UPPER(SUBSTR('&1',1,INSTR('&1','.')-1)),

                             IND.TABLE_OWNER)

AND IND.TABLE_NAME LIKE UPPER(SUBSTR('&1',INSTR('&1','.')+1))

AND    COLS1.TABLE_OWNER = IND.TABLE_OWNER
AND    COLS2.TABLE_OWNER = IND.TABLE_OWNER
AND    COLS1.INDEX_OWNER = IND.OWNER
AND    COLS2.INDEX_OWNER = IND.OWNER
AND    COLS1.TABLE_NAME =  IND.TABLE_NAME
AND    COLS2.TABLE_NAME =  IND.TABLE_NAME
AND    COLS1.INDEX_NAME  = IND.INDEX_NAME
AND    COLS2.INDEX_NAME  = IND.INDEX_NAME
AND    COLS2.COLUMN_POSITION =
       (SELECT MAX(COLS3.COLUMN_POSITION)
        FROM   DBA_IND_COLUMNS COLS3
        WHERE  COLS3.TABLE_OWNER = IND.TABLE_OWNER
        AND    COLS3.INDEX_OWNER = IND.OWNER
        AND    COLS3.TABLE_NAME =  IND.TABLE_NAME
        AND    COLS3.INDEX_NAME  = IND.INDEX_NAME)
ORDER BY IND.TABLE_OWNER
,        IND.OWNER
,        IND.TABLE_NAME
,        IND.INDEX_NAME
,        COLS1.COLUMN_POSITION;

SPOOL OFF
SET RECSEP WRAP
SET EMBEDDED OFF
CLEAR COLUMNS
SET NEWPAGE 1
SET PAGESIZE 9999
SET HEADING ON
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON REM RECREATE_INDEXES2.SQL
REM
REM Created 27-DEC-93 by R. Bakker
REM
REM Variation of CREATE_INDEXES.SQL; takes index name(s) as parameter REM instead of owner.table. Wildcards are still allowed. REM
REM Restrictions: Can't deduce a table name from its synonym. REM CREATE_INDEXES.SQL is empty if the table name given is unknown. REM ASC/DESC, PCTFREE and NOSORT not taken into account because I REM don't know how! CLUSTER left out on purpose. REM
REM Leave out as many blanks as possible if there's only one index column, REM as it has to carry the opening and closing clause, and can thus run REM into the 255-char limit on VMS.
REM
SET ECHO OFF
SET TERMOUT OFF
SET VERIFY OFF
SET HEADING OFF
SET FEEDBACK OFF
SET EMBEDDED ON
SET PAGESIZE 0
SET LINESIZE 80
SET RECSEP OFF
REM
REM May have to change this to CHAR(10)||CHAR(13) for some configurations REM
DEFINE CR = "CHR(10)"
SPOOL CREATE_INDEXES.SQL
SELECT DECODE(COLS1.COLUMN_POSITION

        ,1,'CREATE '||DECODE(IND.UNIQUENESS,'UNIQUE','UNIQUE ','')||
           'INDEX '||IND.OWNER||'.'||IND.INDEX_NAME||' ON '||&CR||
           DECODE(COLS2.COLUMN_POSITION
                  ,1,''
                  ,  '              ')||
           IND.TABLE_OWNER||'.'||IND.TABLE_NAME||' ('||&CR||
           DECODE(COLS2.COLUMN_POSITION
                  ,1,''
                  ,  '              '||
                     SUBSTR(RPAD(' ',30),1,LENGTH(IND.TABLE_OWNER))||' ')
        ,  '              '||
           SUBSTR(RPAD(' ',30),1,LENGTH(IND.TABLE_OWNER))||','
             )||
       COLS1.COLUMN_NAME||
       DECODE(COLS1.COLUMN_POSITION
              ,COLS2.COLUMN_POSITION,
                ' )'||&CR||
                'INITRANS '||IND.INI_TRANS||' MAXTRANS '||IND.MAX_TRANS||&CR||
                'TABLESPACE '||IND.TABLESPACE_NAME||&CR||
                'STORAGE (INITIAL '||
                      DECODE(MOD(INITIAL_EXTENT,(1024*1024))
                             ,0,TO_CHAR(INITIAL_EXTENT/(1024*1024))||'M'
                             ,  DECODE(MOD(INITIAL_EXTENT,1024)
                                       ,0,TO_CHAR(INITIAL_EXTENT/1024)||'K'
                                       ,  TO_CHAR(INITIAL_EXTENT)
                                      )
                            )||
                         ' NEXT '||
                      DECODE(MOD(NEXT_EXTENT,(1024*1024))
                             ,0,TO_CHAR(NEXT_EXTENT/(1024*1024))||'M'
                             ,  DECODE(MOD(NEXT_EXTENT,1024)
                                       ,0,TO_CHAR(NEXT_EXTENT/1024)||'K'
                                       ,  TO_CHAR(NEXT_EXTENT)
                                      )
                            )||
                ' MINEXTENTS '||MIN_EXTENTS||' MAXEXTENTS '||MAX_EXTENTS||
                ' PCTINCREASE '||PCT_INCREASE||');'||&CR||&CR
              , &CR)

FROM DBA_IND_COLUMNS COLS2,DBA_IND_COLUMNS COLS1,DBA_INDEXES IND WHERE IND.INDEX_NAME LIKE UPPER('&1')

AND    COLS1.TABLE_OWNER = IND.TABLE_OWNER
AND    COLS2.TABLE_OWNER = IND.TABLE_OWNER
AND    COLS1.INDEX_OWNER = IND.OWNER
AND    COLS2.INDEX_OWNER = IND.OWNER
AND    COLS1.TABLE_NAME =  IND.TABLE_NAME
AND    COLS2.TABLE_NAME =  IND.TABLE_NAME
AND    COLS1.INDEX_NAME  = IND.INDEX_NAME
AND    COLS2.INDEX_NAME  = IND.INDEX_NAME
AND    COLS2.COLUMN_POSITION =
       (SELECT MAX(COLS3.COLUMN_POSITION)
        FROM   DBA_IND_COLUMNS COLS3
        WHERE  COLS3.TABLE_OWNER = IND.TABLE_OWNER
        AND    COLS3.INDEX_OWNER = IND.OWNER
        AND    COLS3.TABLE_NAME =  IND.TABLE_NAME
        AND    COLS3.INDEX_NAME  = IND.INDEX_NAME)
ORDER BY IND.TABLE_OWNER
,        IND.OWNER
,        IND.TABLE_NAME
,        IND.INDEX_NAME
,        COLS1.COLUMN_POSITION;

SPOOL OFF
SET RECSEP WRAP
SET EMBEDDED OFF
CLEAR COLUMNS
SET NEWPAGE 1
SET PAGESIZE 9999
SET HEADING ON
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON REM RECREATE_GRANTS.SQL
REM
REM Created 27-DEC-93 by R. Bakker
REM Script to recreate the grants on a table or tables, provided as the REM single parameter. Wildcards and/or table owner are allowed. REM
REM With each grant, the user who gave the grant is shown in a comment REM because in Version 6 it's not possible for a DBA to give a grant as REM another user.
REM
REM This script can't handle second-hand grants (ie. grants given by a REM non-owner who was granted a specific privilege with grant option) and REM grants given for columns instead of tables, so give a warning if these REM are detected.
REM
SET ECHO OFF
SET TERMOUT OFF
SET VERIFY OFF
SET HEADING OFF
SET FEEDBACK OFF
SET EMBEDDED ON
SET PAGESIZE 0
SET LINESIZE 80
SET RECSEP OFF
REM
REM May have to change this to CHAR(10)||CHAR(13) for some configurations REM
DEFINE CR = "CHR(10)"
SPOOL CREATE_GRANTS.SQL
REM
SELECT MIN('WARNING: Second-hand or column-specific grants detected.'||&CR||

       'RECREATED_GRANTS.SQL can''t handle these. Ignore following results.')

FROM   SYS.DBA_TAB_GRANTS TAB
WHERE  TAB.OWNER = NVL(UPPER(SUBSTR('&1',1,INSTR('&1','.')-1)),TAB.OWNER)
AND    TAB.TABLE_NAME LIKE UPPER(SUBSTR('&1',INSTR('&1','.')+1))
AND    TAB.GRANTOR != TAB.GRANTEE
AND    ('G' IN (SELECT_PRIV,INSERT_PRIV,UPDATE_PRIV,DELETE_PRIV,
                REFERENCES_PRIV,INDEX_PRIV,ALTER_PRIV)
OR      'S' IN (SELECT_PRIV,INSERT_PRIV,UPDATE_PRIV,DELETE_PRIV,
                REFERENCES_PRIV,INDEX_PRIV,ALTER_PRIV));

REM
SELECT 'REM Granted by '||GRANTOR||&CR||

       'GRANT '||DECODE(SELECT_PRIV,'N','','SELECT')||
                 DECODE(INSERT_PRIV,'N','',
                 DECODE(SELECT_PRIV,'N','',',')||'INSERT')||
                 DECODE(UPDATE_PRIV,'N','',
                 DECODE(INSERT_PRIV,'N',
                 DECODE(SELECT_PRIV,'N','',','),',')||'UPDATE')||
                 DECODE(DELETE_PRIV,'N','',
                 DECODE(UPDATE_PRIV,'N',
                 DECODE(INSERT_PRIV,'N',
                 DECODE(SELECT_PRIV,'N','',','),','),',')||'DELETE')||
                 DECODE(REFERENCES_PRIV,'N','',
                 DECODE(DELETE_PRIV,'N',
                 DECODE(UPDATE_PRIV,'N',
                 DECODE(INSERT_PRIV,'N',
                 DECODE(SELECT_PRIV,'N','',','),','),','),',')||
                                        'REFERENCES')||
                 DECODE(ALTER_PRIV,'N','',
                 DECODE(REFERENCES_PRIV,'N',
                 DECODE(DELETE_PRIV,'N',
                 DECODE(UPDATE_PRIV,'N',
                 DECODE(INSERT_PRIV,'N',
                 DECODE(SELECT_PRIV,'N','',','),','),','),','),',')||
                                        'ALTER')||
                 DECODE(INDEX_PRIV,'N','',
                 DECODE(ALTER_PRIV,'N',
                 DECODE(REFERENCES_PRIV,'N',
                 DECODE(DELETE_PRIV,'N',
                 DECODE(UPDATE_PRIV,'N',
                 DECODE(INSERT_PRIV,'N',
                 DECODE(SELECT_PRIV,'N','',','),','),','),','),','),',')||
                                        'INDEX')||
                 ' ON '||TABLE_NAME||' TO '||GRANTEE||';'

FROM SYS.DBA_TAB_GRANTS TAB
WHERE TAB.OWNER = NVL(UPPER(SUBSTR('&1',1,INSTR('&1','.')-1)),

                             TAB.OWNER)

AND TAB.TABLE_NAME LIKE UPPER(SUBSTR('&1',INSTR('&1','.')+1)) AND TAB.GRANTOR != TAB.GRANTEE
ORDER BY GRANTOR,OWNER,TABLE_NAME,GRANTEE; SPOOL OFF
SET RECSEP WRAP
SET EMBEDDED OFF
CLEAR COLUMNS
SET NEWPAGE 1
SET PAGESIZE 9999
SET HEADING ON
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON Rick Bakker, roving DBA

--
Paul Bakker  bakker_at_cs.uq.oz.au | "PhD theses usually marshall an
Computer Science Dept.          | army of facts to starve a
The University of Qld           | slender and tedious truth
QLD 4072 Australia              | into submission" - The Guardian              

Received on Mon Feb 21 1994 - 02:04:10 CET