Thursday 28 March 2013

Queries I often used on table spaces sizes and space used

To check the allocated space and used space in tablespaces:

1) SELECT d.status "Status", d.tablespace_name "Name",
        TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
        TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999') "Used (M)",
        TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
        FROM sys.dba_tablespaces d,
        (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
        (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
        d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
        (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status
        "Status", d.tablespace_name "Name",
        TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
        TO_CHAR(NVL(t.bytes,0)/1024/1024,'99999999.999') "Used (M)",
        TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
        FROM sys.dba_tablespaces d,
        (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
        (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
        WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND
        D.EXTENT_MANAGEMENT LIKE 'LOCAL' AND D.CONTENTS LIKE 'TEMPORARY';


2)
 SELECT /* + RULE */  DF.TABLESPACE_NAME "Tablespace",
       DF.BYTES / (1024 * 1024 * 1024) "Size (GB)",
       SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY DF.TABLESPACE_NAME,FS.BYTES,DF.BYTES_FREE,DF.BYTES_USED
 ORDER BY 4 DESC;
 SELECT * FROM dba_temp_free_space; 


2)  To check the schemas sizes in database: 

SELECT s.owner,SUM (s.BYTES) / (1024 * 1024 * 1024) SIZE_IN_GB
FROM dba_segments s
GROUP BY S.OWNER;


3) Learn about TEMPORARY   TABALE SPACE HERE

4) Query to find the long running queries in database :

select distinct sesid,serial, spid pid, osuser,schemaname,machine, floor(last_call_et/60) last_call, EVENT,program, sql_text, Mins, P1, P2, P3,hash_value
from
    (select sesid,serial, spid, sql_text, osuser,schemaname,machine, last_call_et, floor((sysdate - to_date(decode(start_time,'// ::',to_char(sysdate,'dd/mm/yyyy
hh24:mi:ss'), start_time), 'dd/mm/yyyy hh24:mi:ss'))* 1440) Mins, EVENT,program, P1, P2, P3,hash_value
    from
        (SELECT s.sid sesid,s.serial# serial,s.program, t.sql_text, s.osuser,s.schemaname,s.machine, s.last_call_et, substr(last_load_time,9,2) || '/' || substr(last_load_time,6,2) ||'/' ||
substr(last_load_time,1,4) || ' ' ||
        substr(last_load_time,12,2) || ':' || substr(last_load_time,15,2) || ':' || substr(last_load_time,18,2) start_time, U.event, U.p1, U.p2, U.p3,
        V.spid,T.hash_value
        FROM v$session S,
        v$sql T,
        v$session_wait U,
        v$process V
        WHERE S.status = 'ACTIVE'
        AND S.sql_address = T.address
        and exists (select 1 from v$sqltext_with_newlines y where y.hash_value = S.sql_hash_value)
        and T.SQL_TEXT <> 'COMMIT'
        AND S.sql_hash_value = T.hash_value
        AND U.SID = S.SID
        and V.addr = S.paddr
        and S.type='USER'
        )
    )
order by LAST_CALL desc;


5) 

## To truncate all tables ###

1.      Select 'truncate TABLE schemaowner.'||table_name||'  ;' FROM dba_tables where owner='schemaowner';

           
2.     

## To move all the Tables and indexes from one TBS to another ##

 
1.      select 'ALTER TABLE schemaowner.'||table_name|| ' MOVE TABLESPACE  BI_EDW_DEV_DATA;' FROM dba_tables  where owner='schemaowner';


2.      select 'ALTER INDEX schemaowner.'||index_name||' REBUILD TABLESPACE BI_EDW_DEV_IDX ;' FROM DBA_INDEXES WHERE OWNER='schemaowner';





Thursday 21 March 2013

WARNING: Oracle executable binary mismatch detected.











WARNING: Oracle executable binary mismatch detected.
Binary of new process does not match binary which started instance
issue alter system set "_disable_image_check" = true to disable these messages


This message was showing in my one of development database after performing some disabling Database Vault configurations.

Oracle recommends for this warning set _disable_image_check= true

what I did:

I bounce my database solved the issue. No message showing in alert log file.

*** As we dont have chance to bounce the production DB better to set the parameter

Database init params error showing while installing fusion (IDM,SOA)

While Installing Repository Creation Utility (RCU) Installation the following error occurs:
I need to create schema with RCU to install Fusion Apps and SOA.

RCU:6107 DB Init Param Error

This can be removed simply by the following:

1. Login on your database with system user.
2. Write > show parameters processes (which will show the current value of processes).
3. If its value is less than 500 then type the following command:
ALTER SYSTEM SET PROCESSES=500 SCOPE=SPFILE;
4. Write > show parameters open_cursors (which will show the current value of open_cursors).
5. If its value is less than 500 then write the following command:
ALTER SYSTEM SET OPEN_CURSORS=500 SCOPE=SPFILE;
6. Restart your DB or system.
7. Also you can check job_queue_processes with the same above.

Wednesday 20 March 2013

Imp Linux commands I often use

To find a file :

1) find -name filename1
2) find . -name *.trc -mtime +10 -exec rm {}\;   --- It finds the file and remove if it exists from before 10 days.
3) file  file_name      <---- to fine file type
4) find -name *.req -ctime +10 -exec rm{}\;

Different TAR commands:

1) tar   -cvzf   target_file.tgz  file1 file2 file3    <---
2) tar   -xvzf   file_name.tgz     <---- To extract the TAR file
3) tar   -ztvf   file_name.tgz      <----  To see the contents of a TAR file.

Memory Usage commands:

1)  df -Th
2)  du
3)  du -sh    <----To know particular directory size
4) free -m
5) top

Unzip commands:
1) unzip filename.zip
2) unzip filename.zip -d  /dir/.   ---->> unzip a file to a particular directory  

Telnet command:

1) telnet  IPaddress  portnumber

How To mount a directory in nfs :

 In host system do the following :
( from which system you want to share a directory ) :

1)  vi /etc/exports

 

Here is softwares is the directory I want to mount 


2) restart the nfs and network with below commands:

  
 From the guest machine where you wanted that mounted directory:

1) create a mounted directory structure as in host system:
Here For Ex  : mkdir -p /mnt/softwares
2) mount -t nfs -o rw IPadressofHost:/mnt/softwares /mnt/softwares



11g database generating excessive .trc and .trm files

Hi, One of my development database generating excessive .trm and .trc files. As well as writing shell script to delete them I used adrci utility to drop them.

adrci is the utility provided to purge the diag_dest

$ adrci
adrci > purge -age 60 -type trace

above command will delete all the trace files older than one hour.

and to create a permanent policy you can use following command on the adrci prompt.

adrci> set control (SHORTP_POLICY = 24) ### Here time is in hours.


If we set control like above it will delete the files with 1 day difference.

also I found one intresting thing that is incident directory under /u01/app/oracle/diag/rdbms/dev/dev/incident

It contains more number of .trc and .trm files . I deleted them by using below command with adrci utility:


adrci > purge -age 60 -type incident