Careers Page
Database Answers Header Rhuddlan Castle, North Wales
SQL Scripts
Home Ask a Question Data Models FAQs Search Site Map  
Chateau in Loire Valley We have another Page of links to Scripts available from from other Web Sites,with some overlap, or you can Search for a particular Script ...
  
CATEGORY DESCRIPTION
Auto-Increment Shows use of Oracle Sequence for auto-increment.
Date & Time Check Date and Time in SQL in Oracle Report Format.
Data Analysis 1. Check Table Owners.
Data Analysis 2. Check Row Counts.
Data Analysis 3. Check Primary Keys.
Data Analysis Print definitions of the Tables in the Database.
Data Analysis Sample Output for Database Report.
Data Analysis Check Table Spaces.
Data Analysis Generate SQL to Analyze Record Count Statistics.
Data Analysis Generate SQL to Check for specific data values.
Data Analysis Where a specific Column is used in Tables.
Data Analysis Generate SQL to Delete data.
Performance Analyze Stats - Oracle Optimizer needs this.
Trigger Check Trigger Definition - Bypasses limits on LONG fields
Users Change User in SQLPLUS Session.
Users Check User in SQLPLUS.

To change User in SQLPLUS Session ...
  • rem connect login/password, for example ...
  • connect scott/tiger;
    Then, to check the results ...
  • show user;

    1b. To change Schema to a different User in SQLPLUS Session ...
  • rem ALTER SESSION SET CURRENT_SCHEMA="USER_ID", for example ...
  • ALTER SESSION SET CURRENT_SCHEMA="scott";
    Note that the Schema Name must be a vaid User.

    Back to the Top
    Check the Date and Time in Oracle Report Format...
    set linesize 132
    column todays_date format a20 heading 'Todays date'
    select TO_CHAR(SysDate,'DD/Mon/YYYY HH:MI P.M.') todays_date from dual;
    To check the User in SQLPLUS Session ...
  • show user;
    4a. Generate SQL to Estimate Record Counts ...
    set feedback off
    set heading off
    spool analyze_all_tables.sql
    rem
    SELECT 'ANALYZE TABLE '||' '||Table_name||' ESTIMATE STATISTICS;' 
    FROM all_tables
    WHERE owner='scott';
    spool off
    quit
    

    4b. Sample Output from the Generated SQL ...
    spool analyze_stats.op
    set echo on;
    set feedback off;
    set heading off;
    ANALYZE TABLE  scott.SUBCONTRACT_DAYS ESTIMATE STATISTICS;                     
    ANALYZE TABLE  scott.SUBCONTRACT_NOMINATIONS ESTIMATE STATISTICS;              
    ANALYZE TABLE  scott.SUBCONTRACT_OPTIONS ESTIMATE STATISTICS;                  
    ANALYZE TABLE  scott.SUBCONTRACT_RAMP_RATES ESTIMATE STATISTICS;               
    ANALYZE TABLE  scott.SUBCONTRACT_TARIFFS ESTIMATE STATISTICS;                  
    ANALYZE TABLE  scott.SUBCONTRACT_TERMS ESTIMATE STATISTICS;                    
    ANALYZE TABLE  scott.SUBTERMINALS ESTIMATE STATISTICS;                         
    ... ditto ...
    

    To Generate SQL to check values for a specified Field...
  • After the SQL is generated, use edit/tpu(vi) and Global replace LIKE "LIKE 'SW%' ;"
    EMA024::ORACLE $ cat gen_chk_mts_id_data.sql
    Rem Filename : gen_chk_mts_id_data.sql
    rem Function : Generate SQL to check for Test Data values for a specific fld
    rem          : wherever that field,(or something like it) appears.
    spool  chk_mts_id_data.sql
    set echo off;
    rem column min_date format a20 heading 'Min Date' 
    set linesize 132;
    select name FROM V$DATABASE;
    show user;
    select TO_CHAR(SysDate,'DD/Mon/YYYY HH:MI P.M.') from dual;
    rem
    SELECT DISTINCT 'SELECT DISTINCT ' || column_name || ' FROM ' || table_name || 
    ' WHERE mts_id LIKE ' 
    FROM   all_tab_columns 
    WHERE  column_name LIKE '%MTS_ID%'
    AND    table_name  NOT LIKE 'BARRY%'
    AND    table_name  NOT LIKE '%TMP'
    AND    table_name  NOT LIKE '%VIEW%'
    AND    table_name  NOT LIKE 'Y2K%' ;
    rem
    spool off;
    quit;
    

    Back to the Top
    To Generate SQL to delete values for a specified Field...
  • After the SQL is generated, use edit/tpu(vi) and Global replace LIKE "LIKE 'SW%' ;"
    
    DISK$APPL01:[ORACLE.BARRYS.SCRIPTS.MTRS]GEN_DEL_MTR_DATA.SQL;4
     
    Rem Filename : gen_del_mts_id_data.sql
    spool  del_mts_id_data.sql
    set echo off;
    rem column min_date format a20 heading 'Min Date' 
    set linesize 132;
    select name FROM V$DATABASE;
    show user;
    select TO_CHAR(SysDate,'DD/Mon/YYYY HH:MI P.M.') from dual;
    rem
    SELECT DISTINCT 'DELETE FROM ' || table_name || 
    ' WHERE ' || column_name || ' LIKE '  
    FROM   all_tab_columns 
    WHERE  column_name LIKE '%MTS_ID%'
    AND    table_name  NOT LIKE 'BARRY%'
    AND    table_name  NOT LIKE '%TMP'
    AND    table_name  NOT LIKE '%VIEW%'
    AND    table_name  NOT LIKE 'Y2K%' ;
    rem
    spool off;
    quit;
    

    Back to the Top
    To Find Tables Where a specific Column is used from System Catalogues...
    {xxx009}oracle# cat da_utilities/chk_where_used.sql
    rem Filename  : chk_where_used.sql
    rem Function  : Find Tables where a specified Column is used.
    rem Directory : y2k/scott_export_data/mitch/
    rem Author    : Barry Williams
    rem
    rem Step 1) Find the Table Owner ...*/
    spool chk_where_used.op
    set heading off;
    rem SELECT owner,count(*) FROM all_tables GROUP BY owner;
    rem
    SELECT   DISTINCT  table_name,column_name
    FROM     all_tab_columns
    WHERE    column_name LIKE '%AGREE%'   
    OR       column_name LIKE '%ANN%'   
    OR       column_name LIKE '%STOR%'   
    OR       table_name  LIKE '%AGREE%'   
    OR       table_name  LIKE '%ANN%'  
    OR       table_name  LIKE '%STOR%'   
    ORDER    BY table_name,column_name;
    rem
    spool off
    quit
    {xxx009}oracle# 
    

    Back to the Top
    10. Print Table Definitions, (i.e. Database Definition) ...
    HOSTNAME::ORACLE $ cat dd_step4b.sql
    rem Filename : dd_step4b.sql 
    rem Note     : This Version is for IFMS and prints only the
    rem          : 'Data' Tables, which begin with FMS_TD_
    rem
    SELECT name FROM V$DATABASE;
    SHOW USER; 
    spool dd_step4b.op 
    select TO_CHAR(SysDate,'DD/Mon/YYYY HH:MI P.M.') from dual; 
    rem
    rem set heading off;
    rem
    set termout  on
    set feedback OFF
    set verify off
    set pagesize 59
    set linesize 132
    set newpage  0
    set space    1
    set recsep off
    set arraysize 5
    rem
    rem column COL_COMMENT format a40  word_wrap
    column TABLE_NAME  format a30 HEADING   'TABLE NAME'
    column KEY_SEQ     format 99  heading  'KEY'
    column COLUMN_ID   format 999  heading  'ID'
    column COLUMN_NAME format a30  heading  'FIELD NAME'
    column TYPE_LENGTH format a15   heading  'DATA TYPE'
    rem column CODED_TYPE  format a8   heading  'LENGTH'
    rem column DATA_SCALE  format 9    heading  'DEC'                      
    column LENGTH      format 9999 heading  'LENGTH'
    column NULLABLE    format a5   heading  'NULL?'                 
    rem
    column TODAY       noprint   new_value   date_var
    column USER        noprint   new_value   user_var               
    rem
    break on TABLE_NAME SKIP 2  
    rem
    ttitle left 'Date: ' 30th. April 2000 -
    center 'IFMS - Tables and Columns  in the FMSPRD1  Database' -
      skip 2
    rem  right  'Page ' format 99  SQL.PNO -
    rem  skip 2 
    rem
    rem spool chk_table_defs.op 
    rem
    SELECT   table_name,key_seq
            ,column_id,column_name
            ,data_type || '(' || TO_CHAR(length) || ')'  type_length
            ,data_scale 
            ,nullable
    FROM     barrys_dd2x   
    WHERE    table_name LIKE 'FMS_TD_%' 
    ORDER BY table_name, key_seq,column_id;
    rem
    set termout on
    rem
    spool off;
    quit;
    HOSTNAME::ORACLE $ 
    


    Back to the Top
    [ Home Page | Ask me a Question | Contact Us | The Life of a DBA | FAQs | Search | Site Map ]

    © Database Answers Ltd. 2001