Oracle DBA Scripts

 
REM: Script to view and count number of sessions to the database
REM:
REM:*****************************************
REM:
REM:
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

set pages 100
set lines 3000

col machine format a25
col username format a15
col program format a25
break on 1;

set heading off;
select
      'Sessions on database '|| d.name ||' having instance name '|| i.instance_name
from v$database d,v$instance i;
set heading on;

compute sum label 'Total Sessions' of sessions on 1
select
    1,username,machine,program,count(*) sessions
from v$session
group by username,machine,program
order by username,sessions desc;

 

 
REM: Script to get list of all jobs
REM:
REM:*****************************************
REM:
REM:
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM:*****************************************

select substr(job,1,4) "job",
substr(job,1,4) "job",
substr(schema_user,1,10) "user",
(schema_user,1,10) "user",
substr(to_char(last_date,'dd.mm.yyyy hh24:mi'),1,16) "last date",
(to_char(last_date,'dd.mm.yyyy hh24:mi'),1,16) "last date",
substr(to_char(next_date,'dd.mm.yyyy hh24:mi'),1,16) "next date",
(to_char(next_date,'dd.mm.yyyy hh24:mi'),1,16) "next date",
substr(broken,1,2) "b",
(broken,1,2) "b",
substr(failures,1,6) "failed",
(failures,1,6) "failed",
substr(what,1,32) "command"
(what,1,32) "command"
from dba_jobs;

 

REM:*****************************************
REM: Generating script for Tablespace from current database
REM: Script generated can be use to create tablespace in event of system failure
REM: Or creating replica of the same database
REM:
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

set trimspool on
set pages 0
spool gentbsp.sql
SELECT 'create tablespace'
||' '||tablespace_name
||' '||'logging'
||' '||'datafile'
||' '||file_name
||' '||'size'||' '||bytes
||' '||'EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO'
FROM Dba_Data_Files
spool off;