Find all tablespaces, indexes

References:

Dynamic views: v$

http://www.dba-oracle.com/menu_v$_views_list.htm

All user tables:

http://www.techonthenet.com/oracle/sys_tables/

All user/all/dba tables:

http://www.dba-oracle.com/concepts/data_dictionary.htm

-- Check all available partitions in database

SQL> select tablespace_name from user_tablespaces;

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

6 rows selected.

--Check all indexes in Database

SQL> select index_name ,table_name from user_indexes;

T_INV_IND_IDX1                 T_INV_IND

T_INV_IND_IDX2                 T_INV_IND

TRANS_EMP_IDX1                 TRANS_EMP

T1_IDX                         T1

PK_EMP                         EMP

PK_DEPT                        DEPT

6 rows selected.

--Check all user objects in Database

SQL> select OBJECT_NAME,OBJECT_TYPE from user_objects where upper(object_type) i

n ('TABLE','PROCEDURE','TRIGGER','FUNCTION');

--for all v$ table , we need to login with system as login

For all active (= connected) users,

select user from v$session ;

--For all user accounts

SQL> select distinct username from dba_users;

--Find out object text like procedure code in DB

SQL> select text from ALL_SOURCE where lower(name)='my_create';

PROCEDURE my_create AS

v_maxtime usermeta.instime%TYPE;

v_tbname usermeta.uname%TYPE;

v_Sqlstring VARCHAR2(200);

BEGIN

SELECT MAX(instime) INTO v_maxtime FROM usermeta;

SELECT uname INTO v_tbname FROM usermeta WHERE instime=v_maxtime;

DBMS_OUTPUT.PUT_LINE('Table Name:  '||v_tbname);

v_SqlString:='CREATE TABLE ' || v_tbname || '(' || v_tbname || 'Date TIMESTAMP,

'

||v_tbname||'Values NUMBER)';

DBMS_OUTPUT.PUT_LINE('DDL Query:  '||v_sqlstring);

EXECUTE IMMEDIATE v_sqlstring;

END;

13 rows selected.

--So how does one find out what all of the dynamic performance views are within Oracle 11g?

SELECT
   NAME,
    TYPE
FROM
   V$FIXED_TABLE
WHERE
    NAME LIKE 'V$%';

Performance Tuning Oracle 11g

Performance tuning is a complex task that daunts many Oracle DBAs. It is as much an art as a science. While GUI tools such as Oracle Enterprise Manager and Quest TOAD provide a nice slick graphical interface, to dig into the internal nuts and bolts of database performance, the serious Oracle performance analyst relies on reports generated by v$ dynamic performance views from within the Oracle 11g data dictionary. The following v$ views provide insight into database tuning for Oracle 11g.

Wait Events for 11g

v$session
v$waitclassmetric
v$waitclassmetric_history
v$waitstat
v$wait_chains

Oracle 11g Concurrency and SQL Tuning

v$lock
v$sql
v$sqlarea
v$sesstat
v$mystat
v$sess_io
v$sysstat
v$statname
v$osstat
v$active_session_history
v$active_sess_pool_mth
v$session_wait
v$session_wait_class
v$system_wait_class
v$transaction
v$locked_object
v$latch
v$latch_children
v$latch_parent
v$latchname
v$latchholder
v$latch_misses
v$enqueue_lock
v$transaction_enqueue
v$sys_optimizer_env
v$ses_optimizer_env
v$sql_optimizer_env
v$sql_plan
v$sql_plan_statistics
v$sql_plan_statistics_all


Oracle 11g Memory Tuning

v$sga
v$sgastat
v$sgainfo
v$sga_current_resize_ops
v$sga_resize_ops
v$sga_dynamic_components
v$sga_dynamic_free_memory
v$pgastat
v$sql_workarea_histogram
v$pga_target_advice_histogram                                              
v$pga_target_advice
v$memory_current_resize_ops
v$memory_resize_ops
v$memory_dynamic_components
v$library_cache_memory
v$shared_pool_advice
v$java_library_cache_memory
v$java_pool_advice
v$streams_pool_advice

Thank you,

-Anand