Connect the Best
Connect the Best
Connect the Best
Connect the Best
Hot Backup
Redo Log
Find Value in a Table
Free space of Tablespace
User is connected or not
__________________________________________________________________________________________________
for Delete a Duplicate rows in a table: -
Delete from table_name where rowid not in (select max(rowid) from table
group by duplicate_values_field_name);
___________________________________________________________________________________________________
Chaind rows in a table: -
@$ORACLE_HOME/rdbms/admin/utlchain.sql;
analyze &object_type &object_name list chained rows into chained_rows;
___________________________________________________________________________________________________
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;
_____________________________________________________________________________________
Find blocking sessions with running a query: -
Run the following query. Basically X$KGLLK has all library cache lock and KGLLKREQ > 0 means that lock was requested by a session but session did not get it .
This query lists all blocking sessions which blocked hanging session.
SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);
