dba_role_privs dba_tab_privs dba_sys_privs

List user Priviliges

set echo off
set verify off
set pages 200
col granted_role form a20
col owner form a12
col table_name form a27
col privilege form a27

ACCEPT username  prompt 'Enter Username : '

spool quser_privs.log

PROMPT Roles granted to user

SELECT granted_role,admin_option,default_role
FROM dba_role_privs
WHERE grantee=UPPER('&username');

PROMPT Table Privileges granted to a user through roles

SELECT granted_role, owner, table_name, privilege
FROM ( SELECT granted_role
     FROM dba_role_privs WHERE grantee=UPPER('&username')
       UNION
       SELECT granted_role
     FROM role_role_privs
     WHERE role in (SELECT granted_role
                FROM dba_role_privs WHERE grantee=UPPER('&username')
               )
    ) roles, dba_tab_privs
WHERE granted_role=grantee;

PROMPT System Privileges assigned to a user through roles

SELECT granted_role, privilege
FROM ( SELECT granted_role
     FROM dba_role_privs WHERE grantee=UPPER('&username')
       UNION
       SELECT granted_role
     FROM role_role_privs
     WHERE role in (SELECT granted_role
                FROM dba_role_privs WHERE grantee=UPPER('&username')
               )
    ) roles, dba_sys_privs
WHERE granted_role=grantee;

PROMPT Table privileges assigned directly to a user

SELECT owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee=UPPER('&username');

PROMPT System privileges assigned directly to a user
SELECT privilege, admin_option
FROM  dba_sys_privs
WHERE grantee=UPPER('&username');

spool off



Hope this Helps! Rupam

explian plan

steps:-
1. create plan table (global temporary table) @?/rdbms/admin/catplan
2. populate plan table
   Note : 235530.1
   SQL> explain plan for
       
3. Displaying The Execution Plan
  SQL> set linesize 150 
  SQL>  select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
or
select * from  table(dbms_xplan.display('plan_table',null,'serial'));
or
select * from table(dbms_xplan.display);
or
@?/rdbms/admin/utlxpls
or 
More details can be found in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql
or
  select * from table(dbms_xplan.display(null, null));
or
select plan_table_output from table(dbms_xplan.display('plan_table',null,'advanced'));

Hope this help! Rupam

STATISTICS dbms_stats

- gather statistics – SYS , system, dictionary , fixed_objects  

 

 exec dbms_stats.gather_system_stats('interval', interval=>180);

 exec dbms_stats.gather_dictionary_stats;

 exec dbms_stats.gather_fixed_objects_stats;

 exec dbms_stats.gather_schema_stats('SYS',degree=>30,-

   estimate_percent=>100,cascade=>TRUE);

  

-- gather database statistics

   exec DBMS_STATS.gather_database_stats(estimate_percent => 15);    

        

-- gather schema statistics

exec dbms_stats.delete_schema_stats('SCOTT');

exec dbms_stats.gather_schema_stats(USER,  cascade => TRUE);

exec dbms_stats.gather_schema_stats(ownname =>'SCOTT,   options =>'GATHER',method_opt=>'FOR ALL COLUMNS SIZE AUTO',estimate_percent => dbms_stats.auto_sample_size,granularity=>'AUTO',cascade=>TRUE);

exec dbms_stats.gather_schema_stats(ownname =>'SCOTT', -

     method_opt=>'FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE -   1',estimate_percent => 15,granularity=>'ALL',cascade=>TRUE);

   

-- gather table, index  stats

exec dbms_stats.delete_table_stats(ownname =>'SCOTT',-

tabname =>'EMPLOYEE');

exec dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname =>'EMPLOYEE',method_opt=>'for all columns size auto',estimate_percent => dbms_stats.auto_sample_size,granularity=>'AUTO',cascade=>true);

 exec dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname =>'EMPLOYEE',method_opt=>'FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent => 15,cascade => TRUE );      

   with histogram on columns

     exec dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname =>'EMPLOYEE',method_opt=>'FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent => dbms_stats.auto_sample_size,granularity=>'ALL',cascade=>TRUE);

   

   with no histogram on columns

     exec dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname =>'EMPLOYEE',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent => dbms_stats.auto_sample_size,granularity=>'ALL',cascade=>TRUE);

   for skewed columns        

     execute dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname =>'EMPLOYEE',method_opt=>'for all columns size skewonly',estimate_percent => dbms_stats.auto_sample_size,granularity=>'AUTO',cascade=>TRUE);

using shell scipt

set time on

declare

cursor tab_cur IS select table_name from dba_tables where owner='SCOTT' order by table_name;

begin

  for tab_rec in tab_cur loop

    exit when tab_cur%NOTFOUND;

    dbms_stats.gather_table_stats(ownname =>'SCOTT',options=>'GATHER',tabname =>tab_rec.table_name,method_opt=>'for all columns size auto',estimate_percent => dbms_stats.auto_sample_size,granularity=>'AUTO',cascade=>true);

  end loop;

end;

/

exit

  

-- gather index stats

   exec dbms_stats.gather_index_stats( ownname=>'SCOTT',indname=>'ITEM_NDX1',-

granularity=>'ALL',estimate_percent=> 10,degree=>1);

using shell scipt
set time on
declare
cursor tab_cur IS select index_name from dba_indexes where owner='SCOTT' and last_analyzed < sysdate - 1  order by table_name;
begin
  for tab_rec in tab_cur loop
    exit when tab_cur%NOTFOUND;
    dbms_stats.gather_index_stats( ownname=>'MAXIMO',indname=>tab_rec.index_name,granularity=>'AUTO',estimate_percent=> dbms_stats.auto_sample_size,degree=>1);
end loop;
end;
/
exit

Hope this helps! Rupam

index statistics in oracle

Statistics generated include the index statistics following:

Table statistics

Number of rows

Number of blocks

Average row length

Column statistics

Number of distinct values (NDV) in column 

Number of nulls in column 

Data distribution (histogram) Index statistics 

Number of leaf blocks 

Levels 

Clustering factor System statistics

I/O performance and utilization

CPU performance and utilization

Hope this Helps! Rupam

ODI - Change Data Capture

About

The goal of Change Data Capture is to track change in the source data. When running integration interface, ODI-EE can reduce the volume of source data processed in the flow by extracting only the changed data.

Reducing the volume of source data is useful in many field such as:

Synchronization/Replication

These changes are captured by Oracle Data Integrator and transformed into events that are propagated throughout the information system.

Changes tracked by Changed Data Capture constitute data events. The ability to track these events and process them regularly in batches or in real time is key to the success of an event-driven integration architecture.

Changed Data Capture is performed by journalizing models. Journalizing a model consists of setting up the infrastructure to capture the changes (inserts, updates and deletes) made to the records of this model's datastores.

Oracle Data Integrator supports two journalizing modes:

Simple Journalizing tracks changes in individual datastores in a model.

Consistent Set Journalizing tracks changes to a group of the model's datastores, taking into account the referential integrity between these datastores. The group of datastores journalized in this mode is called a Consistent Set.

 

Hope this Helps! Rupam

diagnostic_dest, ADR_BASE_LISTENER, ADR_BASE

Changing the default location of logs and trace files in 11g

setting the diagnostic destination for 11gR2 servers to /oramisc01/oracle -

Make the following changes:

/ora01/oracle/admin/network/sqlnet.ora - added the line 'ADR_BASE=/oramisc01/oracle'

/ora01/oracle/admin/network/listener.ora - added the line 'ADR_BASE_LISTENER=/oramisc01/oracle'  

   (may not take effect until the listener is restarted or reloaded)

ASM instance - set the init value 'diagnostic_dest='/oramisc01/oracle'

Database – set the init value 'diagnostic_dest='/oramisc01/oracle'

Agent11gHome/network/admin/sqlnet.ora - added the line 'ADR_BASE=/oramisc01/oracle'

Hope this helps! Rupam