How To Check Which Oracle Features are Enabled ?

There is a very good view DBA_FEATURE_USAGE_STATISTICS . Its function is to display information about database feature usage statistics. Oracle 10g will keep track of any of its features that are being used.   As an example, if we SELECT from any of the Oracle Diagnostic Pack and Tuning Pack views like V$ACTIVE_SESSION_HISTORY, the database will record our usage of it in this DBA view.  

Some of the information tracked are :

•  Name of the feature
•  Number of times the system has detected usage for the feature
•  First sample time the system detected usage of the feature
•  Last sample time the system detected usage of the feat

The below sql will give the detail of the oracle services which are being used.

SQL> select NAME, VERSION, DETECTED_USAGES, CURRENTLY_USED, FIRST_USAGE_DATE, LAST_USAGE_DATE from DBA_FEATURE_USAGE_STATISTICS where CURRENTLY_USED  = 'TRUE' order by 1,2;

Output : 

How to  check the Oracle Edition  : 
We can the check the oracle edition i.e; whether it is enterprise edition or standard edition by using the below command :

SQL>select * from product_component_version;

PRODUCT                                                     VERSION                 STATUS

----------------------------------                        -------------               ------------
NLSRTL                                                         11.2.0.1.0             Production
Oracle Database 11g Enterprise Edition      11.2.0.1.0             Production
PL/SQL                                                         11.2.0.1.0             Production
TNS for 32-bit Windows:                              11.2.0.1.0             Production


Enjoy    :-)

IMP-00010 not a valid export file header failed verification , IMP-00000:

While performing an exp/imp operation , i got IMP-00010 while importing the dumps .On googling i found that this error occur generally due to two reasons .

1.) File Corrupted During Transfer.
Note : FTP should be done in BINARY MODE

2.) When export dump file is higher version and import in lower version.
e.g; exp dump file is Oracle 10g and import in Oracle 9i

Export From            <---->   Import to   <----> Use Import/Export Utility
Version : 10.2.0.1.0 <----->   10.1.0.2.0   <----->   10.1.0.2.0
Version : 10.2.0.1.0 <----->   9.2              <----->   9.2
Version : 10.2.0.1.0 <----->   9.0.1           <----->   9.0.1

Note : Suppose we want to import 10.2.0.1.0 export dump file in 9.2.0 then we must use 9.2.0 export tools for export data from 10.2.0.1.0. and run CATEXP.SQL script which is located in Located : $ORACLE_HOME/rdbms/admin

Consider an example : Dumpfile exported in Oracle 11gr1  and has to be Imported  in 10gr1, 9i. Then use Oracle 11gr1 client (IMPORT) binary to export 11g dumpfile. Install oracle 11g client and connect to 10g,9i,8i server and import .

Enjoy     :-) 

Block Developers Tools on Production Database

I have come across a very good post on how to prevent users from using additional tools to connect to production database. This can also allow us to connect for specific users only. This can be done by creating the a AFTER LOGON trigger create ON DATABASE .Below is the script 

c:\> sqlplus / as sysdba

SQL> CREATE OR REPLACE TRIGGER block_tools_from_prod
          AFTER LOGON ON DATABASE
DECLARE
           v_prog sys.v_$session.program%TYPE;
BEGIN
         SELECT   program    INTO   v_prog
         FROM     sys.v_$session
         WHERE    audsid = USERENV('SESSIONID')
         AND   audsid != 0          ---- Don't Check SYS Connections
        AND  ROWNUM = 1;    ---- Parallel processes will have the same AUDSID's
      IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR   -- Toad
      UPPER(v_prog) LIKE '%SQLNAV%' OR     -- SQL Navigator
      UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
      UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects
      UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in
  THEN
     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
  END IF;
END;
/

Reference : http://psoug.org/snippet.htm/Block_TOAD_and_other_tools_516.htm


Enjoy    :-)