Oracle Data Dict

Check Executing SQL Statements
select /*+ T1A */ * from hr.employees;
select sql_id, executions, loads, sql_text from v$sql
where sql_text like '%T1A%';

SQL Explain Plan
select * from table (dbms_xplan.display_cursor('6npnynbfn50vs'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6npnynbfn50vs, child number 0
-------------------------------------
select /*+ T1A */ * from hr.employees
Plan hash value: 1445457117
-------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------
0 SELECT STATEMENT 3 (100)
1 TABLE ACCESS FULL EMPLOYEES 107 7276 3 (0) 00:00:01
-------------------------------------------------------------------------------
13 rows selected
Execution plan is case sensitive on SQL statements
Use Hash to store
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;

Redo Log
select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- -------- ---------------- ---------------------- -------------------------
1 1 8 52428800 2 NO CURRENT 596545 25-JAN-09
2 1 6 52428800 2 NO INACTIVE 543104 24-JAN-09
3 1 7 52428800 2 NO INACTIVE 573428 25-JAN-09
alter system switch logfile;
alter system switch succeeded.
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------------------- ---------------------- ---------------------- ---------------------- ---------------------- -------- ---------------- ---------------------- -------------------------
1 1 8 52428800 2 NO ACTIVE 596545 25-JAN-09
2 1 9 52428800 2 NO CURRENT 607813 26-JAN-09
3 1 7 52428800 2 NO INACTIVE 573428 25-JAN-09

Kill Sessions

Check Data Files
1* select file_name, tablespace_name, bytes, autoextensible from dba_data_files
SYS> /
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_4qm78bfj_.dbf
USERS 5242880 YES
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_4qm78b7x_.dbf
SYSAUX 262144000 YES
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_4qm78bd0_.dbf
UNDOTBS1 36700160 YES
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_4qm78b7d_.dbf
SYSTEM 503316480 YES
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_4qm7c00l_.dbf
EXAMPLE 104857600 YES
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs2_4shhwf2w_.dbf
UNDOTBS2 20971520 NO
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_userstbs_4shj5fyy_.dbf
USERSTBS2 20971520 NO
/u01/app/oracle/oradata/ORCL/datafile/inventorytbs1
INVENTORY 5242880 NO
8 rows selected.
SYS>

Table Free Space
1* select table_name, pct_free, chain_cnt from user_tables where table_name like 'A%'
SYS> /
ATTRCOL$ 0 0
ARGUMENT$ 10 0
ACCESS$ 10 0
AUDIT$ 10 0
ATTRIBUTE$ 0 0
ASSOCIATION$ 10 0
APPROLE$ 10 0
AUD$ 10 0
AUX_STATS$ 10 0
AW$ 10 0
AW_OBJ$ 10 0

Check if FlashBack is ON
1* select name, current_scn, flashback_on from v$database
SYS> /
ORCL 782184 NO
SYS> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS> startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 109054196 bytes
Database Buffers 54525952 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS> alter database flashback on;
Database altered.
SYS>

Undo Transactions
select * from v$transactions

Others
select file#, checkpoint_change#, name from v$datafile
SYS> select name, open_mode from v$database;
ORCL READ WRITE
SYS> select * from v$instance;
1 orcl localhost.draco.com 10.2.0.1.0 15-FEB-09 OPEN NO 1 STOPPED ALLOWED
NO ACTIVE PRIMARY_INSTANCE NORMAL NO
1* select count(*) from v$fixed_table
SYS> /
1383
SYS> desc dict;
Name Null? Type
----------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
SYS> select segment_name, extents, blocks, bytes from user_segments where segment_name like '%INV%';
INVALIDATION_REGISTRY$ 1 8 65536
INVOICE 1 8 65536
I_INVALIDATION_REGISTRY$ 1 8 65536
INVPK 1 8 65536
SYS> select tablespace_name, extent_management, segment_space_management from dba_tablespaces;
SYSTEM LOCAL MANUAL
UNDOTBS1 LOCAL MANUAL
SYSAUX LOCAL AUTO
TEMP LOCAL MANUAL
USERS LOCAL AUTO
UNDOTBS2 LOCAL MANUAL
EXAMPLE LOCAL AUTO
TEMPTBS2 LOCAL MANUAL
USERSTBS2 LOCAL AUTO
INVENTORY LOCAL AUTO
INVOICE_PK LOCAL AUTO
11 rows selected.
SYS> select * from session_roles;
no rows selected