10g/11g Scripts

Syntax we use in database for administration


                                      SYNTAX WE USE as a DBA
1} CREATING AN SPFILE:
             
                 create SPFILE=’</Source path>’ from PFILE=’</Destination path>’;
2} ALTERING THE DATABASE:
  alter database<Database name> NOMOUNT;
  alter database<Database name> MOUNT;
  alter database<Database name> OPEN;

3} OPENING THE DATABASE IN READONLY MODE:
  alter database<Database name> open READONLY;
4} OPENING DATABASE IN RESTRICTED MODE:
  startup RESTRICT;
  alter system enable RESTRICTED session;
5} MULTIPLEXING CONTROL FILES USING ‘SPFILE’:
alter system set controlfile=’</Source path>’ ‘</Destination path>’                  scope=spfile;
               shut down database;
                copy controlfiles using O.S command.
                 ==>cp ‘</source path>’ ‘</destination path>’
                 startup database
6}MULTIPLEXING CONTROL FILES USING ‘PFILE’:
               Shut the database;
                copy controlfiles using O.S command.
                     ==>cp ‘</source path>’ ‘</destination path>’
                Edit the pfile with new control file locations.
                startup the database
7}FORCING A LOG SWITCH:
                 alter system switch logfile;
8} ADDING REDO LOG ‘GROUPS’:
                alter database ADD logfile GROUP 1
                 (‘</Path of logfile to be placed>’ ‘</ another logfile to be placed>’)
                 Size 1M;
9}ADDING ONLINE REDO LOG FILE ‘MEMBERS’:
                 alter database ADD logfile MEMBER
                 ‘</Path of logfile>’ to GROUP 1,
                     ‘</Path of logfile>’ to GROUP 2,
                 ‘</Path of logfile>’ to GROUP 3;
10}DROPPING REDO LOG ‘GROUPS’:
                alter database DROP logfile GROUP <group no.>;
11} DROPPING REDO LOG ‘MEMBERS’:
                 alter database DROP logfile MEMBER ‘</Path of log file>’;
12}RELOCATING (OR) RENAMING LOGFILES:
                 Copy redo log files to new location.
== > cp ‘</source path>’ ‘</Destination path>’
            alter database clear logfile ‘</Path and new name>’;
                Drop old members;
13}CREATING TABLESPACE:
                 create tablespace <tablespace name> DATAFILE’</Path>’ size <no.>
                     Autoextend on next<size.> maxsize <size>;
14} CREATING LOCALLY MANAGED TABLESPACES:
                 create tablespace <tablespace name>
                  DATAFILE ‘</Destination of datafile>’ size<no.>
                  Extent management local uniform size <no.>;
15}CREATING UNDO TABLESPACES:
                 create undotablespace <tablespace name>
                     DATAFILE ‘</Destination of datafile>’ size<no.>;
16}CREATING TEMPORARY TABLESPACES:
                 create temporary tablespace <tablespace name>
                     TEMPFILE ‘</Destination>’ size<no.>
                     Extent management local uniform size<no.>;
17} SETTING DEFAULT TEMPORARY TABLESPACES:
                alter database default temporary tablespace <tablespace name>;
18} SETTING TABLESPACE IN ‘READONLY’ MODE:
                 alter tablespace <tablespace name> READONLY;
19} TAKING TABLESPACE OFFLINE AND ONLINE:
                alter tablespace <tablespace name> OFFLINE;
                 alter tablespace <tablespace name> ONLINE;
  
20} MANUALLY RESIZING DATAFILE:
                 alter database DATAFILE ‘</path>’ RESIZE <NO.>;
21}ADDING DATAFILE TO TABLESPACE:
                 alter tablespace <tablespace name>
                     ADD DATAFILE ‘</path>’ size <no.>;
22} METHODS OF MOVING(RENAMING) DATAFILES:
                Take tablespace offline.
                 use O.S command to copy datafile to different location.
                 alter database ‘</path>’ RENAME
                 Datafile ‘</Source path>’ to ‘</Destination path>’;
                 Bring tablespace online;
23}IF TABLESPACE CANNOT BE TAKEN OFFLINE FOR MOVING:
                 Shut down database.
                 use O.S command to copy datafile to different location.
                 Mount database.
                alter database ‘</path>’ RENAME
                 Datafile ‘</Source path>’ to ‘</Destination path>’;
                 open database.
24}TABLESPACE DROPPING:
                drop tablespace <tablespace name> including contents and datafiles;
25} AUTOMATIC SEGMENT SPACE MANAGEMENT:
                create tablespace <tablespace name>
                     DATAFILE ‘</path>’ size<no.>
                     Extent management local uniform size <no.>
                     Segment space management AUTO;
26}SWITCHING  UNDO TABLESPACE:
                alter system set UNDO_TABLESPACE=<undo name>;
27} DROPPING UNDO TABLESPACE:
                 drop tablespace <undo tablespace name>;
 28} MANUALLY ALLOCATING EXTENTS:
                 alter table <tablespace name>
                     Allocate extent (size<no.> DATAFILE ‘</path>’);
29} MOVING ONE TABLE TO OTHER TABLESPACE:
                 alter table <table name>
                     Move tablespace<tablespace name>;
30} TRUNCATING TABLE:
                 truncate table <table name>;
31} DROPPING TABLE:
                 drop table <table name> cascade constraints;
32} DROPPING COLUMNS:
                 alter table<table name> drop column comments
                     Cascade constraints checkpoint <1000>;
33} DIFFERENT UNUSED OPTIONS:
                 MARK
                                == > alter table <table name> set UNUSED COLUMN comments
                                                Cascade constraints;
               
                DROP
== > alter table <table name>  DROP UNUSED COLUMN checkpoint 1000;
  CONTINUE
                == > alter table <table name>  DROP COLUMN CONTINUE
checkpoint 1000;
34}CREATING B-TREE INDEXES:
                 create Index <tablename_column_name_idx>
                     On <tablename>(column name)
                      Pctfree 30
                      Storage(initial <no.> next <no.>
                      Pct increase 0 maxextents <no.>)
                      Tablespace <tablespace name>;
 35} CREATE BITMAP INDEX:
                 create BITMAP index < tablename_column_name_idx >
                           On <tablename>(column name)
                      Pctfree 30
                      Storage(initial <no.> next <no.>
                      Pct increase 0 maxextents <no.>)
                      Tablespace <tablespace name>;
36} REBUILDING INDEX:
                 alter index <index name> REBUILD
                     Tablespace<tablesapce name>;
37} REBUILDING INDEXES ONLINE:
                alter index <index name> REBUILD ONLINE;
38}DROPPING INDEX
                drop index <index name>;

 

 

To calculate the size of Database


The following script calculates the storage allocated to an Oracle database, i.e., its overall size. You will need DBA privilege to run this script.
SQL> select ROUND( ( a.data_size + b.temp_size +
c.redo_size + d.cf_size +
e.bct_size)
/1024/1024/1024
) “total_GB_size”
from
( select SUM(bytes) data_size from v$datafile) a,
( select NVL(sum(bytes),0) temp_size from v$tempfile) b,
( select SUM(bytes) redo_size from v$log) c,
( select SUM(block_size*file_size_blks) cf_size from v$controlfile) d,
( select NVL(bytes,0) bct_size from v$block_change_tracking) e
select a.data_size+b.temp_size+c.redo_size “total_size”
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files )a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c

Procedure to check Database growth on daily basis

Procedure to check DB growth
1.Create a Table By the Name DB_GROWTH…with following details…
CREATE TABLE DB_GROWTH
( DAY DATE,
DATABASE_SIZE_GB NUMBER,
DAILY_GROWTH_GB NUMBER);
2.create or replace PROCEDURE database_growth
AS
today_size NUMBER;
yesterday_size NUMBER;
growth_size NUMBER;
cnt NUMBER;
BEGIN
SELECT sum(bytes)/(1024*1024) INTO today_size FROM SM$TS_USED;
SELECT COUNT(1) INTO cnt FROM db_growth ;
IF cnt > 0
THEN
SELECT database_size_mb INTO yesterday_size FROM db_growth WHERE to_date(d
ay,’dd-mon-yy’)=to_date(SYSDATE -1,’dd-mon-yy’);
ELSE
yesterday_size:=today_size;
END IF;
growth_size := today_size – yesterday_size;
INSERT INTO db_growth VALUES(sysdate,today_size,growth_size);
EXCEPTION
WHEN no_data_found THEN
INSERT INTO db_growth VALUES(sysdate,today_size,0);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
3.Submit in DBMS_JOBS
variable jobno number;
begin
dbms_job.submit(
:jobno,
‘database_growth ;’,
trunc(sysdate+1) + 4/24,
‘trunc(sysdate+1) + 4/24′
);
commit;
end;
/

Scripts Every DBA Should Have

While working on oracle I found some scripts which are mandatory for any DBA.I would like to share those scripts with you.
Scripts Every DBA Should Have
I. Display the Current Archivelog Status :
ARCHIVE LOG LIST;
II. Creating a Control File Trace File
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
III. Tablespace Free Extents and Free Space
column Tablespace_Name format A20
column Pct_Free format 999.99
select Tablespace_Name,
Max_Blocks,
Count_Blocks,
Sum_Free_Blocks,
100*Sum_Free_Blocks/Sum_Alloc_Blocks AS Pct_Free
from
(select Tablespace_Name, SUM(Blocks) Sum_Alloc_Blocks
from DBA_DATA_FILES
group by Tablespace_Name),
(select Tablespace_Name FS_TS_NAME,
MAX(Blocks) AS Max_Blocks,
COUNT(Blocks) AS Count_Blocks,
SUM(Blocks) AS Sum_Free_Blocks
from DBA_FREE_SPACE
group by Tablespace_Name)
where Tablespace_Name = FS_TS_NAME;
IV. Display Allocated Space & Quota by User
select * from DBA_TS_QUOTAS
order by Tablespace_Name, Username;
V. Show Allocated Storage for All Objects
column Segment_Name format A40
select Segment_Name, Extents, Blocks
from DBA_SEGMENTS
where Segment_Type = ‘&segment_type’
order by Segment_Name;
VI. Map a Tablespace’s Used and Free Space
select Segment_Name, File_Id, Block_Id, Blocks
from DBA_EXTENTS where Tablespace_Name = ‘&&tablespacename’
UNION
select ‘Free Space’, File_Id, Block_Id, Blocks
from DBA_FREE_SPACE where Tablespace_Name = ‘&&tablespacename’
order by 2,3;
VII. Blocks Used by Data in a Table
select COUNT(DISTINCT(SUBSTR(ROWID,1,8)||SUBSTR(ROWID,15,4)))
Blocks_Used from &table;
VIII. Reset a User’s Password
select ‘ALTER USER &&1 IDENTIFIED BY VALUES
‘||””||Password||””||’;’
from DBA_USERS where Username = UPPER(‘&&1′);
IX. Query V$PARAMETER
column Name format A50
column Value format A28
select Name, Value from V$PARAMETER;
X. Show Statement Execution Path
select LPAD(‘ ‘,2*LEVEL)||Operation||’ ‘||Options
||’ ‘||Object_Name Q_PLAN
from PLAN_TABLE where Statement_ID = ‘TEST’

Script: Database structure and file location

If we are handling any new database for first time then these scripts are useful.Provide the initial required details of database.
set pagesize 500
set linesize 130
Prompt
Prompt Control Files Location >>>>
col name format a60 heading “Control Files”
select name
from sys.v_$controlfile
/
Prompt
Prompt Redo Log File Locations >>>>
Prompt
col Grp format 9999
col member format a50 heading “Online REDO Logs”
col File# format 9999
col name format a50 heading “Online REDO Logs”
break on Grp
select group#,member
from sys.v_$logfile
/
Prompt Data Files Locations >>>>
col Tspace format a25
col status format a3 heading Sta
col Id format 9999
col Mbyte format 999999999
col name format a50 heading “Database Data Files”
col Reads format 99,999,999
col Writes format 99,999,999
break on report
compute sum label ‘Total(MB)’ of Mbyte on report
select F.file_id Id,
F.file_name name,
F.bytes/(1024*1024) Mbyte,
decode(F.status,’AVAILABLE’,'OK’,F.status) status,
F.tablespace_name Tspace
from sys.dba_data_files F
order by tablespace_name;
   

Script : Tablespace free space and fragmentation

Fragmentation is use to happen for tabl as well as for tablespace.Fragmentation is nothing but waisted space(After deletion of data).
set linesize 150
column tablespace_name format a20 heading ‘Tablespace’
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading ‘Total| Size(Mb)’
column Tot_Free format 999,999,999 heading ‘Total Free(MB)’
column Pct_Free format 999.99 heading ‘% Free’
column Chunks_Free format 9999 heading ‘No Of Ext.’
column Max_Free format 999,999,999 heading ‘Max Free(Kb)’
set echo off
PROMPT FREE SPACE AVAILABLE IN TABLESPACES
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1048576) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
from
(
select tablespace_name,0 tots,sum(bytes) sumb,
max(bytes) largest,count(*) chunks
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0,0,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
order by pct_free;

Table fragmentation & how to avoid same

When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.
HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don’t have data, it only reads blocks up to the high water mark when doing a full table scan.
DDL statement always resets the HWM.
Small example to find the table fragmentation.
SQL> select count(*) from big1;
1000000 rows selected.
SQL> delete from big1 where rownum <= 300000;
300000 rows deleted.
SQL> commit;
Commit complete.
SQL> update big1 set object_id = 0 where rownum <=350000;
342226 rows updated.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,'BIG1′);
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8),2) tablesize, round((num_rows*avg_row_l
en/1024),2) actualsize from dba_tables  where table_name=’BIG1′;
TABLE_NAME TABLE SIZE ACTUAL DATA
————————— ——————————————- ——————
BIG1              72952          30604.2
Note = 72952 – 30604 = 42348 Kb is wasted space in table

The difference between two values is 60% and Pctfree 10% (default) – so, the table has 50% extra space which is wasted because there is no data.
How to reset HWM / remove fragemenation?
For that we need to reorganize the fragmented table.
We have four options to reorganize fragmented tables:
1. alter table … move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition
Option: 1 “alter table … move + rebuild indexes”
SQL> alter table BIG1 move;
Table altered.
SQL> select status,index_name from user_indexes
2 where table_name = ‘BIG1′;
STATUS INDEX_NAME
——– ——————————
UNUSABLE BIGIDX
SQL> alter index bigidx rebuild;
Index altered.
SQL> select status,index_name from user_indexes
2 where table_name = ‘BIG1′;
STATUS INDEX_NAME
——– ——————————
VALID BIGIDX
SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,'BIG1′);
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8),2) tablesize, round((num_rows*avg_row_l
en/1024),2) actualsize from dba_tables  where table_name=’BIG1′;
TABLE_NAME TABLE SIZE ACTUAL DATA
————————— ——————————————- ——————
BIG1              38224           30727.37
Option: 2 “Create table as select”
SQL> create table big2 as select * from big1;
Table created.
SQL> drop table big1 purge;
Table dropped.
SQL> rename big2 to big1;
Table renamed.
SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,'BIG1′);
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8),2) tablesize, round((num_rows*avg_row_l
en/1024),2) actualsize from dba_tables  where table_name=’BIG1′;
TABLE_NAME TABLE SIZE ACTUAL DATA
————————— ——————————————- ——————
BIG1              85536            68986.97
SQL> select status from user_indexes
2 where table_name = ‘BIG1′;
no rows selected
SQL> –Note we need to create all indexes.
Option: 3 “export / truncate / import”
SQL> select table_name, round((blocks*8),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;
TABLE_NAME size
—————————— ——————————————
BIG1 85536kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;
TABLE_NAME size
—————————— ——————————————
BIG1 42535.54kb
SQL> select status from user_indexes where table_name = ‘BIG1′;
STATUS
——–
VALID
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Pr
oduction
With the Partitioning, OLAP and Data Mining options
C:>exp scott/tiger@Orcl file=c:big1.dmp tables=big1
Export: Release 10.1.0.5.0 – Production on…..
Export terminated successfully without warnings.
SQL> truncate table big1;
Table truncated.
imp scott/tiger@Orcl file=c:big1.dmp ignore=y
Import: Release 10.1.0.5.0 – Production on….
Import terminated successfully without warnings.
SQL> select table_name, round((blocks*8),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;
TABLE_NAME size
—————————— ——————————————
BIG1 85536kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;
TABLE_NAME size
—————————— ——————————————
BIG1 42535.54kb
SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,'BIG1′);
PL/SQL procedure successfully completed.
SQL> select table_name, round((blocks*8),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;
TABLE_NAME size
—————————— ——————————————
BIG1 51840kb
SQL> select table_name, round((num_rows*avg_row_len/1024),2)||’kb’ “size”
2 from user_tables
3 where table_name = ‘BIG1′;
TABLE_NAME size
—————————— ——————————————
BIG1 42542.27kb
SQL> select status from user_indexes where table_name = ‘BIG1′;
STATUS
——–
VALID
SQL> exec dbms_redefinition.can_redef_table(‘SCOTT’,'BIG1′,-
> dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.



Lists all invalid objects in the database.
Call Syntax  : @invalid_objects
Access to the DBA views.
---------------------------------------------------------
COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;
 
Displays a list of all the spfile parameters.
Access to the v$ views.
Call Syntax  : @spfile_parameters
-------------------------------------------------------
SET LINESIZE 500

COLUMN name  FORMAT A30
COLUMN value FORMAT A60
COLUMN displayvalue FORMAT A60

SELECT sp.sid,
       sp.name,
       sp.value,
       sp.display_value
FROM   v$spparameter sp
ORDER BY sp.name, sp.sid;
  
 
 
Displays space usage for each datafile.
Access to the DBA views.
Call Syntax  : @free_space
-- --------------------------------------------------------------------
SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF

SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
       Substr(df.file_name,1,40) "File Name",
       Round(df.bytes/1024/1024,2) "Size (M)",
       Round(e.used_bytes/1024/1024,2) "Used (M)",
       Round(f.free_bytes/1024/1024,2) "Free (M)",
  Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
FROM   DBA_DATA_FILES DF,
       (SELECT file_id,
               Sum(Decode(bytes,NULL,0,bytes)) used_bytes
        FROM dba_extents
        GROUP by file_id) E,
       (SELECT Max(bytes) free_bytes,
               file_id
        FROM dba_free_space
        GROUP BY file_id) f
WHERE  e.file_id (+) = df.file_id
AND    df.file_id  = f.file_id (+)
ORDER BY df.tablespace_name,
         df.file_name;

PROMPT
SET FEEDBACK ON
SET PAGESIZE 18
 
 

Size of any Object Index or Table

You can find the size of any object (table or index) with the following

SQL >SELECT sum(bytes)/1048576 MB, segment_name
FROM user_extents
WHERE segment_name = ‘&object_name’
GROUP BY segment_name
/

This gives the total megabytes allocated for the index/table.

 Displays information on the current
 wait states for all active database sessions.
 Access to the V$ views.
 Call Syntax  : @active_session_waits

-- ----------------------------------------------------------
SET LINESIZE 250
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN logon_time FORMAT A20

SELECT NVL(a.username, '(oracle)') AS username,
       a.osuser,
       a.sid,
       a.serial#,
       d.spid AS process_id,
       a.wait_class,
       a.seconds_in_wait,
       a.state,
       a.blocking_session,
       a.blocking_session_status,
       a.module,
       TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session a,
       v$process d
WHERE  a.paddr  = d.addr
AND    a.status = 'ACTIVE'
ORDER BY 1,2;

SET PAGESIZE 14
Displays the status of buffers in the SGA.
 Access to the v$ and DBA views.
Call Syntax  : @sga_buffers

-- ---------------------------------------------------------
SET LINESIZE 200
COLUMN object_name FORMAT A30

SELECT t.name AS tablespace_name,
       o.object_name,
       SUM(DECODE(bh.status, 'free', 1, 0)) AS free,
       SUM(DECODE(bh.status, 'xcur', 1, 0)) AS xcur,
       SUM(DECODE(bh.status, 'scur', 1, 0)) AS scur,
       SUM(DECODE(bh.status, 'cr', 1, 0)) AS cr,
       SUM(DECODE(bh.status, 'read', 1, 0)) AS read,
       SUM(DECODE(bh.status, 'mrec', 1, 0)) AS mrec,
       SUM(DECODE(bh.status, 'irec', 1, 0)) AS irec
FROM   v$bh bh
       JOIN dba_objects o ON o.object_id = bh.objd
       JOIN v$tablespace t ON t.ts# = bh.ts#
GROUP BY t.name, o.object_name;

                                                   RMAN automatic backup

You must a schell script and schedule it with linux crontab. 
Yes, you'll have to have a shell-script residing on the file system, but you can just as well use the dba_scheduler to schedule the backup (in 10g/11g). I've scheduled my backups like this:

-- create job for backup, point to actual external script
begin
dbms_scheduler.create_job(
        job_name        => 'BACKUP',
        job_type        => 'EXECUTABLE',
        job_action      => '/home/oracle/scripts/rman_backup.sh',
        start_date      => trunc(sysdate)+1+7/48,
        repeat_interval => 'trunc(sysdate)+1+7/48',
        enabled         => true,
        auto_drop       => false,
        comments        => 'execute backup script at 03:30');
end;
/ 


Script : Segments with highest I/O activity

This script will list the top 10 segments in the database that have the most number of
physical reads against them.
set pagesize 200
setlinesize 120
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||’.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in (‘physical reads’)
order by total_physical_reads desc)
where rownum <=10;


Tablespace creation script

set pagesize 100
set heading off
set feedback off
select ‘create tablespace ‘ || df.tablespace_name || chr(10)
|| ‘ datafile ”’ || df.file_name || ”’ size ‘ || df.bytes
|| decode(autoextensible,’N',null, chr(10) || ‘ autoextend on maxsize ‘
|| maxbytes)
|| chr(10)
|| ‘default storage ( initial ‘ || initial_extent
|| decode (next_extent, null, null, ‘ next ‘ || next_extent )
|| ‘ minextents ‘ || min_extents
|| ‘ maxextents ‘ || decode(max_extents,’2147483645′,’unlimited’,max_extents)
|| ‘) ;’
from dba_data_files df, dba_tablespaces t
where df.tablespace_name=t.tablespace_name
/

Script for Database Information

set feedback off
set termout on
set pagesize 56
set linesize 800
ttitle off
spool out.log
set heading off
set verify off
column today NEW_VALUE p_currdate noprint
select TO_CHAR(SYSDATE,’fmMonth ddth, yyyy’) today from dual;
clear breaks
clear computes
clear columns
set heading off
column name heading ” justify center format a74
select ‘Database Name (SID): ‘ || name “name” from v$database;
prompt
prompt
prompt Version/SGA Information:
set heading off
select * from v$version;
column sgatot justify left format 9,999,999,999
select ‘Total System Global Area as of &p_currdate.:’ hd1, sum(value) sgatot, ‘bytes’ hd2 from v$sga;

************* TABLESPACES/DATAFILES **************

prompt
prompt
prompt Tablespaces and Datafiles:
clear breaks
clear computes
clear columns
set heading on
column tablespace_name heading ‘Tablespace’ justify left format a15
column file_id heading ‘File|ID’ justify center format 9999
column file_name heading ‘Datafile’ justify center format a60 word_wrapped
column size Heading ‘Size|in MB.’ justify center format 9,99990.99
break on tablespace_name skip 1 on report skip 2
compute sum label ‘TS SIZE:’ of size on tablespace_name
compute sum label ‘DB SIZE:’ of size on report
select tablespace_name,file_id,file_name,bytes/1024/1024 “size” from dba_data_files order by tablespace_name, file_id, file_name;

************* SPACE USAGE **************

prompt Space usage (as of &p_currdate.):
prompt
clear breaks
clear computes
clear columns
set heading on
column tspace heading ‘Tablespace’ justify left format a20 truncated
column tot_ts_size heading ‘Size|in MB.’ justify left format 9,99999999990
column free_ts_size heading ‘Used|in MB.’ justify right format 9,9999999990
column tbusedpct heading ” justify left format a6
column tbfreepct heading ” justify left format a6
break on report
compute sum label ‘Totals:’ of tot_ts_size free_ts_size on report
col tspace form a25 Heading “Tablespace”
col tot_ts_size form 99999999999999 Heading “Size (Mb)”
col free_ts_size form 99999999999999 Heading “Free (Mb)”
col ts_pct form 999 Heading “% Free”
col ts_pct1 form 999 Heading “% Used”
SELECT df.tablespace_name tspace,df.bytes/(1024*1024) tot_ts_size,
sum(fs.bytes)/(1024*1024) free_ts_size,round(sum(fs.bytes)*100/df.bytes) ts_pct,
round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1
FROM dba_free_space fs,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df
WHERE fs.tablespace_name = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes;

************* REDO LOG FILES **************

prompt
prompt
prompt Online Redo Logfiles:
clear breaks
clear computes
clear columns
column member heading ‘Logfile’ justify center format a60 word_wrapped
column group heading ‘Group|Number’ justify center format 9999
column size heading ‘Size|in MB.’ justify center format 999990.99
select f.member “member”,f.group# “group”,l.bytes/1024/1024 “size” from v$logfile f, v$log l where f.group#=l.group# order by f.group#,f.member;

************* CONTROL FILES ************** –
prompt
prompt
prompt Control files:
clear breaks
clear computes
clear columns
column name heading ‘File Name’ format a60 word_wrapped
select name from v$controlfile;

************* ROLLBACK SEGMENTS ************** –
prompt
prompt
prompt Rollback Segments (sizes as of &p_currdate.):
clear breaks
clear computes
clear columns
set heading on
column tablespace_name heading ‘Tablespace’ justify left format a15 truncated
column segment_name heading ‘Seg|Name’ justify center format a7
column status heading ‘Status’ justify center format a8
column initial_extent heading ‘Initial|(in M)’ justify center format 99990.9
column next_extent heading ‘Next|(in M)’ justify center format 99990.9
column min_extents heading ‘Min|Ext’ justify center format 99990
column max_extents heading ‘Max|Ext’ justify center format 999999999990
column pct_increase heading ‘Pct|Inc’ justify center format 99990
column rbsize heading ‘Curr Size|(in M)’ justify left format 9,99990
break on tablespace_name skip 1 on report skip 2
select r.tablespace_name,r.segment_name,r.status,
r.initial_extent/1024/1024 “initial_extent”,
r.next_extent/1024/1024 “next_extent”,
r.min_extents,r.max_extents,
r.pct_increase,
sum(e.bytes)/1024/1024 “rbsize”
from dba_rollback_segs r, dba_extents e
where e.segment_name = r.segment_name
group by r.tablespace_name, r.segment_name, r.status,
r.initial_extent/1024, r.next_extent/1024,
r.min_extents, r.max_extents, r.pct_increase;

************* PARAMETERS **************

prompt
prompt Parameters (non-defaults):
clear breaks
clear computes
clear columns
column name heading ‘Name’ format a35 word_wrapped
column pvalue heading ‘Value’ format a50 word_wrapped
select name, rtrim(value) “pvalue” from v$parameter order by name;

************* USER DETAILS **************

prompt
prompt user details :
clear breaks
clear computes
clear columns
select username,profile,default_tablespace,temporary_tablespace from dba_users;
select * from nls_database_parameters;
select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
select owner,object_type,count(1) from dba_objects group by owner,object_type order by owner;
SELECT file_name,tablespace_name,autoextensible,maxbytes/1048576 FROM dba_data_files;

Script : Top SQL (Buffer Gets)

This script will list the top 5 SQL statements sorted by the most number of buffer gets or logical reads
set serverout on size 1000000
declare
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select buffer_gets,substr(sql_text,1,4000)
from v$sqlarea
order by buffer_gets desc;
begin
dbms_output.put_line(‘Reads’||’ ‘||’ Text’);
dbms_output.put_line (‘—–’||’ ‘||’—————————————————’);
dbms_output.put_line(‘ ‘);
open c1;
for i in 1 .. 5 loop
fetch c1 into top5, text1;
dbms_output.put_line(rpad(to_char(top5),9)|| ‘ ‘||substr(text1,1,66));
len1 :=length(text1);
x := 66;
while len1 > x-1 loop
dbms_output.put_line(‘” ‘||substr(text1,x,64));
x := x+64;
end loop;
end loop;
end;
/

Script : Top SQL (Physical Reads)

This script will list the top 5 SQL statements sorted by the most number of physical reads
set serverout on size 1000000
set feedback off
declare
top5 number;
text1 varchar2(4000);
x number;
len1 number;
Cursor c1 is
select disk_reads,substr(sql_text,1,4000)
from v$sqlarea
order by disk_reads desc;
begin
dbms_output.put_line(‘Reads’||’ ‘||’ Text’);
dbms_output.put_line (‘—–’||’ ‘||’—————————————————-’);
dbms_output.put_line(‘ ‘);
open c1;
for i in 1 .. 5 loop
fetch c1 into top5, text1;
dbms_output.put_line(rpad(to_char(top5),9)|| ‘ ‘||substr(text1,1,66));
len1 :=length(text1);
x := 66;
while len1 > x-1 loop
dbms_output.put_line(‘” ‘||substr(text1,x,64));
x := x+64;
end loop;
end loop;
end;
/
   

Script : Latch Contention (top 5 latches)


This script will display the top 5 latches with the most sleeps.
set linesize 120
col name format a30
select * from
(select name, gets,misses, sleeps
from v$latch
order by sleeps desc)
where rownum < 6;







No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...