Tuesday, September 28, 2010

Query columns used by views

this SQL block will tell you all the columns being used by a view.

SET serveroutput ON
DECLARE
-- local variables, create a cursor for the records
CURSOR c1
IS
SELECT text,view_name FROM dba_views WHERE owner = &&OWNER1;
CURSOR c2
IS
SELECT table_name,
column_name
FROM dba_tab_columns
WHERE owner =
&&OWNER1
ORDER BY 1,2;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
FOR r2 IN c2
LOOP
FOR r1 IN c1
LOOP
IF (instr(lower(r1.text),lower(r2.column_name)) > 0) THEN
dbms_output.put_line(r1.view_name || ',' ||r2.column_name ||','||r2.table_name);
dBMS_OUTPUT.NEW_LINE;
END IF;
END LOOP;
END LOOP;
END;

Monday, September 20, 2010

useful when resizing datafile

this script i got from asktom.com is a usefull script to determine what object/s are in the end of the datafile which will cause for you to not to shrink it the way you want it to shrink :) (I mean if there is a big free space between that object and the other object)

column tablespace_name format a20
column "Name" format a45
break on file_id skip 1
ttitle &1
select file_id, block_id, blocks,
owner||'.'||segment_name "Name"
from sys.dba_extents
where tablespace_name = upper('&1')
UNION
select file_id, block_id, blocks,
'Free'
from sys.dba_free_space
where tablespace_name = upper('&1')
order by 1,2,3
/


set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
set pages 3000
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

Wednesday, September 15, 2010

Undo Rollback time

this query will show you how much time remaining for your rollback session.

for 9i and up

SET lines 500 pages 500 col PCT FOR a5
SELECT state ,
undoblocksdone ,
undoblockstotal ,
cputime ,
undoblocksdone / undoblockstotal * 100 percent,
TO_CHAR(((((undoblockstotal - undoblocksdone) / (undoblocksdone/cputime) / 3600) / 24) + sysdate),'dd-mon-yyyy hh24:mi:ss') timetocomplete
FROM v$fast_start_transactions;
--just a note if your doing a parallel rollback the values does not give the correct values


for 10g and up


SELECT ADDR,
ROUND(((KTUXESIZ*
(SELECT value FROM v$parameter WHERE name = 'db_block_size'
)/1024/1024)),2) "Undo Size",
KTUXESTA "Status" ,
KTUXECFL
FROM sys.x$ktuxe
WHERE KTUXESTA='ACTIVE'