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 MoreORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded – It’s typical error encountered in Oracle database. It happens when too much cursors are opened on an oracle instance. Maximum number of opened cursors…
Read MoreORA-00845: MEMORY_TARGET not supported on this system
Modify the entry in “/etc/fstab” to have persistent settings between reboot of your machine. Here shared segments are set to 3500M. tmpfs /dev/shm tmpfs size=3500M 0 0 remount it mount…
Read More