learn-oracle

Oracle DBA






Oracle DBA Scripts


Script to view and count number of sessions to the database

Generating script for Sessions from current database. Script generated can be use to view and count number of sessions to the database

 
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;

 
Script to get list of all jobs

Generating script for Jobs from current database. Script generated can be use to create list of jobs in the database with last and next date.

 
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;

 
Generating script for Tablespace from current database

Generating script for Tablespace from current database. Script generated can be use to create tablespace in event of system failure or creating replica of the same database.

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; 

More Tutorials on Oracle dba ...


Liked it ? Want to share it ? Social Bookmarking

Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information


Source : Website | Oracle DBA

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info





   Oracle Jobs   

   Forum
Oracle Forum   
   Ask A Question