Oracle Database Administration

Hi, Here i am going to write the queries related to tablespace usage. We can find the free space,used space and its percentage of tablespace and datafiles separately. If your tablespace is not auto extendable, you can use the below query given in 1 a) and 1 b) to get space usage of tablespace 1 a) Combine query with sm$ts_avail,sm$ts_used,sm$ts_free: ===================================================== set pages 1000 break on report compute sum of Total_MB on report compute sum of Used_MB on report compute sum of Free_MB on report select a.TABLESPACE_NAME, round(Curr_Size,1) Total_MB,round(Used,1) Used_MB,round(Free,1) Free_MB, round(100*(1-free/Curr_Size),1) Usage from (select TABLESPACE_NAME,BYTES/(1024*1024) Curr_Size from sm$ts_avail) a ,(select TABLESPACE_NAME,BYTES/(1024*1024) Used from sm$ts_used) b, (select TABLESPACE_NAME,BYTES/(1024*1024) Free from sm$ts_free) c where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) and a.TABLESPACE_NAME=c.TABLESPACE_NAME order by 1

Hi all, In the alert log we found the below ORA-600 internal error.  Alert log Error is below: ORA-00600: internal error code, arguments: [kxfxsStatus: nojoin but already sent joinOK], [197888], [], [], [], [], [], [], [], [], [], [] We had to report this error with our analysis to our manager. We had searched the related document for this error in Oracle support website and found the following document. Bug 9785632 - Parallel query fails with OERI[kxfxsstatus: nojoin but already sent joinok] (Doc ID 9785632.8) As per above Doc ID 9785632.8, Affects:: Versions confirmed as being affected ·         11.2.0.1 ·         11.1.0.7 Fixed: This issue is fixed in         ·         12.1.0.1 (Base Release) ·         11.2.0.2 (Server Patch Set) ·         11.2.0.1 Bundle Patch 8 for Exadata Database Symptoms: ·         Internal Error May Occur (ORA-600) ·         ORA-600 [kxfxsStatus: nojoin but already sent joinOK] Description: ORA-600:

Hi all, One of our production databases is frequently experienced below errors in alert log and database became unresponsive due to continuous hit of this error and CPU load also went high at that time. Here the time duration for this error is between Tue Dec 09 15:33:18 2014 and Tue Dec 09 15:50:27 2014. Errors in alert log: ORA-04031: unable to allocate 264 bytes of shared memory ("shared pool","unknown object","KGLH0^6ab33a","kglob") ORA-04031: unable to allocate 120 bytes of shared memory ("shared pool","unknown object","CCUR^857b59d8","kkocsCardEst: kkocsNodeAllocated") ORA-04031: unable to allocate 1072 bytes of shared memory ("shared pool","select HIERARCHY_TYPE, SITE_...","CCUR^28cddb74","kkocs.c.kgght") ORA-04031: unable to allocate 264 bytes of shared memory ("shared pool","unknown object","CCUR^9d1660bd",&

Here we will see how to clone / move oracle 10G R2 (10.2.0.3.0) home and database from one windows server to another windows server. First i'll explain you the situation where the above scenario arose in our environment. Due to some reasons, every VM server in our company is moved to new storage from old storage. While moving one of the server which contains oracle 10g r2 (10.2.0.3.0) database, we got some error. Hence We are not able to complete the task for only that server. So our network team installed a new machine with the same windows operating systems in the new storage. Now our job is to reconfigure same oracle 10g (10.2.0.3.0) database for our local development team in the new server. But we don't have any Oracle 10G R2 software with us. Hence we had raised a SR with Oracle support. But Oracle is not providing Oracle 10G R2 software anymore and it came up with the following reasons. "Hello Ramesh,  Please note that, from 1st of march 2014 all the D

Hi all, Here we'll see how do we check the free space and used space of each datafile separately in the tablespace as i said in previous post Tablespace usage details (Current size,used size and free space with extendable We can use the below query to check the space usage of each and every data file in the tablespaces. break on report compute sum of allocated_mb on report compute sum of used_mb on report compute sum of free_space_mb on report set lines 500 COLUMN free_space_mb format 999999.90 COLUMN allocated_mb format 999999.90 COLUMN used_mb format 999999.90 col file_name for a60 SELECT   SUBSTR (df.NAME, 1, 60) file_name, df.bytes / 1024 / 1024 allocated_mb,          ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))                used_mb,          NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb     FROM v$datafile df, dba_free_space dfs    WHERE df.file# = dfs.file_id(+) and dfs.TABLESPACE_NAME=' _DATA ' GROUP BY dfs

Need tablespace usage details? want to check how much space used from current size and max size of the tablespace? Below are some queries to check the free space with extendable size,used space & their percentage of usage. The below query is to check the particular tablespace usage details set lines 300 col TABLESPACE_NAME for a30 col file_name for a45 select    a.TABLESPACE_NAME,    round(avail,1) curr_size_MB,    round(used,1) used_MB,    round(total,1) Maxsize_MB,    round(free+extentable_MB,1) "Free+extendable_MB",    round(100*(1-(free+extentable_MB)/total),1)"Usage %" from (       select         TABLESPACE_NAME,         sum(BYTES)/(1024*1024) avail,         sum(MAXBYTES)/(1024*1024) total,         (sum(MAXBYTES)/(1024*1024) - sum(BYTES)/(1024*1024)) extentable_MB       from dba_data_files       group by TABLESPACE_NAME) a,      (       select          TABLESPACE_NAME,          sum(BYTES)/(1024*1024) free       from dba_free_space