Home
 | 
Miscellaneous
 | 
Miscellaneous
Overview
Recent Article
All Archives
Topics
Comments 
Last modified: April 2013
You will find here some of the most useful SQL commands and some basic commands which can come very handy for DBAs most of the time.
Object(s) compilation
Using ALTER
SQL>
alter <PACKAGE> | <PROCEDURE> | <FUNCTION> | <TRIGGER> | <TABLE> | <VIEW> 
     [<username>].<object_name> compile;

alter procedure SCOTT.MYTABLE compile;
alter procedure SCOTT.MYPROCEDURE compile;
alter package SCOTT.MYPACKAGE compile;
alter package SCOTT.MYPACKAGE compile body;
Using DBMS_DDL
SQL>
exec dbms_ddl.alter_compile('PACKAGE','<USERNAME>','<PACKAGE NAME>');
exec dbms_ddl.alter_compile('PACKAGE BODY','<USERNAME>','<PACKAGE NAME>');
Using DBMS_UTILITY Package
SQL>
exec dbms_utility.compile_schema('<SCHEMA>',FALSE);
FALSE instructs Oracle to compile only invalid objects
 
MV / Job Scheduling
The following will submit a job to refresh the materialized view every 15 minutes daily...
SQL>
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X, 
      what      => 'dbms_refresh.refresh(''"<USERNAME>"."<MV_NAME>"'');',
      next_date => to_date('27/09/2012 09:15:00','dd/mm/yyyy hh24:mi:ss'),
      interval  => 'SYSDATE + 15/1440 ',
      no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/
commit;
Another example using atomic_refresh => FALSE:
SQL>
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what      => 'dbms_mview.refresh(''"<USERNAME>"."<MV_NAME>"'', atomic_refresh=>FALSE);'
     ,next_date => to_date('27/09/2012 07:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'SYSDATE + 15/1440  '
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/
commit;
This will schedule the job to run every 24 hrs
SQL>
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what      => 'dbms_refresh.refresh(''"<USERNAME>"."<MV_NAME>"'');'
     ,next_date => to_date('27/09/2012 10:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'sysdate + 24/24   '
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/
commit;
The followinb will run every day at 0800 hrs
SQL>
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what      => 'dbms_refresh.refresh(''"<USERNAME>"."<MV_NAME>"'');'
     ,next_date => to_date('27/09/2012 08:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'TRUNC(SYSDATE) + 1 + (8/24)  '
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/
commit;
This will run every one minute....
SQL>
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what      => '<MY_PACKAGE>.<MY_PROCEDURE_NAME>();'
     ,next_date => to_date('27/09/2012 07:34:21','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'sysdate + 1 / (24 * 60)'
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/
commit;
Date Values in Oracle
How to add a day/hour/minutes/seconds to a date value in Oracle?
SYSDATE
Meaning
SYSDATE + 1 / (24 * 60)
Now
SYSDATE + 1
Every one minute
SYSDATE + 7
Tomorrow/Next Day
SYSDATE + 1/24
Seven days from Now
SYSDATE + 3/24
One hour from Now
SYSDATE + 3/24
Three hours from now
SYSDATE + 24/24
Twenty four hours from Now
SYSDATE + 1/48
A half hour from Now
SYSDATE + 10/1440
10 Minutes from Now
SYSDATE + 30/86400
30 seconds from Now
TRUNC(SYSDATE + 1)
Tomorrow at 12 Midnight
TRUNC(SYSDATE + 1) + 8/24
TRUNC(SYSDATE) + 1 + (8/24)
Tomorrow at 8 AM
 
NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
Next Monday at 12:00 Noon
TRUNC(LAST_DAY(SYSDATE ) + 1)
First day of the month at 12 Midnight
TRUNC(LEAST(NEXT_DAY(sysdate, 'MONDAY'), NEXT_DAY(sysdate, 'WEDNESDAY'), NEXT_DAY(sysdate, 'FRIDAY'))) + 9/24
The next Monday, Wednesday
or Friday at 9 AM
 
 
Creating a simple Trigger
A simple CREATE TRIGGER example. This trigger will convert lowercase into upper BEFORE INSERT or UPDATE.
SQL>
CREATE OR REPLACE TRIGGER DB_NAME_CASE_CHANGE 
BEFORE INSERT OR UPDATE ON SQL_FOR_REVIEW
FOR EACH ROW
BEGIN
:NEW.DB_NAME := UPPER(:NEW.DB_NAME);
END;
/
Identifying Duplicate Records
This simple query can identify duplicate records found in your table.
SQL>
select
    ID, CITY
from
    CITY_MASTER A
where
    ROWID > (select MIN(ROWID) from CITY_MASTER B where B.CITY = A.CITY)
/
Another example:
SQL>
SELECT
    DB_NAME, SQL_ID
FROM
    SQL_FOR_REVIEW A
WHERE
    ROWID > (SELECT MIN(ROWID) FROM SQL_FOR_REVIEW B WHERE B.SQL_ID = A.SQL_ID)
/
Gather Table Stats
How to gather statis on a locked table.
SQL>
exec DBMS_STATS.UNLOCK_TABLE_STATS('<USERNAME>','<MYTABLE>');
exec DBMS_STATS.GATHER_TABLE_STATS('<USERNAME>','<MYTABLE>');
exec DBMS_STATS.LOCK_TABLE_STATS('<USERNAME>','<MYTABLE>');
Creating a Job
How to create a job and schedule it?
SQL>
BEGIN
    sys.dbms_scheduler.create_job(
    job_name => '"<USERNAME>"."<MY_PROCEDURE>"',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin
        PENDING_ENQUIRY_WRAPPER_PRC;
    end;',
    repeat_interval => 'FREQ=DAILY;BYHOUR=0;BYMINUTE=5;BYSECOND=0',
    start_date => to_timestamp_tz('2012-11-23 Europe/London', 'YYYY-MM-DD TZR'),
    job_class => '"DEFAULT_JOB_CLASS"',
    comments => 'Daily Job',
    auto_drop => FALSE,
    enabled => TRUE);
END;
/
If you wanted to change the REPEAT_INTERVAL - say from 00:05 to 05:00
SQL>
BEGIN
    sys.dbms_scheduler.disable( '"<USERNAME>"."<MY_PROCEDURE>"' );
    sys.dbms_scheduler.set_attribute( 
        name => '"<USERNAME>"."<MY_PROCEDURE>"',
        attribute => 'repeat_interval',
        value => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0');
    sys.dbms_scheduler.enable( '"<USERNAME>"."<MY_PROCEDURE>"' );
END;
/
Enabling / Disabling Job(s)
How to enable or disable multiple jobs using PL/SQL block.
SQL>
begin
     dbms_scheduler.enable (name=> '<SCHEMA>.<JOBNAME1>');
     dbms_scheduler.disable (name=> '<SCHEMA>.<JOBNAME2>');
     dbms_scheduler.enable (name=> '<SCHEMA>.<JOBNAME3>');
end;
/
Single job:
SQL>
exec dbms_scheduler.enable('<job_name>');
exec dbms_scheduler.disable('<job_name>');
An ad hoc job
How to run a job quickly using a shell script?
vi
-- ad_hoc_job.sh --
sqlplus scott/tiger <<EOF
exec mypackage.my_procedure_name;
exit
EOF
Then use 'nohup' to leave it running in the background...
$
nohup sh ad_hoc_job.sh &
Auto logon by OS user
If the OS user is 'shannura' to which we want to allow auto logon to database, we will create the user in the database as shown below:
SQL>
create user ops$shannura identified externally; 
User created.

grant create session to ops$shannura; 
Grant succeeded.

sqlplus /            

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Nov 5 12:38:32 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

grant dba to  OPS$SHANNURA;
Grant succeeded.
Please write your comment if this was useful.

Shannura

/