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.



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

Flash Back until a specified time

This article will help you recover a table upto a specified time. This Procedure was tested on Oracle 10g Release 2 Firstly create a test table which will be used for testing

Create table Emp_new as select * from emp where 1=2;

Insert some values and issue a commit.
Now select the systimestamp from the database.

Select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
09-Nov-09 07.16.47.906882 PM +08:00

Now delete data from the Emp_new table and commit it.

Delete from emp_new;
Commit;

Now select the systimestamp again from the database.
select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
09-Nov-09 07.23.47.906882 PM +08:00

Now you know that table was truncated at 7:16. So we will perform recovery / Flashback till 3:11

INSERT INTO emp_new
(SELECT * FROM emp_new AS OF TIMESTAMP TO_TIMESTAMP('09-NOV-09 19:16:00','DD-MON-YY HH24: MI: SS'))

Commit;

Now you can see the data has been restored in the table.

SQL> Select * from emp_test;