SQL

–9.Show datafile usage as map

-– X: in use
-- =: free

set serveroutput on size 20000
set linesize 80

declare
  cursor c_map(p_file_id number) is
  (select file_id,block_id,blocks,'X' used
   from dba_extents where file_id=p_file_id
   union
   select file_id,block_id,blocks,'=' used
   from dba_free_space where file_id=p_file_id
   ) order by 1,2,3;
 cursor c_file is
 select file_name,file_id,blocks/400  bsize from dba_data_files;
 j number :=1;
 xsize number;
begin
   for r_file in c_file
   loop
       dbms_output.put_line('Map of '||r_file.file_name);
       for r_map in c_map(r_file.file_id)
       loop
           for i  in 1..r_map.blocks/r_file.bsize
           loop
            dbms_output.put(R_MAP.USED);
            if j>=80 then
        j :=1;dbms_output.new_line;
            else
               j := j+1;
            end if;
           end loop;
       end loop;
       dbms_output.new_line;j:=1;
   end loop;
end;
/

–8.Show shrinkable datafile


set serveroutput on
declare
CURSOR C_DBFILE IS
SELECT TABLESPACE_NAME, FILE_NAME, FILE_ID, BYTES
FROM SYS.DBA_DATA_FILES
WHERE STATUS != ‘INVALID’
ORDER BY TABLESPACE_NAME, FILE_ID;

CURSOR C_SPACE(V_FILE_ID IN NUMBER) IS
SELECT BLOCK_ID, BLOCKS
FROM SYS.DBA_FREE_SPACE
WHERE FILE_ID = V_FILE_ID
ORDER BY BLOCK_ID DESC;
BLOCKSIZE NUMBER;
FILESIZE NUMBER;
EXTSIZE NUMBER;

BEGIN
DBMS_OUTPUT.ENABLE(99999999);

SELECT VALUE
INTO BLOCKSIZE
FROM V$PARAMETER
WHERE NAME = ‘db_block_size’;
FOR C_REC1 IN C_DBFILE LOOP
FILESIZE := C_REC1.BYTES;
<<OUTER>>
FOR C_REC2 IN C_SPACE(C_REC1.FILE_ID) LOOP
EXTSIZE := ((C_REC2.BLOCK_ID – 1) * BLOCKSIZE +
C_REC2.BLOCKS * BLOCKSIZE);
IF EXTSIZE = FILESIZE THEN
FILESIZE := (C_REC2.BLOCK_ID – 1) * BLOCKSIZE;
ELSE
EXIT OUTER;
END IF;
END LOOP OUTER;
IF FILESIZE = C_REC1.BYTES THEN
DBMS_OUTPUT.PUT_LINE(‘Tablespace: ‘ || C_REC1.TABLESPACE_NAME);
DBMS_OUTPUT.PUT_LINE(‘ Datafile: ‘ || C_REC1.FILE_NAME);
DBMS_OUTPUT.PUT_LINE(‘Can not be resized, no free space at end of file.’);
DBMS_OUTPUT.PUT_LINE(‘.’);
ELSE
IF FILESIZE < 2 * BLOCKSIZE THEN
DBMS_OUTPUT.PUT_LINE(‘Tablespace: ‘ || C_REC1.TABLESPACE_NAME);
DBMS_OUTPUT.PUT_LINE(‘ Datafile: ‘ || C_REC1.FILE_NAME);
DBMS_OUTPUT.PUT_LINE(‘Actual size: ‘ || C_REC1.BYTES/(1024*1024)||’M’);
DBMS_OUTPUT.PUT_LINE(‘Min resize: ‘ || 2 * BLOCKSIZE/(1024*1024)||’M’);
DBMS_OUTPUT.PUT_LINE(‘ALTER DATABASE DATAFILE ”’||C_REC1.FILE_NAME||”’ resize 1M;’);
DBMS_OUTPUT.PUT_LINE(‘ ‘);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Tablespace: ‘ || C_REC1.TABLESPACE_NAME);
DBMS_OUTPUT.PUT_LINE(‘ Datafile: ‘ || C_REC1.FILE_NAME);
DBMS_OUTPUT.PUT_LINE(‘Actual size: ‘ || C_REC1.BYTES/(1024*1024)||’M’);
DBMS_OUTPUT.PUT_LINE(‘Min resize: ‘ || FILESIZE/(1024*1024)||’M’);
DBMS_OUTPUT.PUT_LINE(‘ALTER DATABASE DATAFILE ”’||C_REC1.FILE_NAME||”’ resize ‘||ceil(FILESIZE/(1024*1024))||’M;’);
DBMS_OUTPUT.PUT_LINE(‘ ‘);
END IF;
END IF;
END LOOP;
END;
/

–7.Check tablespaces usage

select a.TABLESPACE_NAME, round(total) Total_G, round(free) Free_G, round(100*(1-free/total)) “Usage%” from (select TABLESPACE_NAME,sum(BYTES)/(1024*1024*1024) total from dba_data_files group by TABLESPACE_NAME) a ,(select TABLESPACE_NAME,sum(BYTES)/(1024*1024*1024) free from dba_free_space group by TABLESPACE_NAME) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) order by 4 desc;

–6.Generate statements to disable/enable foreign key:
//disable
select ‘Alter table ‘||owner||’.’||table_name||’ disable constraint ‘||constraint_name||’;’
from dba_constraints where r_constraint_name in
(select constraint_name from dba_constraints
where table_name in (‘TABLE_NAME’));

//enable novalidate
select ‘Alter table ‘||owner||’.’||table_name||’ enable novalidate constraint ‘||constraint_name||’;’
from dba_constraints where r_constraint_name in
(select constraint_name from dba_constraints
where table_name in (‘TABLE_NAME’));

–5.Get the current lock information:
select rpad(oracle_username,10) user_name,session_id sid,c.serial#,
decode(locked_mode,0,’None’,1,’Null’,2,’Row share’,
3,’Row Exclusive’,4,’Share’,5,’Share Row Exclusive’,6,’Exclusive’) lock_type,
object_name ,c.machine,c.osuser,xidusn,xidslot,xidsqn
from v$locked_object a, all_objects b, v$session c
where a.object_id=b.object_id and c.sid=a.session_id;

select sql_text from v$sqltext_with_newlines where hash_value in
(select SQL_HASH_VALUE from v$session where sid=290 ) order by piece;

Alter system kill session ‘sid,serial#’;

–4.Get wait session information in RAC
SELECT
(SELECT SQL_TEXT FROM gv$sql WHERE inst_id = GV$SESSION.INST_ID AND hash_value = GV$SESSION.SQL_HASH_VALUE AND ROWNUM =1) SQL,
GV$SESSION.MACHINE,GV$SESSION.STATUS, ‘alter system kill session ”’ || GV$SESSION.sid || ‘,’ || GV$SESSION.serial# || ”’;’ ,
EVENT, –(SELECT spid FROM gv$process g WHERE g.addr = GV$SESSION.PADDR) pid ,
GV$SESSION.INST_ID,
GV$SESSION.SID,
GV$SESSION.SERIAL#,
USERNAME,
WAIT_TIME,
SECONDS_IN_WAIT,
GV$SESSION.USERNAME
FROM GV$SESSION_WAIT, GV$SESSION
WHERE GV$SESSION_WAIT.SID = GV$SESSION.SID AND
GV$SESSION_WAIT.INST_ID = GV$SESSION.INST_ID AND
USERNAME IS NOT NULL AND
GV$SESSION_WAIT.EVENT NOT LIKE ‘SQL*Net%’
AND GV$SESSION_WAIT.EVENT NOT LIKE ‘PX Deq%’ –AND USERNAME = ‘CENTER’
ORDER BY 1, 4 DESC

–3. Get the buffer pool hit ratio
select name, 1-(physical_reads/(db_block_gets+consistent_gets)) “Hit Ratio”
from v$buffer_pool_statistics where db_block_gets+consistent_gets > 0;
or using this one
select 1-(phy.value-lob.value-dir.value)/ses.value “cache hit ration”
from v$sysstat ses, v$sysstat lob, v$sysstat dir, v$sysstat phy
where ses.name = ‘session logical reads’
and dir.name=’physical reads direct’
and lob.name =’physical reads direct (lob)’
and phy.name=’physical reads’;

–2.Get the needed size of keep buffer pool
select sum(t)*8/1024 total_m from
(
select sum(blocks) t from dba_tables where BUFFER_POOL=’KEEP’
union
select sum(leaf_blocks) t from dba_indexes where table_name in (select table_name from dba_tables where BUFFER_POOL=’KEEP’)
)

–1.Make SQL statement from text using shell command
cat 20040811.cet.list |awk ‘{print “insert into email_examid_12 values(@”$1″@,”$2″,”$3″,”$4″);”}’ |sed “s/@/’/g” |awk ‘BEGIN{i=0}{i++;if(i%100==0) print “commit;”;print $0}END{print “commit;”}’ > 20040811.cet.list.sql

6 Responses to SQL

  1. Hi Alex,

    Please guide me how to apply patch 5965763 on Oracle 10.2.0.3 on Solaris 10.

    Thankyou for your kindness

    Best regards
    Arif Rahman Hakim

  2. alexzeng says:

    hi Arif,
    Here are steps.
    1)download p5965763_10203_SOLARIS64.zip from metalink.oracle.com
    2)shutdown database
    3)unzip p5965763_10203_SOLARIS64.zip
    4)cd 5965763
    5)opatch apply
    6)opatch lsinventory

    The patch number should in the list.
    Regards,
    Alex

  3. Viadeazhu says:

    say hi to you:)

  4. Nice blog but i’m having some minor problems with the design in Opera Browser, maybe i should change Browser!

  5. kishore says:

    Hi Alex,
    Pls can you guide me how to install (Web Services) Oracle Application Server 10.1.3.1 on IBM AIX5.3L

    Do you have any steps ,can help me .

    Thanks
    Kishore

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: