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
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
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
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
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
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
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
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
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 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 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)
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)
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:
Post a Comment