Skip to content

Sample SQL scripts for the new oracle dba- can be executed from sqlplus





So you are a new oracle dba and you would like different examples of sqlplus scripts so it would be easy to write them if needed.

I hope you already know how to execute sqlscripts from sqlplus and I hope you already know what is the sqlplus tool and how to use it?

If not then  please see this link on using sqlplus .

Below are the sample sqlplus scripts

SCRIPT1

REM

REM

REM  the REM command is to used to write comments like i am writing now

REM  this script runs well in a unix environment. i dont know if this needs any changes in windows environment,

REM  as i havent tested this in a windows environment.

REM 

REM 

REM

REM 

REM

REM 

REM 

REM

REM

PROMPt prompt is used to display messages on the screen or to a log file when you run this script

CONNECT username/password

set feedback off

set verify off

set echo on

–spool is used to create a log file in the directory from where you are running this script from

spool createthislogfile.log

–as you can below i am calling and executing another script here

@callanothersqlscript.sql

spool off

PROMPT once again sending messages to the screen

CONNECT username/password

spool createanotherlogfile.log

@callanothersqlscript.sql

spool off

exit

SCRIPT2

 

 

Rem

rem these are comments where you can explain what this script is about.

rem below SET commands are sqlplus commands.look in the sqlplus reference guide

rem  for more information about the individual commands

SET ECHO ON

SET FEEDBACK 1

SET NUMWIDTH 10

SET LINESIZE 80

SET TRIMSPOOL ON

SET TAB OFF

SET PAGESIZE 100

–i am running below some anonymous pl/sql blocks to execute the dbms_scheduler package.

begin

  dbms_scheduler.drop_job(‘SYS.DUMMYJOB’);

end;

   /

 –remember that in sqlplus — is the comment sign. This is how i am able to write this text here and this doesnt interfere when this script is run.

–i am creating a procedure . this is an example on how you can put a pl/sql procedure or trigger or view etc inside a sql script and then execute it.

–please note that this will create the procedure in the database but it is not executing the procedure.

–i am writing comments to help you understand each line.

–syntax for create procedure command

create or replace procedure dummyjob10 as

–declaring some variables

output_file utl_file.file_type;

–declaring the cursor

cursor c1 is select unique(audsid) from V$SESSION;

sessid number;

aud_dest varchar2(1000);

inst_name varchar2(1000);

ver varchar2(100);

ExecImmediate_STR varchar(2000);

–beginning of the procedure block

begin

select value into inst_name from v$parameter where name = ‘instance_name’;

–assigning a long sql string to a variable.this is because you cant execute ddl statements inside a pl/sql procedure directly.

–you have to use execute immediate.

ExecImmediate_STR := ‘create or replace directory dummy_directory as ”/usr/local/chk/tmp”’;

–now i am using execute immediate to execute the sql statement.

execute immediate (ExecImmediate_STR);

–below i am using the utl_file package to open a file from pl/sql

–to know more about utl-file go to the pl/sql packages reference book on oracle website.

–the oracle links have been provided in one webpage on this site.

output_file := utl_file.fopen (‘OS_AUD_CLEANUP’,’session_’||inst_name||’_list.txt’, ‘W’);

–you have declared a cursor before the begin block. now you are opening the cursor.

–read the pl/sql reference guide for detailed explanation of the cursor definiton and its usage and other examples.

open c1;

loop

fetch c1 into sessid;

exit when c1%notfound;

utl_file.put_line (output_file, sessid);

end loop;

–i am closing the file that i opened earlier.

utl_file.fclose(output_file);

–selecting some values into pl/sql variable aud_dest. we have declared this variable before the begin section.

select value into aud_dest from v$parameter where name = ‘audit_file_dest’;

select version into ver from v$instance;

if ver like ‘10%’ or ver like ‘11%’

then

execute immediate

‘BEGIN dbms_scheduler.run_job(”dummyjob1”,TRUE); END;’;

else if ver like ‘9%’

then

output_file := utl_file.fopen (‘OS_AUD_CLEANUP’,’audit_dest.txt’, ‘W’);

utl_file.put_line (output_file, aud_dest);

utl_file.fclose(output_file);

end if;

end if;

end;

/

–we finished the procedure creation above. remember that after you created the procedure code above you should keep the forward slash / at the end .only then will oracle  create the

–procedure in the database when you execute this script. The forward slash / is sqlplus syntax telling sqlplus to execute the code and create the procedure in the database.

–below is an example of a pl/sql anonymous block within a sql script.

–so you can see that in one single script we were initially using pl/sql anonymous blocks and then we used a pl/sql procedure creation code and now we are again using a pl/sql

–anonymous block to execute more statements.

declare

ver varchar2(100);

argv3 varchar2(1000);

argv2 varchar2(1000);

aud_dest varchar2(1000);

inst_name varchar2(1000);

Begin

select version into ver from v$instance;

select value into aud_dest from v$parameter where name = ‘audit_file_dest’;

select value into inst_name from v$parameter where name = ‘instance_name’;

argv2 := ‘/usr/local/chk/tmp’ || ‘/’ || ‘session_’||inst_name||’_list.txt’;

argv3 := ‘/usr/local/chk/scripts’ || ‘/os_aud_cleanup.pl’;

if ver like ‘10%’ or ver like ‘11%’

then

execute immediate

‘BEGIN DBMS_SCHEDULER.CREATE_JOB (JOB_NAME => ”dummy1”,

JOB_TYPE => ”executable”,

JOB_ACTION => ”’ || argv3 || ”’,

NUMBER_OF_ARGUMENTS => 3,

ENABLED => FALSE); END;’;

execute immediate

‘BEGIN DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (

job_name => ”dummyjob1”,

argument_position => 1,

argument_value => ”’ || aud_dest || ”’); END;’;

execute immediate

‘BEGIN DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (

job_name => ”dummyjob2”,

argument_position => 2,

argument_value =>”’ || argv2 || ”’); END;’;

execute immediate

‘BEGIN DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (

job_name => ”dummyjob2”,

argument_position => 3,

argument_value => ”2”); END;’;

execute immediate

‘BEGIN DBMS_SCHEDULER.CREATE_JOB (JOB_NAME => ”dummyjob3”,

JOB_TYPE => ”STORED_PROCEDURE”,

JOB_ACTION => ”sys.dummyjob10”,

REPEAT_INTERVAL => ”FREQ=DAILY;INTERVAL=1”,

ENABLED => TRUE,

COMMENTS => ”Cleaup Job Run Daily”); END;’;

end if;

End;

/

exec DBMS_SCHEDULER.SET_ATTRIBUTE(name => ‘dummyjob1’ ,attribute => ‘start_date’ ,value => TRUNC(SYSDATE)+17/24);

script example 3

set pages 9999
set lines 200
set echo off
set feedback off

set heading off

set serveroutput on size 100000

whenever sqlerror exit failure

BEGIN

for f in

(

SELECT F.TABLESPACE_NAME,

TO_CHAR ((T.TOTAL_SPACE – F.FREE_SPACE),’999,999′) Used_MB,

TO_CHAR (F.FREE_SPACE, ‘999,999’) Cur_Free_MB,

TO_CHAR (T.TOTAL_SPACE, ‘999,999’) Cur_Tot_MB,

TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),’999′) || ‘ %’ Cur_Pct_Free,

TO_CHAR (T.max_SPACE, ‘999,999’) Auto_Max_MB,

TO_CHAR (T.max_SPACE – T.TOTAL_SPACE, ‘99,999’) Auto_Free_MB,

TO_CHAR ((100-((T.TOTAL_SPACE – F.FREE_SPACE)/T.max_SPACE)*100),’999.99′) || ‘ %’ Auto_Pct_Free,

TO_CHAR ((((T.TOTAL_SPACE – F.FREE_SPACE)/T.max_SPACE)*100),’999.99′) || ‘% ‘ Auto_Pct_Used

FROM (

SELECT TABLESPACE_NAME,

ROUND (SUM (BLOCKS*(SELECT VALUE/1024 FROM V$PARAMETER

WHERE NAME = ‘db_block_size’)/1024)

) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME

) F,

( SELECT TABLESPACE_NAME,

ROUND (SUM (BYTES/1048576)) TOTAL_SPACE,

sum(

decode

(sign(maxbytes/(1024*1024) – BYTES/(1024*1024)), 1, maxbytes/(1024*1024),

0, BYTES/(1024*1024),

-1, BYTES/(1024*1024)

)

) max_space

FROM DBA_DATA_FILES

where autoextensible = ‘YES’

GROUP BY TABLESPACE_NAME

) T

WHERE f.TABLESPACE_NAME = T.TABLESPACE_NAME

and (((T.TOTAL_SPACE – F.FREE_SPACE)/T.max_SPACE)*100) > 80

)

LOOP

DBMS_OUTPUT.put_line (‘Tablespace ‘||f.tablespace_name || ‘ has used’ || f.used_mb || ‘ MB, ‘ || ‘has ‘ || f.cur_free_mb || ‘ MB free, can autoextend’ || f.auto_free_MB || ‘ MB, and reached’ || f.auto_pct_used || ‘of maxsize’);

END LOOP;

for x in (select c.tablespace_name Name,

sum(c.dtot) Total,

sum(c.dtot)-sum(c.ftot) Used,

sum(c.ftot) Free,

(sum(c.dtot)-sum(c.ftot))/sum(c.dtot)*100 Pcent

from

(

select tablespace_name,sum(bytes) dtot,0 ftot

from dba_data_files

where autoextensible=’NO’

group by tablespace_name

union

select a.tablespace_name,0,sum(a.bytes)/count(distinct b.file_id) ftot

from dba_free_space a, dba_data_files b

where a.tablespace_name=b.tablespace_name

and b.autoextensible=’NO’

group by a.tablespace_name

union

select a.tablespace_name,sum(a.bytes)/count(distinct b.file_id) dtot,0

from dba_temp_files a, dba_data_files b

where a.tablespace_name=b.tablespace_name

and b.autoextensible=’NO’

group by a.tablespace_name

) c

group by c.tablespace_name)

LOOP

if x.Pcent > 80 then

DBMS_OUTPUT.put_line (‘Tablespace ‘||x.Name||’ (non-extensible) is ‘||round(x.Pcent)||’% full (Total: ‘||round(x.Total/1048576)||’ Mb – Used: ‘||round(x.Used/1048576)||’ Mb – Free: ‘||round(x.Free/1048576)||’ Mb)’);

end if;

END LOOP;

for x in

(select owner||’.’||segment_name||’ ‘||partition_name res, a.tablespace_name tb

from dba_segments a

where a.next_extent is not null

and a.next_extent >

(select max(b.bytes) from dba_free_space b

where b.tablespace_name= a.tablespace_name

group by b.tablespace_name))

LOOP

dbms_output.put_line(‘Segment ‘||x.res||’ on tablespace ‘||x.tb||’ has no more space to expand !’);

END LOOP;

END;

/

exit

script example 4

whenever sqlerror exit failure
set serveroutput on

set head off

set feed off

set echo off

set ver off

declare

par varchar2(3);

base varchar2(8);

machine varchar2(12);

timest varchar2(19);

files number;

begin

par := ‘&1’;

if par = ‘net’ then

select a.instance_name, a.host_name, to_char(a.startup_time,’DD-MM-YYYY-HH24:MI:SS’)

into base, machine, timest from v$instance a, v$database b

where upper(a.instance_name)=b.name;

dbms_output.put_line(‘Login via listener to ‘||base||’/’||machine||’ successful’);

else

select count(*) into files from dba_data_files;

select instance_name, host_name, to_char(startup_time,’DD-MM-YYYY-HH24:MI:SS’)

into base, machine, timest from v$instance;

dbms_output.put_line(‘Local login to ‘||base||’/’||machine||’ successful’);

end if;

end;

/

exit

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.