Each Schema's Used Space

To check space used by each user schema, you can use the following Query.

/* Space used by each Schema */
select substr(owner,1,16) owner, substr(tablespace_name,1,26) tablespace_name,substr(segment_type,1,10) segment_type,sum(bytes/(1024*1024)) Mbytes,
sum(blocks) blocks from sys.dba_extents
where owner not in ('SYS','SYSTEM','SYSADMIN','SYSMAN','SH','DBSNMP','CTXSYS','EXFSYS','WMSYS','XDB','TSMSYS','MDSYS','OLAPSYS','OUTLN',
'ORDSYS','PM','OE','SCOTT','IX','HR','DMSYS')
group by substr(owner,1,16),substr(tablespace_name,1,26),substr(segment_type,1,10)
order by 1,2,3

Listener Password

The oracle listener can be the object of attacks from someone willing to take control of the database.

Putting a password on the listener is easy.

C:\>LSNRCTL

LSNRCTL>status

If you got some instances described then this is your current listener. The default name is listener and a potential hacker is well aware of that.

It’s good to create the listener with a different name.

If you don’t know your listener’s name, go to the services and look for OracleListenerSomelistenername. Your listener name is Somelistenername.

LSNRCTL>change_password Somelistenername

old password: There was nothing so we just press enter.

new password: YourNewPassword

retype new password: YourNewPassword

LSNRCTL>save_config

The save_config command will add the password encrypted in the listener.ora file located in ORACLE_HOME\NETWORK\Admin

If that command haven’t been performed then no password have been added.

How to check expdp progress

V$SESSION_LONGOPS :This view helps determine Data Pump export progress indicator through the MESSAGE column.

select t.username,t.START_TIME,t.LAST_UPDATE_TIME,t.TIME_REMAINING,t.ELAPSED_SECONDS,
t.opname,t.target_desc,t.sofar,t.totalwork,t.message from V$SESSION_LONGOPS t where/* t.USERNAME = 'user_name' and*/ t.TARGET_DESC = 'EXPORT'

Database Hanging due to Row lock contention

To check for the sessions waiting, use the query

select sid, username, event, blocking_session,
seconds_in_wait, wait_time
from v$session where state in ('WAITING')
and wait_class != 'Idle';

To check for the corresponding sql statement relating the waiting session

select sid, sql_text
from v$session s, v$sql q
where sid in (254,270)
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id);


Wait event Class
select wait_class_id, wait_class,
total_waits, time_waited
from v$session_wait_class
where sid = 270;

From the above query find the highest wait class and find the cause of the wait by using the query below.

select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and wait_class_id = xyz; -- Where xyz is the highest wait_class_id from the previous query