ORA-39095: Dump file space has been exhausted

ORA-39095: Dump file space has been exhausted

Encountered this error when taking dump backup which exceeds more than 99 files
$ORACLE_HOME/bin/expdp dcs_dev/*****@fdbbp directory=dcsdevdir dumpfile=dcsdev_$(date +"%d-%m-%Y" )_fdbbp%U.dmp logfile=dcsdev_$(date +"%d-%m-%Y")_fdbbp.log filesize=600M


Solution : 1
We can up-size the filesize, to bring down the number of files generated
eg
$ORACLE_HOME/bin/expdp dcs_dev/******@fdbbp directory=dcsdevdir dumpfile=dcsdev_$(date +"%d-%m-%Y" )_fdbbp%U.dmp logfile=dcsdev_$(date +"%d-%m-%Y")_fdbbp.log filesize=1024M

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

How to Calculate Table size

TABLE not partitioned:
/* Size of each table in a user schema */

Connect to the user and use the following query.

Table not partitioned

select segment_name, t.tablespace_name, bytes / 1024 / 1024 MB from user_segments t
Where t.segment_type = 'TABLE' order by 3 desc


Alternatively to check what are all the objects stored in a tablespace.

select segment_name, t.tablespace_name, t.segment_type,bytes / 1024 / 1024 MB from user_segments t Where t.tablespace_name = 'DCSTAB' order by 4 desc


For a Partitioned table


select segment_name, partition_name, bytes / 1024 / 1024 MB
from user_segments Where segment_type = 'TABLE PARTITION' and Segment_Name= and partition_name = ;

TABLE partitioned:
select segment_name, sum(bytes / 1024 / 1024) MB
from user_segments Where segment_type = 'TABLE PARTITION'
and Segment_Name= Group by segment_name;

Current Runing SQL statements

To have a script that displays all of the currently executing SQL statements - use the following Query in case if you are experiencing performance problem.

--Currently Executing SQL statements.
select u.sid,substr(u.username,1,12) user_name,s.sql_text from v$sql s, v$session u
where s.hash_value = u.sql_hash_value and sql_text not like '%from v$sql s, v$session u%'
order by u.sid


The Sample output is given below


SID USER_NAME SQL_TEXT
7 184 PROC_DEV SELECT A.* FROM MAIN_HEADER A INNER JOIN
VIEW_HEADER_N2 B ON A.MESSAGE_NUMBER =
B.MESSAGE_NUMBER WHERE A.EPROC_SOURCE = :B1 AND
B.SALES_TOTAL_AMOUNT != 0 AND B.PO_CLOSED_DATE IS
NULL AND A.MESSAGE_NUMBER IN (SELECT DISTINCT
DT.MESSAGE_NUMBER FROM VIEW_DETAIL_N2 DT INNER JOIN
VIEW_DELIVERY_N2 DL ON DT.MESSAGE_NUMBER =
DL.MESSAGE_NUMBER) ORDER BY A.MESSAGE_NUMBER

8 184 PROC_DEV SELECT A.* FROM MAIN_HEADER A INNER JOIN
VIEW_HEADER_N2 B ON A.MESSAGE_NUMBER =
B.MESSAGE_NUMBER WHERE A.EPROC_SOURCE = :B1 AND
B.SALES_TOTAL_AMOUNT != 0 AND B.PO_CLOSED_DATE IS
NULL AND A.MESSAGE_NUMBER IN (SELECT DISTINCT
DT.MESSAGE_NUMBER FROM VIEW_DETAIL_N2 DT INNER JOIN
VIEW_DELIVERY_N2 DL ON DT.MESSAGE_NUMBER =
DL.MESSAGE_NUMBER) ORDER BY A.MESSAGE_NUMBER


If you could tune these statement most of the Performance related problems will be solved.



===================================================