DBA SCRIPTS
for
DBA TIPS or A JOB IS A TERRIBLE THING TO WASTE!
maxexts.sql
REM maxextts.sql
REM Author: TUSC (The Ultimate Software Consultants)
REM This script lists segments that are within 15 extents of their maximum
Set TrimSpool On
Set NewPage 0
Set Pages 80
Set Line 132
Set FeedBack Off
Set Verify Off
Set Term Off
TTitle Off
BTitle Off
Column Segment_Name Format A30 Heading "Segment"
Column Segment_Type Format A30 Heading "Type"
Column TableSpace_Name Format A30 Heading "Tablespace"
Column Extents Format 999,990 Heading "# Of |Extents"
Column Max_Extents Format 999,990 Heading "Maximum|Extents"
Column Instance New_Value _Instance NoPrint
Column Today New_Value _Date NoPrint
Select Global_Name Instance, To_Char(SysDate, 'DD-MON-YYYY HH24:MI:SS') Today
From Global_Name;
TTitle On
TTitle Left 'Date Run: ' _Date Skip 1-
Center 'Segments that are within 15 extents of their maximum' Skip 1 -
Center 'Instance Name: ' _Instance Skip 2
Select Segment_Name,
Segment_Type,
TableSpace_Name,
Extents,
Max_Extents
From Sys.DBA_Segments
Where Max_Extents - 15 < Extents
Order By TableSpace_Name
/
Set FeedBack On
Set Verify On
Set Term On
rollbcks.sql
REM rollbcks.sql
REM Author: TUSC (The Ultimate Software Consultants)
REM This script will display rollback segment contention
Set TrimSpool On
Set NewPage 0
Set Pages 80
Set Line 132
Set FeedBack Off
Set Verify Off
Set Term Off
TTitle Off
BTitle Off
Column Class Format A18 Heading 'RollBack Header'
Column Count Format 999,999 Heading 'Number |of waits'
Column Con_Get Format 999,999,999,999 Heading 'Logical| Reads'
Column Pct Format 990.99 Heading 'Pct of |Contention'
Column Instance New_Value _Instance NoPrint
Column Today New_Value _Date NoPrint
Select Global_Name Instance, To_Char(SysDate, 'DD-MON-YYYY HH24:MI:SS') Today
From Global_Name;
TTitle On
TTitle Left 'Date Run: ' _Date Skip 1-
Center 'RollBack Contention Report' Skip 1 -
Center 'If Pct >1% you may need additional RollBacks' Skip 1 -
Center 'If # of Wraps and Extends > 10,' Skip 1 -
Center 'Recreate Rollbacks with Larger Initial/Next Extent' Skip 1 -
Center 'Instance Name: ' _Instance Skip 2
Select A.Class, Count, Sum(Value) Con_Get, ((Count / Sum(Value)) * 100) pct
From V$WaitStat A, V$SysStat B
Where A.Class In ('system undo header', 'system undo block',
'undo header', 'undo block')
And Name In ('db block gets', 'consistent gets')
Group by A.Class, Count
/
TTitle Off
Set NewPage 1
Prompt
Prompt
Prompt
Prompt
Column Name Format A30 Heading 'RollBack Segment'
Column Gets Format 9,999,999 Heading 'Number of|Activities'
Column Waits Format 9,999,999 Heading 'Number|of Waits'
Column Pct Format 990.99 Heading 'Pct of|Gets'
Select Name, Gets, Waits, ((Waits / gets) * 100) Pct
From V$RollStat A, V$RollName B
Where A.USN = B.USN
/
Select Shrinks, Wraps, Extends, Aveshrink, Aveactive, Hwmsize
From V$Rollstat;
/
locks.sql
rem *********************************************
rem locks.sql
rem Author: TUSC (The Ultimate Software Consultants)
rem This program is used to show both 1) users waiting for locked resources
rem and 2) the users who are locking those who are waiting
rem *********************************************
set linesize 80
set verify off
column username format a10
column lockwait format a10
column sql format a70 word_wrap
column object_owner format a15
column object format a20
break on sid skip 2
rem This script shows all users who are waiting for locked resources
SELECT
b.username,
b.serial#,
c.sid,
c.owner object_owner,
c.object,
a.sql_text SQL
FROM v$access c,v$sqltext a,v$session b
WHERE
a.address = b.sql_address
AND
a.hash_value = b.sql_hash_value
AND
b.sid = c.sid
AND
b.lockwait IS NOT NULL
AND
c.owner NOT IN ('SYS','SYSTEM');
rem This script shows the users who are locking the above waiting resources.
rem This script will also show the SQL being executed by the user. NOTE: If
rem the locking user is no longer executing any SQL, rows returned will be 0.
rem The user, however, is still locking the above users since he/she has not
rem yet committed his/her transaction -- maybe they are away from their desk??
rem You should call them and ask them to either commit or rollback.
rem
SELECT
x.sid, x.serial#, x.username, y.id1, z.sql_text SQL
FROM v$sqltext z, v$session x, v$lock y
WHERE y.id1 IN
(SELECT distinct b.id1
FROM v$lock b, v$session a
WHERE b.kaddr = a.lockwait)
and x.sid = y.sid
and z.hash_value = x.sql_hash_value
and y.request = 0;
chk_usr.sql
rem chk_usr.sql
rem Author: TUSC (The Ultimate Software Consultants)
rem Use this script to view the SQL being executed by your top CPU user(s)
define = &&user
col sid format 999999
col serial# format 999999
select b.sid,b.serial#,a.sql_text from v$sqltext a,
v$session b,v$process c
where
b.paddr=c.addr
and a.hash_value=b.sql_hash_value
and (c.spid='&&user' or b.process='&&user');
undefine user
dup_keys.sql
rem dup_keys.sql
rem Author: TUSC (The Ultimate Software Consultants)
rem Use this script to list all duplicate table rows
rem Column_name should be the column name(s) that
rem represent the columns in your constraint (see talbe dba_cons_columns)
rem Note: If your constraint consists of more than one column, you will also need
rem to modify the first SELECT statement -- adding the columns to the WHERE clause
SELECT a.rowid
FROM table_name a
WHERE a.rowid > (SELECT min(b.rowid)
FROM table_name b
WHERE a.column_name = b.column_name);
del_dups.sql
rem del_dups.sql
rem Author: TUSC (The Ultimate Software Consultants)
rem Use this script to delete all duplicate table rows
DELETE from table_name
SELECT a.rowid
FROM table_name a
WHERE a.rowid > (SELECT min(b.rowid)
FROM table_name b
WHERE a.column_name = b.column_name);
mon_sql.sql
rem mon_sql.sql
rem Author: TUSC (The Ultimate Software Consultants)
rem Use this script to list your worst performing SQL statements
Set Line 132
Set Pages 55
-- Break On Executions On Disk_Reads On Buffer_Gets Skip 1
Col SQL_Text format a64
Col Disk_Reads Format 999,999,990
Col Buffer_Gets Format 999,999,990
Col Avg_Buffers Format 999,999,990.9
Select parsing_user_id,
Executions,
((Disk_Reads + Buffer_Gets) / Decode(Executions, 0, 1, Executions)) Avg_Buffers,
Disk_Reads,
Buffer_Gets,
SQL_Text
From V$SQLArea
Where ((Disk_Reads + Buffer_Gets) / Decode(Executions, 0, 1, Executions)) > 10000
Or (Executions > 20000
And ((Disk_Reads + Buffer_Gets) / Decode(Executions, 0, 1, Executions)) > 500)
-- Or Upper(SQL_Text) Like '%V_DEALER%'
Order By ((Disk_Reads + Buffer_Gets) / Decode(Executions, 0, 1, Executions)) Desc
/
for_keys.sql
REM for_keys.sql
REM Author: TUSC (The Ultimate Software Consultants)
REM forkeys.sql
REM Author: TUSC
REM This script will create a spooled script called: for_keys.sql
REM that can be used to recreate foreign keys for tables you plan
REM on exporting/dropping/importing. For example: IF you are
REM going to reorg a table called PRODUCT, you should run this
REM script to recreate the foreign keys that point to columns in
REM the PRODUCT table from other tables. When the script prompts
REM you for a table name, type the table name of the table you
REM plan on exporting/dropping/importing, i.e., PRODUCT
set echo off
set arraysize 5
set feedback off
set heading off
set linesize 200
set long 1000000
set pagesize 0
set serveroutput on
set space 0
set termout off
set verIFy off
REM ************************************************************************
REM Initialize error trap.
whenever SQLERROR exit failure rollback;
REM ************************************************************************
REM Initialize variables.
col Statement_Text format a200 word_wrapped
REM Drop any migration control tables that already exist.
spool for_keys.log
DECLARE
v_cursor integer;
v_table varchar2(30);
v_owner varchar2(30);
cursor C1 is
SELECT tname
FROM tab
WHERE tname like 'TMP_FOREIGN_KEYS%' AND
tabtype='TABLE';
BEGIN
open C1;
loop
fetch C1 into v_table;
exit when C1%NOTFOUND;
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor, 'drop table '||v_table, dbms_sql.v7);
dbms_sql.close_cursor(v_cursor);
END loop;
close C1;
EXCEPTION
when OTHERS THEN
dbms_output.put_line('Program Error - Unable to drop migration control tables.');
dbms_output.put_line(SQLERRM);
raise_application_error(-20000, 'END of error message');
END;
/
spool off
REM ************************************************************************
REM Prompt the user for the required parameters.
spool for_keys.log
set termout on
prompt
accept v_owner prompt 'Enter the schema of the objects to be exported: ';
prompt
accept v_ts prompt 'Enter the name of the working tablespace: ';
prompt
accept v_table prompt 'Enter the table name: ';
prompt
set termout off
DECLARE
tester number;
BEGIN
SELECT count(*)
into Tester
FROM dba_tablespaces
WHERE tablespace_name=upper('&v_ts');
IF Tester=0 THEN
raise NO_DATA_FOUND;
END IF;
EXCEPTION
when OTHERS THEN
raise_application_error(-20000,'Program Error-'||'&v_ts'||'tablespace.');
END;
/
spool off
set termout on
prompt
prompt Generating script to create constraints...
prompt
set termout off
spool for_keys.log
create table tmp_foreign_keys
(Statement_Sequence number not null,
Statement_Text long)
tablespace &v_ts
storage (initial 1M next 1M pctincrease 0);
DECLARE
BAD_CONSTRAINT exception;
v_column varchar2(30);
v_pk_table varchar2(30);
v_table varchar2(30);
v_sequence number := 0;
cursor C1 is
SELECT b.owner,
b.table_name,
b.constraint_name,
b.constraint_type,
b.search_condition,
b.r_owner,
b.r_constraint_name,
b.delete_rule
FROM dba_constraints a,dba_constraints b
WHERE a.table_name = upper('&v_table')
AND a.owner = upper('&v_owner')
AND a.constraint_name = b.r_constraint_name;
cursor C2 (v_owner varchar2, v_constraint varchar2) is
SELECT table_name,
column_name
FROM dba_cons_columns
WHERE owner = v_owner AND
constraint_name = v_constraint
ORDER by position;
cursor C4 (v_owner varchar2, v_constraint varchar2) is
SELECT distinct table_name
FROM dba_cons_columns
WHERE owner = v_owner AND
constraint_name = v_constraint;
BEGIN
for C1_Rec in C1 loop
IF C1_Rec.constraint_name like 'SYS_%'
THEN
v_sequence := v_sequence + 1;
insert into tmp_foreign_keys(Statement_Sequence,
Statement_Text)
values (v_sequence,
'alter table '||C1_Rec.owner||'.'||C1_Rec.table_name||' add '||'foreign key (');
ELSE
v_sequence := v_sequence + 1;
insert into tmp_foreign_keys(Statement_Sequence,
Statement_Text)
values (v_sequence,
'alter table '||C1_Rec.owner||'.'||C1_Rec.table_name||' add constraint '||C1_Rec.constraint_name||' foreign key (');
END IF;
open C2(C1_Rec.owner, C1_Rec.constraint_name);
loop
fetch C2 into v_table, v_column;
exit when C2%NOTFOUND;
IF C2%ROWCOUNT > 1 THEN
v_sequence := v_sequence + 1;
insert into tmp_foreign_keys(Statement_Sequence,
Statement_Text)
values (v_sequence,
',');
END IF;
v_sequence := v_sequence + 1;
insert into tmp_foreign_keys(Statement_Sequence,
Statement_Text)
values (v_sequence,
v_column);
END loop;
close C2;
open C4(C1_Rec.r_owner, C1_Rec.r_constraint_name);
fetch C4 into v_pk_table;
close C4;
v_sequence := v_sequence + 1;
insert into tmp_foreign_keys(Statement_Sequence,
Statement_Text)
values (v_sequence,
') references '||C1_Rec.r_owner||'.'||v_pk_table||'(');
open C2(C1_Rec.r_owner, C1_Rec.r_constraint_name);
loop
fetch C2 into v_table, v_column;
exit when C2%NOTFOUND;
IF C2%ROWCOUNT > 1 THEN
v_sequence := v_sequence + 1;
insert into tmp_foreign_keys(Statement_Sequence,
Statement_Text)
values (v_sequence,
',');
END IF;
v_sequence := v_sequence + 1;
insert into tmp_foreign_keys(Statement_Sequence,
Statement_Text)
values (v_sequence,
v_column);
END loop;
close C2;
v_sequence := v_sequence + 1;
insert into tmp_foreign_keys(Statement_Sequence,
Statement_Text)
values (v_sequence,
')');
IF C1_Rec.delete_rule = 'CASCADE' THEN
v_sequence := v_sequence + 1;
insert into tmp_foreign_keys(Statement_Sequence,
Statement_Text)
values (v_sequence,
'on delete cascade');
END IF;
insert into tmp_foreign_keys(Statement_Sequence,
Statement_Text)
values (v_sequence,
';');
END loop;
EXCEPTION
when BAD_CONSTRAINT THEN
dbms_output.put_line('Program Error - Unsupported constraint type.');
raise_application_error(-20000, 'END of error message');
when OTHERS THEN
dbms_output.put_line('Program Error - Block error at location 32.');
dbms_output.put_line(SQLERRM);
raise_application_error(-20000, 'END of error message');
END;
/
spool off
spool for_keys.sql
SELECT Statement_Text
FROM tmp_foreign_keys
ORDER by Statement_Sequence;
spool off
CREATING A NEW CONTROLFILE TO INCREASE MAXDATAFILES (or any other MAX... parameters)
- Shutdown database; Backup database
- Start up database
- From sqlplus, svrmgr or sqldba, type: alter database backup controlfile to trace;
- Type: shutdown immediate:
- Go to the operating system and go to the USER_DUMP_DEST directory
- Find the newest trace file
- Edit the trace file and change MAXDATAFILES to the new value.
You will also need to delete all of the lines prior to the line that begins: STARTUP NOMOUNT.
See sample text below (the example is for a database using ARCHIVELOG):
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORACLE" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1600
LOGFILE
GROUP 1 'D:\ORAWIN95\DATABASE\LOG2ORCL.ORA' SIZE 200K,
GROUP 2 'D:\ORAWIN95\DATABASE\LOG1ORCL.ORA' SIZE 200K
DATAFILE
'D:\ORAWIN95\DATABASE\SYS1ORCL.ORA',
'D:\ORAWIN95\DATABASE\USR1ORCL.ORA',
'D:\ORAWIN95\DATABASE\RBS1ORCL.ORA',
'D:\ORAWIN95\DATABASE\TMP1ORCL.ORA'
;
# Database can now be opened normally.
ALTER DATABASE OPEN RESETLOGS;
- From sqlplus, svrmgr or sqldba, run the edited trace file from step 7.
- Shutdown database;backup database
THE END