SQL Scripts

Careers Page

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