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;

Existing Index Needed or Not needed

/* In Oracle10g we can easily see what indexes are used, when they are used and the context where they are used. Here is a simple AWR query to plot index usage:*/

Connect as sys user and use this query to check how many times each index is used for a schema...


Method 1 : ( Oracle 10g )
select p.object_owner owners, p.object_name Obj_Name, p.operation Operation, p.options Options, count(1) Idx_Usg_Cnt
from dba_hist_sql_plan p,dba_hist_sqlstat s
where p.object_owner = 'Schema_Name' and p.operation like '%INDEX%' and p.sql_id = s.sql_id
group by p.object_owner,p.object_name,p.operation,p.options
order by 1,2,3

To list all the indexes in a schema use the following query.

select t.owner,t.table_name,t.index_name,t.last_analyzed from dba_indexes t
where t.owner = 'schema_name'



/* -- Here is another script for tracking unused indexes and showing the invocation count(How manytimes and when they are called/Used) of all indexes.
-- Best of all, this script shows the columns referenced for multi-column indexes: */

select to_char(sn.begin_interval_time,'yy-mm-dd hh24') Begin_Int_Time, p.search_columns Search_Col, count(*) Invocation_Cnt
from dba_hist_snapshot sn, dba_hist_sql_plan p, dba_hist_sqlstat st
where st.sql_id = p.sql_id and sn.snap_id = st.snap_id and lower(object_name) like /*lower('%&idxname%')*/ lower ('Idx_Name')
group by begin_interval_time,search_columns

With these queries we can find out the unused indexes and drop them off.


Method 2 : (Oracle 9i)
Connect to a user and then
alter index index_Name monitoring usage

then use the query below to check whether the index is used

select index_name mon,used from v$object_usage;

Here is a simple SQL*Plus script to track all index usage in a Oracle schemas:

set pages 999;
set heading off;
spool Idx_monitor.sql

select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes
where owner = 'Schema_name';

spool off;

@Idx_monitor.sql

Once idx_monitor.sql completes, you can check for the Index usage by

select index_name mon,used from v$object_usage;

Lock Identification

/* This script shows all users who are waiting for locked resources */

SELECT b.username, b.serial#, c.sid, c.owner object_owner, c.object, d.id1, a.sql_text SQL FROM v$sqltext a,v$session b, v$access c, v$lock d
WHERE a.address = b.sql_address AND a.hash_value = b.sql_hash_value AND b.sid = c.sid AND b.lockwait = d.kaddr AND c.owner NOT IN ('SYS','SYSTEM');

/* This script shows the users who are locking the above waiting resources. This script will also show the SQL being executed by the user. NOTE: If the locking user is no longer executing any SQL, the rows returned will be 0. The user, however, is still locking the above users since he/she has not yet committed his/her transaction -- maybe they are away from their desk?? You should call them and ask them to either commit or rollback. */

SELECT x.serial#, x.sid, x.username, y.id1, z.sql_text SQL FROM v$session x, v$lock y, v$sqltext z
WHERE y.id1 IN (SELECT distinct b.id1 FROM v$session a, v$lock b WHERE b.kaddr = a.lockwait) AND x.sid = y.sid AND z.hash_value = x.sql_hash_value AND y.request = 0


/* This script is identical to the above script EXCEPT that it does not return the SQL text being executed by the locking user. You can use it to quickly identify the locking user's serial#, sid and username if you get to a point where you will need to kill the user's session. */

SELECT x.serial#, x.sid, x.username, y.id1 FROM v$session x, v$lock y WHERE y.id1 IN (SELECT distinct b.id1 FROM v$session a, v$lock b WHERE b.kaddr = a.lockwait) AND x.sid = y.sid AND y.request = 0;

To Check the Database Version

Here is the query to check what is the Database version

Connect as sys/pwd@dbname as sysdba

select username,account_status,default_tablespace,temporary_tablespace,created
from dba_users


Sample Output

PRODUCT VERSION STATUS
NLSRTL 10.2.0.1.0 Production
Oracle Database 10g Enterprise Edition 10.2.0.1.0 Prod
PL/SQL 10.2.0.1.0 Production
TNS for Linux: 10.2.0.1.0 Production

PL/SQL Last Compiled Date/Time

To check when was the pl/sql procedure compiled.

Connect to the schema which you want and give the query as

Eg:

select to_char(LAST_DDL_TIME,'YYYY-MM-DD HH24:MI:SS') from user_objects where object_name = upper('dcs_dev');