DBA DAILY SCRIPTS

++++++++++++++++++sample script for create database+++++++++++++++++++++++++
CREATE DATABASE DATABASE_NAME
   USER SYS IDENTIFIED BY xxxxxxxx
   USER SYSTEM IDENTIFIED BY xxxxxxxx
   LOGFILE GROUP 1 ('LOCATION_PATH/redo01.log') SIZE 100M,
           GROUP 2 ('LOCATION_PATH/redo02.log') SIZE 100M,
           GROUP 3 ('LOCATION_PATH/redo03.log') SIZE 100M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET US7ASCII
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE 'LOCATION_PATH/system01.dbf' SIZE 800M
   SYSAUX DATAFILE 'LOCATION_PATH/sysaux01.dbf' SIZE 800M
   DEFAULT TABLESPACE users
      DATAFILE 'LOCATION_PATH/users01.dbf'
      SIZE 500M
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE 'LOCATION_PATH/temp01.dbf' SIZE 500M
   UNDO TABLESPACE UNDOTBS1
      DATAFILE 'LOCATION_PATH/undotbs01.dbf' SIZE 500M;

     
      Run Scripts to Build Data Dictionary Views
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql
EXIT

*********************************************************************************************************************************************

Script Description
CATALOG.SQL Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.
CATPROC.SQL Runs all scripts required for or used with PL/SQL.
PUPBLD.SQL Required for SQL*Plus. Enables SQL*Plus to disable commands by user.
*********************************************************************************************************************************************
Query to check tablespaces which are used more than 80%:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
col name for a25 
select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
   round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
               (select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
               where tablespace_name like '%%' group by tablespace_name) a,
               (select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
               where tablespace_name like '%%' group by tablespace_name) b
   Where a.tbl=b.tblsp and round((1-(b.fsz/a.tsz))*100)>80;
 
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

TABLESPACE Management:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
col name for a25 
select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
   round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
               (select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
               where tablespace_name like '%Tablespace_Name%' group by tablespace_name) a,
               (select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
               where tablespace_name like '%Tablespace_Name%' group by tablespace_name) b
   Where a.tbl=b.tblsp;

col file_name for a60
select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name ='Tablespace_Name';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Query to extract ddl of tablespaces:

select 'Create tablespace '||tablespace_name||' datafile '||'''+diskgroup_name'''||' size '||sum(bytes)/1024/1024||'m;' from dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','UNDOTBS2','USERS') group by tablespace_name;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Queries to generate snapshots and generating awr and addm reports:

EXEC dbms_workload_repository.create_snapshot;
!date
@?/rdbms/admin/awrrpti
@?/rdbms/admin/addmrpti
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Query to check user roles and grants:

select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
  select
    null     grantee,
    username granted_role
  from
    dba_users
  where
    username like upper('username')
/* THE ROLES TO ROLES RELATIONS */
union
  select
    grantee,
    granted_role
  from
    dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
  select
    grantee,
    privilege
  from
    dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Queries to check blocking sessions:

set time on
!date|awk '{print $4}'
set echo off
column blocker format a11;
column blockee format a10;
column sid format 99999;
select
           (select username from v$session where sid=a.sid) blocker,
         a.sid,
       ' is blocking ' "IS BLOCKING",
         (select username from v$session where sid=b.sid) blockee,
             b.sid
    from v$lock a, v$lock b
   where a.block = 1
     and b.request > 0
     and a.id1 = b.id1
     and a.id2 = b.id2
/

select spid, sid, a.serial#, b.username from v$session a, v$process b where sid=#### and a.paddr=b.addr;

select 'alter system kill session '''||sid||','||serial#||''''||' immediate;' from gv$session where status='INACTIVE' and username='Schema_name';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

THE SIZE OF THE DATABASE:
====================

set linesize 150
column file_name format A38 Heading "file"
column TABLESPACE_NAME format A18 Heading "TABLESPACE"
select d.TABLESPACE_NAME,
FILE_NAME,
d.BYTES/1024/1024 dfile_size_M,
nvl(sum(e.BYTES/1024/1024),0) bytes_used_M,d.MAXBYTES/1024/1024 MAXSIZE_M,
round(nvl(sum(e.BYTES/1024/1024),0) / (d.BYTES/1024/1024), 4) * 100 percent_used,
(d.BYTES - nvl(sum(e.BYTES),0))/1024/1024 bytes_free_M
from DBA_EXTENTS e,
DBA_DATA_FILES d
where d.FILE_ID = e.FILE_ID (+)
group by FILE_NAME,d.TABLESPACE_NAME, d.FILE_ID, d.BYTES, STATUS,d.MAXBYTES
order by d.TABLESPACE_NAME,d.FILE_ID;

Also:
=====

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.poo / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.poo / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as poo
from dba_free_space) free
group by free.poo
/

============================================================
DATABASE FILES:
==================

column Location format A45 Heading "LOCATION"
select 'Archived Log Directory' "Filename",
value "Location"
from v$parameter
where name = 'log_archive_dest'
UNION
select 'Control Files' "Filename",
value "Location"
from v$parameter
where name = 'control_files'
UNION
select 'Datafile' "Filename",
name "Location"
from v$datafile
UNION
select 'LogFile Member' "Filename",
member "Location"
from v$logfile;

============================================================
The last startup time for the DATABASE:
=================================

select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"
from v$instance;

============================================================
PARAMETERS VALUES:
=====================
column NAME format A25 Heading "NAME"
column VALUE format A25 Heading "VALUE"
column ISDEFAULT format A15 Heading "ISDEFAULT"
column ISSES_MODIFIABLE format A15 Heading "ISSES_MODIFIABLE"
column ISMODIFIED format A15 Heading "ISMODIFIED"
select NAME,
VALUE,
ISDEFAULT,
ISSES_MODIFIABLE,
ISMODIFIED
from v$parameter
order by NAME;

=============================================================
JOBS details:
============
set linesize 150
column LOG_USER format A15 Heading "LOG_USER"
column SCHEMA format A15 Heading "SCHEMA"
column JOB# format A8 Heading "SCHEMA"
column INTERVAL format A15 Heading "INTERVAL"
column NEXT_EXECUTION format A15 Heading "NEXT_EXECUTION"
column WHAT format A65 Heading "WHAT"
select LOG_USER,
SCHEMA_USER schema,
JOB job#,
INTERVAL,
to_char(NEXT_DATE,'MM/DD/YYYY HH24:MI:SS') next_execution,
BROKEN,
substr(WHAT,1,100) what
from dba_jobs
order by LOG_USER;

=============================================================
DB LINKS details:
=================

set linesize 150
column OWNER format A20 Heading "OWNER"
column DB_LINK format A45 Heading "DB_LINK"
column USERNAME format A23 Heading "USERNAME"
column created format A32 Heading "created"
select OWNER,
DB_LINK,
USERNAME,
HOST,
to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created
from dba_db_links
order by OWNER,DB_LINK;

=============================================================
Sequences Details:
=================
set linesize 150
select SEQUENCE_OWNER,
SEQUENCE_NAME,
MIN_VALUE,
MAX_VALUE,
INCREMENT_BY,
CYCLE_FLAG,
ORDER_FLAG,
CACHE_SIZE,
LAST_NUMBER
from dba_sequences
where SEQUENCE_OWNER not in ('SYS','SYSTEM')
order by SEQUENCE_OWNER,SEQUENCE_NAME;

=============================================================
Synonyms Details:
=================

set linesize 150
select OWNER,
SYNONYM_NAME,
TABLE_OWNER,
TABLE_NAME,
DB_LINK
from dba_synonyms
where owner not in ('SYS','SYSTEM','PUBLIC','DBSNMP')
order by OWNER,SYNONYM_NAME;

=============================================================
Triggers Details:
=================

set linesize 150
column TABLE_OWNER format A20 Heading "TABLE_OWNER"
column TABLE_NAME format A30 Heading "TABLE_NAME"
column TRIGGER_NAME format A30 Heading "TRIGGER_NAME"
column TRIGGER_TYPE format A20 Heading "TRIGGER_TYPE"
column TRIGGERING_EVENT format A20 Heading "TRIGGERING_EVENT"
select TABLE_OWNER,
TABLE_NAME,
TRIGGER_NAME,
TRIGGER_TYPE,
TRIGGERING_EVENT,
STATUS
from dba_triggers
order by TABLE_NAME, TRIGGER_NAME;

=============================================================
VIEWs Details:
=============

set linesize 150
column OWNER format A20 Heading "OWNER"
column OBJECT_NAME format A40 Heading "OBJECT_NAME"
column created format A35 Heading "created"
column status format A10 Heading "status"
select OWNER,
OBJECT_NAME,
to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created,
status
from dba_objects
where OWNER not in ('SYS','SYSTEM')
and OBJECT_TYPE='VIEW'
order by OWNER,OBJECT_NAME;

=============================================================
Show non-default parameters:
===========================

set pages 999 lines 100
col name format a30
col value format a50
select name
, value
from v$parameter
where isdefault = 'FALSE'
and value is not null
order by name
/

=============================================================
Reset the a parameter to it's default:
===================================alter system reset scope=spfile sid='*';

The sid='*' bit is always necessary, even in non RAC database.


=============================================================
Show the ten largest objects in the database:
============================================
col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 11
/

=============================================================
What's in undo:
==============

select tablespace_name
, status
, count(*) as HOW_MANY
from dba_undo_extents
group by tablespace_name
, status
/

=============================================================
Is anything rolling back at the moment?:
=======================================
set lines 100 pages 999
col username format a15
col command format a20
select ses.username
, substr(ses.program, 1, 19) command
, tra.used_ublk
from v$session ses
, v$transaction tra
where ses.saddr = tra.ses_addr
/
=============================================================
This Estimate the time percent complete for long SQL statements (DML) on the database: (powerfull)
=========
This Query fits all DML Statement:
=================================

set linesize 150
col username format a20
col opname format a35
SELECT * FROM (select
username,opname,sid,serial#,context,sofar,totalwork
,round(sofar/totalwork*100,2) "% Complete"
from v$session_longops)
WHERE round(sofar/totalwork*100,2) != 100;

%copmlete here will shows you the progress of long statements on the system.

=============================================================
To estimate how much the user need to complete rollback operation:
==================================================================
When a user doing a rollback for a large transaction and you want to know how much time remaining for this rollback operation to complete:

SELECT t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr=t.addr
and s.SID =:sid;


:sid : Here is for session sid that doing the rollback.

During the operation the columns t.used_ublk and t.used_urec will decrease until they become 0 when the output be "no rows selected" this means that the rollback operation completed.