Some Tips on Oracle Table space and Users

–List all users/schema
SELECT DISTINCT USERNAME FROM DBA_USERS;
SELECT * FROM DBA_USERS;
–List ONLY your tables
SELECT * FROM CAT;
SELECT * FROM TAB;
–List the Oracle Tablespaces
SELECT * from v$TABLESPACE;
–See your TABLESPACE
SELECT * FROM ALL_TABLES WHERE TABLE_NAME = ‘BL_SESSION_EVENT_HIST’;
SELECT TABLE_NAME, TABLESPACE_NAME, STATUS FROM USER_TABLES;

–find table space of all tables
SELECT * FROM dba_tables;
–List All dynamic performance view
SELECT * FROM V$FIXED_TABLE WHERE NAME LIKE ‘V$%’;

–Viewing current ALL Oracle users
SELECT SID, SERIAL#, USERNAME, OSUSER, MACHINE, STATUS
FROM V$SESSION
WHERE USERNAME IS NOT NULL;
SELECT * FROM V$SESSION WHERE USERNAME IS NOT NULL;
–Viewing current Oracle user
select user from dual;
–the name of the oracle instance
select sys_context(‘userenv’,’instance_name’) from dual;
SELECT sys_context(‘USERENV’, ‘SID’) FROM DUAL;
select sys_context(‘userenv’,’db_name’) from dual;
select ora_database_name from dual;
select * from global_name;

–Check each user usage on tablespaces.
select sysdate, a.owner username, a.tablespace_name, round(b.total_space/1024/1024,2) “Total (MB)”, round(sum(a.bytes)/1024/1024,2) “Used (MB)”, round(sum(a.bytes/b.total_space)*100,2) “% Used”
from dba_segments a, (select tablespace_name, sum(bytes) total_space
from dba_data_files
group by tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
group by a.tablespace_name, a.owner, b.total_space/1024/1024
order by a.tablespace_name, a.owner;

–Check all tablespaces usage
select t1.tsp_name, sysdate,
round(t1.total_space/1024/1024,2) “Total (MB)”,
round((t1.total_space-t2.free_bytes)/1024/1024,2) “Used (MB)”,
round(t2.free_bytes/1024/1024,2) “Free (MB)”,
round(t2.free_bytes/t1.total_space*100,2) “% Free”,
decode(greatest(round(t2.free_bytes/t1.total_space*100,2),20),20,’*’,’ ‘) D,
decode(greatest(round(t2.free_bytes/t1.total_space*100,2),15),15,’*’,’ ‘) E
from (select tablespace_name tsp_name, sum(bytes) total_space
from sys.dba_data_files
group by tablespace_name) t1,
(select tablespace_name tsp_name, sum(bytes) free_bytes
from sys.dba_free_space
group by tablespace_name) t2
where t1.tsp_name = t2.tsp_name
order by tsp_name;

Enjoy!

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: