Create Partition in an Old Table Oracle

You can partition a non-partitioned table three different ways:

A) export/import method
B) Insert with a subquery method
C) Partition exchange method

Either of these 3 methods will create a partitioned table from an existing non-partitioned table.
Continue reading

Start/Stop Enterprise Manager in Oracle

Those are commands to check out the Status or Start/Stop Enterprise Manager in Oracle Database.


bash-3.2$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://srvip:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /oracle/app/11g/db_1/srvip_ababil/sysman/log

Continue reading

Enable/Disable Table level Audit Option in Oracle

Use this sql command to check out audit option is enabled on which Tables.


SELECT OWNER, OBJECT_NAME FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'SIBL';

OWNER                          OBJECT_NAME                   
------------------------------ ------------------------------
SIBL                           ACCOUNT                       
SIBL                           ACCOUNTTYPE                   
SIBL                           APPCONFIGURATION              
SIBL                           DEP_TXN_ARCH_OLD              
SIBL                           FINANCIALPRODUCT              
SIBL                           INLANDREMITTANCE              
SIBL                           INVMSTR                       
SIBL                           REMITTANCEPRODUCT             
SIBL                           SSP_ACCOUNT                   
SIBL                           SSP_MONTHLYPRODUCTINFO        
SIBL                           SSP_PRODUCT                   
SIBL                           TFS_ACCOUNT                   
SIBL                           TRANSACTIONRECORD  

Continue reading

Database Audit Option in Oracle

Use this sql command to checkout if audit_trail is enable or not.


SQL> show parameter audit;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /oracle/app2/admin/testdbdp/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE


Execute this following sql and bounce database to enable audit_trail.


ALTER SYSTEM SET AUDIT_TRAIL=DB, EXTENDED SCOPE=SPFILE;


Execute this following sql and bounce database to disable audit_trail.


ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=SPFILE;

Thanks
A Rahim Khan

Cancel datapump Jobs

First of all, use hand this handy query to checkout Datapump Job status.


SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE      
---------- -------------------- ---------- ---------- ------------
SYSTEM     EXP_FULL             EXPORT     FULL       NOT RUNNING

Continue reading

Automatic Block Media Recovery in Action on Video

Uwe Hesse

The little video below shows the 11gR2 New Feature Automatic Block Media Recovery in Action. I have already introduced the feature in this post, but some things are just more impressive when you actually see it happening, don’t you agree?

View original post

Delete Archive Log using rman

This scenario is like you don’t have any space left in db_recovery_file_dest or any other archive destination and you need to delete some archive logs to bring the database up and running.

Don’t delete archive logs manually, instead use rman to do this job.


rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Sep 30 10:02:33 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ABABIL (DBID=1225231090)

RMAN>delete noprompt archivelog until time 'sysdate -5';

Thanks
A Rahim Khan