Oracle SQL Cheatsheet

Check Max Memory

• Issue:

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

• Check memory:

select sum(pga_max_mem)/1024/1024 "TOTAL MAX PGA (MB)"from v$process;

• Check PGA limit:

show parameter pga_aggregate_limit;

Check FS Size

select * from dba_segments;
select * from dba_data_files;
select * from dba_free_space;

-- IN GB:
select sum(bytes/(1024*10204*1024)) from dba_segments;
select sum(bytes/(1024*10204*1024)) from dba_data_files;
select sum(bytes/(1024*10204*1024)) from dba_free_space;

Check user password expiry

SELECT * FROM USER_USERS;

Check number of sessions

SELECT  COUNT(*) FROM v$session