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 check this site for sqlplus reference.
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 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
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
