Oracle Scripts 1

Script for Deleting Duplicate rows in a table: -

Delete from table_name where rowid not in (select max(rowid) from table
group by duplicate_values_field_name);
_________________________________________________

Finding chained rows in a table: -

@$ORACLE_HOME/rdbms/admin/utlchain.sql;

analyze &object_type &object_name list chained rows into chained_rows;
___________________________________________________

Finding oracle Invalid Objects: -

break on c1 skip 2
set pages 999
col c1 heading 'owner' format a15
col c2 heading 'name' format a40
col c3 heading 'type' format a15
ttitle 'Invalid Objects'
select owner c1,object_type c3,object_name c2 from dba_objects
where status != 'VALID'
order by owner,object_type;


Compiling invalid Objects: -

Set heading off;
set feedback off;
set echo off;
Set lines 999;
Spool run_invalid.sql
select
'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
dba_objects where status = 'INVALID'
and object_type in ('PACKAGE','FUNCTION','PROCEDURE');
spool off;
set heading on;
set feedback on;
set echo on;

@run_invalid.sql

(or)

@$ORACLE_HOME/rdbms/admin/utlrp.sql
___________________________________________

Getting Query which is taking more CPU: -


select SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE = (select sql_hash_value from v$session where SID = (select s.sid from v$process p, v$session s where s.paddr=p.addr and p.spid =&top_comm_PID)) ;

____________________________________________

Creating Tablespace Script:-

set verify off;
set termout off;
set feedback off;
set echo off;
set pagesize 0;
set termout on;
select 'Creating tablespace build script...' from dual;
set termout off;

create table ts_temp (lineno number, ts_name varchar2(30),
text varchar2(800));

DECLARE
CURSOR ts_cursor IS select tablespace_name,
initial_extent,
next_extent,
min_extents,
max_extents,
pct_increase,
status
from sys.dba_tablespaces
where tablespace_name != 'SYSTEM'
and status != 'INVALID'
order by tablespace_name;
CURSOR df_cursor (c_ts VARCHAR2) IS select file_name,
bytes,
autoextensible,
maxbytes,
increment_by
from sys.dba_data_files
where tablespace_name = c_ts
and tablespace_name != 'SYSTEM'
order by file_name;
lv_tablespace_name sys.dba_tablespaces.tablespace_name%TYPE;
lv_initial_extent sys.dba_tablespaces.initial_extent%TYPE;
lv_next_extent sys.dba_tablespaces.next_extent%TYPE;
lv_min_extents sys.dba_tablespaces.min_extents%TYPE;
lv_max_extents sys.dba_tablespaces.max_extents%TYPE;
lv_pct_increase sys.dba_tablespaces.pct_increase%TYPE;
lv_status sys.dba_tablespaces.status%TYPE;
lv_file_name sys.dba_data_files.file_name%TYPE;
lv_bytes sys.dba_data_files.bytes%TYPE;
lv_autoextensible sys.dba_data_files.autoextensible%TYPE;
lv_maxbytes sys.dba_data_files.maxbytes%TYPE;
lv_increment_by sys.dba_data_files.increment_by%TYPE;
lv_first_rec BOOLEAN;
lv_string VARCHAR2(800);
lv_lineno number := 0;

procedure write_out(p_line INTEGER, p_name VARCHAR2,
p_string VARCHAR2) is
begin
insert into ts_temp (lineno, ts_name, text) values
(p_line, p_name, p_string);
end;

BEGIN
OPEN ts_cursor;
LOOP
FETCH ts_cursor INTO lv_tablespace_name,
lv_initial_extent,
lv_next_extent,
lv_min_extents,
lv_max_extents,
lv_pct_increase,
lv_status;
EXIT WHEN ts_cursor%NOTFOUND;
lv_lineno := 1;
lv_string := ('CREATE TABLESPACE '||lower(lv_tablespace_name));
lv_first_rec := TRUE;
write_out(lv_lineno, lv_tablespace_name, lv_string);
OPEN df_cursor(lv_tablespace_name);
LOOP
FETCH df_cursor INTO lv_file_name,
lv_bytes,
lv_autoextensible,
lv_maxbytes,
lv_increment_by;
EXIT WHEN df_cursor%NOTFOUND;
if (lv_first_rec) then
lv_first_rec := FALSE;
lv_string := 'DATAFILE ';
else
lv_string := lv_string || ',';
end if;
lv_string:=lv_string||''''||lv_file_name||''''||
' SIZE '||to_char(lv_bytes) || ' REUSE';
if (lv_autoextensible = 'YES') then
lv_string:=lv_string||' AUTOEXTEND ON'||
' NEXT '||to_char(lv_increment_by)||
' MAXSIZE '||to_char(lv_maxbytes);
END IF;
END LOOP;
CLOSE df_cursor;
lv_lineno := lv_lineno + 1;
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := (' DEFAULT STORAGE (INITIAL ' ||
to_char(lv_initial_extent) ||
' NEXT ' || lv_next_extent);
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := (' MINEXTENTS ' ||
lv_min_extents ||
' MAXEXTENTS ' || lv_max_extents);
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := (' PCTINCREASE ' ||
lv_pct_increase || ')');
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_string := (' '||lv_status);
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:='/';
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:=' ';
write_out(lv_lineno, lv_tablespace_name, lv_string);
END LOOP;
CLOSE ts_cursor;
END;
/

spool create_tablespaces.sql
set heading off
set recsep off
col text format a80 word_wrap


select text
from ts_temp
order by ts_name, lineno;

spool off;

drop table ts_temp;

exit

Create Users Script:-

set pagesize 0
set escape on
spool create_user.sql
select 'create user ' || U.username || ' identified ' ||
DECODE(password,
NULL, 'EXTERNALLY',
' by values ' || '''' || password || ''''
)
|| chr(10) ||
'default tablespace ' || default_tablespace || chr(10) ||
'temporary tablespace ' || temporary_Tablespace || chr(10) ||
' profile ' || profile || chr(10) ||
'quota ' ||
decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) ||
' on ' || default_tablespace ||
decode (account_status,'LOCKED', ' account lock',
'EXPIRED', ' password expire',
'EXPIRED \& LOCKED', ' account lock password expire',
null)
||
';'
from dba_users U, dba_ts_quotas Q
-- Comment this clause out to include system & default users
where U.username not in ('SYS','SYSTEM',
'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',
'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',
'HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM',

'QS_CB','QS_CS','PERFSTAT')
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
;
spool off
set pagesize 100
set escape off


To delete the non-default users:-

set pages 0
spool dropuser.sql
select 'drop user '||username|| 'cascade;' from dba_users
where username not in ('MGMT_VIEW','SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS', 'EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS',
'SI_INFORMTN_SCHEMA','OLAPSYS','SCOTT','TSMSYS','BI','PM','MDDATA',
'IX','SH','DIP','OE','HR')
/


24hr in 30 days logswitches:-

SET LINESIZE 150
COLUMN day FORMAT A3
COLUMN Total FORMAT 99G990
COLUMN h00 FORMAT 999
COLUMN h01 FORMAT 999
COLUMN h02 FORMAT 999
COLUMN h03 FORMAT 999
COLUMN h04 FORMAT 999
COLUMN h05 FORMAT 999
COLUMN h06 FORMAT 999
COLUMN h07 FORMAT 999
COLUMN h08 FORMAT 999
COLUMN h09 FORMAT 999
COLUMN h10 FORMAT 999
COLUMN h11 FORMAT 999
COLUMN h12 FORMAT 999
COLUMN h13 FORMAT 999
COLUMN h14 FORMAT 999
COLUMN h15 FORMAT 999
COLUMN h16 FORMAT 999
COLUMN h17 FORMAT 999
COLUMN h18 FORMAT 999
COLUMN h19 FORMAT 999
COLUMN h20 FORMAT 999
COLUMN h21 FORMAT 999
COLUMN h22 FORMAT 999
COLUMN h23 FORMAT 999
COLUMN h24 FORMAT 999
BREAK ON REPORT
COMPUTE MAX OF "Total" ON REPORT
COMPUTE MAX OF "h00" ON REPORT
COMPUTE MAX OF "h01" ON REPORT
COMPUTE MAX OF "h02" ON REPORT
COMPUTE MAX OF "h03" ON REPORT
COMPUTE MAX OF "h04" ON REPORT
COMPUTE MAX OF "h05" ON REPORT
COMPUTE MAX OF "h06" ON REPORT
COMPUTE MAX OF "h07" ON REPORT
COMPUTE MAX OF "h08" ON REPORT
COMPUTE MAX OF "h09" ON REPORT
COMPUTE MAX OF "h10" ON REPORT
COMPUTE MAX OF "h11" ON REPORT
COMPUTE MAX OF "h12" ON REPORT
COMPUTE MAX OF "h13" ON REPORT
COMPUTE MAX OF "h14" ON REPORT
COMPUTE MAX OF "h15" ON REPORT
COMPUTE MAX OF "h16" ON REPORT
COMPUTE MAX OF "h17" ON REPORT
COMPUTE MAX OF "h18" ON REPORT
COMPUTE MAX OF "h19" ON REPORT
COMPUTE MAX OF "h20" ON REPORT
COMPUTE MAX OF "h21" ON REPORT
COMPUTE MAX OF "h22" ON REPORT
COMPUTE MAX OF "h23" ON REPORT

SELECT TRUNC(first_time) AS "Date",
TO_CHAR(first_time, 'Dy') AS "Day",
COUNT(1) AS "Total",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'00',1,0)) AS "h00",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'01',1,0)) AS "h01",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'02',1,0)) AS "h02",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'03',1,0)) AS "h03",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'04',1,0)) AS "h04",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'05',1,0)) AS "h05",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'06',1,0)) AS "h06",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'07',1,0)) AS "h07",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'08',1,0)) AS "h08",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'09',1,0)) AS "h09",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'10',1,0)) AS "h10",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'11',1,0)) AS "h11",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'12',1,0)) AS "h12",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'13',1,0)) AS "h13",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'14',1,0)) AS "h14",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'15',1,0)) AS "h15",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'16',1,0)) AS "h16",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'17',1,0)) AS "h17",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'18',1,0)) AS "h18",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'19',1,0)) AS "h19",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'20',1,0)) AS "h20",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'21',1,0)) AS "h21",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'22',1,0)) AS "h22",
SUM(DECODE(TO_CHAR(first_time, 'HH24'),'23',1,0)) AS "h23"
FROM V$log_history
GROUP BY TRUNC(first_time), TO_CHAR(first_time, 'Dy')
ORDER BY 1;
CLEAR BREAKS

____________________________________________

Log Miner: -

/* first we have to set utl_file_dir parameter */

@$ORACLE_HOME/rdbms/admin/dbmslm.sql;
@$ORACLE_HOME/rdbms/admin/dbmslmd.sql;

exec dbms_logmnr_d.build('&file_name','&path_of_logmnr',-
dbms_logmnr_d.store_in_flat_file);

exec dbms_logmnr.add_logfile(logfilename=>'&path_of_the_logfile',-
options=>dbms_logmnr.new);

exec dbms_logmnr.add_logfile(logfilename=>'&path_of_2nd_logfile_or_archive',-
options=>dbms_logmnr.addFILE);

exec dbms_logmnr_d.build(dictionary_filename=>'&file_name',-
dictionary_location=>'&path_of_the_logmnr');

exec dbms_logmnr.start_logmnr(DictFileName=>'&path_of_the_file_name');

select username,sql_redo,to_char(timestamp,'dd-mm-yyy hh24:mi:ss')
time_in_sec from v$logmnr_contents where username='&user_name';

____________________________________________

Monitor Import Speed: -

SELECT
SUBSTR(sql_text, INSTR(sql_text,'INTO "'),30) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM
sys.v_$sqlarea
WHERE
sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions > 0;


--------------------------------------------------------------


script to calculate Tablespace Free Space:-

column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a19 heading "Tablespace Name"
column Kbytes format 999,999,999 heading "Kbytes"
column used format 999,999,999 heading "Used"
column free format 999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
column max_size format 999,999,999 heading "MaxPoss|Kbytes"
column pct_max_used format 999. heading "%|Max|Used"
break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report


select (select decode(extent_management,'LOCAL','*',' ') ||
decode(segment_space_management,'AUTO','a ','m ')
from dba_tablespaces where tablespace_name = b.tablespace_name) ||
nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_temp_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 1
/