How Long SQL will take

select *
from ( select
opname
,target
,round(((elapsed_seconds * (totalwork - sofar)) / sofar), 2) time_remaining
,sofar
,totalwork
,units
,elapsed_seconds
,message
,a.start_time
,b.sql_text
from v$sql b
,v$session_longops a
where b.sql_id (+) = a.sql_id
and round(((elapsed_seconds * (totalwork - sofar)) / sofar), 2) > 0
)
order by time_remaining desc ;



CREATE OR REPLACE FUNCTION sys.get_sqltext(p_hash_value NUMBER)
RETURN VARCHAR2 IS
l_sql_text VARCHAR2(32767) := '';
l_sql_left NUMBER := 4000;
BEGIN
FOR i IN(SELECT * FROM sys.v$sqltext
WHERE hash_value = p_hash_value ORDER BY piece
) LOOP
IF l_sql_left > 64 THEN
l_sql_text := l_sql_text || i.sql_text;
ELSIF l_sql_left > 0 THEN
l_sql_text := l_sql_text || SUBSTR(i.sql_text,1,l_sql_left);
END IF;
l_sql_left := l_sql_left - LENGTH(i.sql_text);
END LOOP;
RETURN l_sql_text;
END get_sqltext;
/

show errors
GRANT EXECUTE ON sys.get_sqltext TO PUBLIC;

spool longops
SELECT l.*, NVL(s.sql_text
, sys.get_sqltext(l.sql_hash_value)) sql_text
FROM (
SELECT l.target, l.operation, l.sql_hash_value
, SUM(secs) secs, SUM(execs) execs
FROM (
SELECT l.sid, l.serial#, l.sql_address, l.sql_hash_value
, l.target, l.operation
, MAX(l.last_update_time-l.start_time)*86400 secs
, COUNT(*) execs
, SUM(totalwork) totalwork
FROM (
SELECT l.*
, SUBSTR(l.message,1,instr(l.message,':',1,1)-1) operation
FROM v$session_longops l) l
GROUP BY l.sid, l.serial#, l.sql_address
, l.sql_hash_value, l.target, l.operation
) l
GROUP BY l.target, l.operation, l.sql_hash_value
) l
LEFT OUTER JOIN v$sql s ON s.hash_value = l.sql_hash_value
--AND s.address = l.sql_address
AND s.child_number = 0
ORDER BY secs desc
/
spool off

===========

COL how_long FORMAT 99,990 HEAD “Time|Run”
COL secs_left FORMAT 99,990 HEAD “Appr.|Secs Left”
COL sofar FORMAT 9,999,990 HEAD “Work|Done”
COL totalwork FORMAT 9,999,990 HEAD “Total|Work”
COL percent FORMAT 999.90 HEAD “%|Done”
–
select
a.username
,a.opname
,b.sql_text
,to_char(a.start_time,’DD-MON-YY HH24:MI’) start_time
,a.elapsed_seconds how_long
,a.time_remaining secs_left
,a.sofar
,a.totalwork
,round(a.sofar/a.totalwork*100,2) percent
from v$session_longops a
,v$sql b
where a.sql_address = b.address
and a.sql_hash_value = b.hash_value
and a.sofar <> a.totalwork
and a.totalwork != 0;

SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 355
SET FEEDBACK OFF

SELECT df.AUTOEXTENSIBLE "AutoExtent",Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,50) "File Name",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(df.maxbytes/1024/1024,2) "Size (MaxMBytes)",
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 df.tablespace_name='SHIPPING_INDX' and 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

Oracle 10G checking data file space

SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 355
SET FEEDBACK OFF

SELECT df.AUTOEXTENSIBLE "AutoExtent",Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,50) "File Name",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(df.maxbytes/1024/1024,2) "Size (MaxMBytes)",
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 df.tablespace_name='SHIPPING_INDX' and 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

Create standby controlfile

oracle@anuj> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Aug 6 13:22:27 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

oracle@anuj>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/controlfile.ctl';

Database altered

Oracle reserved words

col keyword for a30
select *
from v$reserved_words
where keyword like upper('%&word%')
order by keyword;

create csv file glogin

SET UNDERLINE OFF
SET COLSEP ','
SET LINES 100 PAGES 100
SET FEEDBACK off
--(If you don’t want column headings in CSV file)
SET HEADING off
Spool C:\Export\EMP.csv
--Now the actual query
SELECT * FROM EMP;
Spool OFF

Oracle Outstanding Alert

set linesize 145
set pagesize 1000
set trimout on
set trimspool on
Set Feedback off
set timing off
set verify off


prompt

prompt -- ----------------------------------------------------------------------- ---

prompt -- Outstanding Alert ---

prompt -- ----------------------------------------------------------------------- ---

prompt

set linesize 200
column ct format a18 heading "Creation Time"
column instance_name format a8 heading "Instance|Name"
column object_type format a14 heading "Object|Type"
column message_type format a9 heading "Message|Type"
column message_level format 9999 heading "Mess.|Lev."
column reason format a30 heading "Reason"
column suggested_action format a75 heading "Suggested|Action"

Select
To_Char(Creation_Time, 'DD-MM-YYYY HH24:MI') ct
, instance_name
, object_type
, message_type
, message_level
, reason
, suggested_action
From dba_outstanding_alerts
Order By Creation_Time
;


Prompt