How can I know the size of my Oracle database?

How can I know the size of my Oracle database?
=====================================================


An oracle database consists of data files, redo log files, control files, temporary files.
Whenever you say the size of the database this actually means the summation of these files.

Also, if you are in archive log mode, you will generate files in your archive log
destination. Issue the following query to see where your archived redo
logs get placed:

select * from v$parameter where name = 'log_archive_dest';



overall Database Size Query will be ...


SELECT round( a.data_size
+ b.temp_size
+ c.redo_size
+ d.controlfile_size,4) "total_size in GB"
FROM (SELECT SUM (BYTES) / 1024 / 1024/ 1024 data_size
FROM dba_data_files) a,
(SELECT NVL (SUM (BYTES), 0) / 1024 / 1024/ 1024 temp_size
FROM dba_temp_files) b,
(SELECT SUM (BYTES) / 1024 / 1024/ 1024 redo_size
FROM SYS.v_$log) c,
(SELECT SUM (block_size * file_size_blks) / 1024/ 1024
/ 1024 controlfile_size
FROM v$controlfile) d



or you can see it

---its only oracle datafiles summation size

SELECT name, highwater/ 1024 / 1024/ 1024, last_value / 1024 / 1024/ 1024
FROM dba_high_water_mark_statistics where name = 'DB_SIZE';

How to see what session's are doing from V$ACTIVE_SESSION_HISTORY in oracle database

How to see what session's are doing from V$ACTIVE_SESSION_HISTORY in oracle database?
==========================================================================

V$ACTIVE_SESSION_HISTORY displays sampled session activity in the database.
It contains snapshots of active database sessions taken once a second.
A database session is considered active if it was on the CPU or was waiting for
an event that didn't belong to the Idle wait class. Refer to the V$EVENT_NAME
view for more information on wait classes.

This view contains one row for each active session per sample and returns the
latest session sample rows first. A majority of the columns describing the session
in the active session history are present in the V$SESSION view.


v$active_session_history only holds data for last half an hour, to view historical information
of more than half an hour, information will be available in DBA_HIST_ACTIVE_SESS_HISTORY

DBA_HIST_ACTIVE_SESS_HISTORY

DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active
session history of recent system activity. This view contains snapshots of V$ACTIVE_SESSION_HISTORY



SELECT sql_id, blocking_session, session_id, event
FROM v$active_session_history
where session_id=2958


/* Formatted on 2011/06/08 14:45 (Formatter Plus v4.8.8) */
SELECT sql_id, blocking_session, session_id, event
FROM dba_hist_active_sess_history
where blocking_session=4404



example:-


SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jun 8 14:54:04 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> conn eraadmin@live
Enter password:
Connected.
SQL>
SQL>
SQL> set linesize 1000
SQL>
SQL> SELECT sql_id, blocking_session, session_id, event
2 FROM v$active_session_history
3 where session_id=2958 ;

SQL_ID BLOCKING_SESSION SESSION_ID EVENT
------------- ---------------- ---------- --------------------------------------
--------------------------
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq Credit: send blkd

SQL_ID BLOCKING_SESSION SESSION_ID EVENT
------------- ---------------- ---------- --------------------------------------
--------------------------
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958 PX Deq: Table Q Get Keys
5gsdgs2syh71d 2958 latch free
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
5gsdgs2syh71d 2958
5gsdgs2syh71d 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
f97da85fghdwh 2958 PX Deq Credit: send blkd
5bd61nfc8m84n 2958

SQL_ID BLOCKING_SESSION SESSION_ID EVENT
------------- ---------------- ---------- --------------------------------------
--------------------------
5bd61nfc8m84n 2958
5bd61nfc8m84n 2958
5bd61nfc8m84n 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd
2ffckx228w8gm 2958 PX Deq Credit: send blkd

31 rows selected.


SQL>
SQL>
SQL>
SQL> select * from table(dbms_xplan.display_cursor('2ffckx228w8gm')) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID 2ffckx228w8gm, child number 0
-------------------------------------
SELECT DISTINCT b.cacmpcde || ' - ' || b.CACMPNAM,b.cacmpcde || ' - ' ||
b.CACMPNAM FROM SYRIGHTS A,SYPARMAS B WHERE A.USERCODE=:1 AND
A.COMPCODE=B.CACMPCDE ORDER BY 1

Plan hash value: 1114147443

--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 154 (100)|
|
| 1 | SORT ORDER BY | | 2580 | 87720 | 154 (2)| 00:00
:02 |
| 2 | HASH UNIQUE | | 2580 | 87720 | 153 (2)| 00:00
:02 |
| 3 | NESTED LOOPS | | 2580 | 87720 | 151 (0)| 00:00
:02 |
| 4 | TABLE ACCESS FULL| SYPARMAS | 74 | 1702 | 3 (0)| 00:00
:01 |
|* 5 | INDEX RANGE SCAN | PK_SYRIGHTS4 | 35 | 385 | 2 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
5 - access("A"."COMPCODE"="B"."CACMPCDE" AND "A"."USERCODE"=:1)


24 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('5gsdgs2syh71d')) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID 5gsdgs2syh71d, child number 0
-------------------------------------
SELECT DISTINCT ACTNUM FROM STTRPROF ORDER BY 1

Plan hash value: 240056120

--------------------------------------------------------------------------------
-----------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
-----------------------------------------------
| 0 | SELECT STATEMENT | | | | | 18877
(100)| | | | |
| 1 | PX COORDINATOR | | | | |
| | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
| 2 | PX SEND QC (ORDER) | :TQ10001 | 229K| 2690K| | 13998
(5)| 00:02:48 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT UNIQUE | | 229K| 2690K| 48M| 13998
(5)| 00:02:48 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 2293K| 26M| | 3037
(3)| 00:00:37 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 2293K| 26M| | 3037
(3)| 00:00:37 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 2293K| 26M| | 3037
(3)| 00:00:37 | Q1,00 | PCWC | |
|* 7 | INDEX FAST FULL SCAN| PK_STTRPROF | 2293K| 26M| | 3037
(3)| 00:00:37 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------
-----------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)


24 rows selected.

SQL>