Flash Recovery Area USED & FREE SPACE
select name, floor(space_limit/1024/1024) “Size MB”,ceil(space_used/1024/1024) “Used MB” from v$recovery_file_dest order by name;
Read MoreFind Sessions on RAC
select inst_id,count(*) from gv$session where username is not null group by inst_id;
Read MoreFind Schema Details in Oracle
select owner, count(1), object_type from dba_objects group by owner, object_type having owner in (select username from dba_users where default_tablespace = ‘USERS’) and object_type = ‘TABLE’;
Read MoreQuery to check all the schemas and their sizes
select OWNER,sum(bytes)/1024/1024/1000 “SIZE_IN_GB” from dba_segments group by owner order by owner;
Read MoreQuery to Display Archive Log Generation by Day in Oracle
SET PAGESIZE 70 SET LINESIZE 400 COL “Generation Date” FORMAT a30 SELECT TRUNC(completion_time) “Generation Date” , round(SUM(blocks*block_size)/1048576,0) “Total for the Day in MB” FROM gv$archived_log GROUP BY TRUNC(completion_time) ORDER BY…
Read MoreAdding a Datafile to Temp Tablespace
Query to see Current Temp Datafiles State select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB from dba_temp_files d, v$tempfile v where d.FILE_ID = v.FILE# order by d.TABLESPACE_NAME,…
Read MoreAdding a Datafile to Tablespace
Query to see Current Datafiles State select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB from dba_data_files d, v$datafile v where d.FILE_ID = v.FILE# order by d.TABLESPACE_NAME, d.FILE_NAME;…
Read MoreQuery To Check Tablespace Allocated Size and Free Size in GB
Select b.tablespace_name, tbs_size SizeGB, a.free_space FreeGB from (select tablespace_name, round(sum(bytes)/1024/1024/1024,1) as free_space from dba_free_space group by tablespace_name UNION select tablespace_name, round((free_space)/1024/1024/1024,1) as free_space from …
Read MoreOPATCH_JAVA_ERROR : An exception of type “OPatchException” has occurred:
ERROR: $opatch lsinventory OPATCH_JAVA_ERROR : An exception of type “OPatchException” has occurred: Can not get a list of inventory on this home. ERROR: OPatch failed because of Inventory problem. Solution:…
Read Moreadcfgclone issue – ORA-28040: No matching authentication protocol
ERROR: perl adcfgclone.pl appsTier : RC-40201: Unable to connect to Database 12c Database: perl adcfgclone.pl appsTier : RC-40201: Unable to connect to Database CloneContext_***.log Shows the Message: Exception occurred: java.sql.SQLException:…
Read More