expdp / impdp

Some expdp impdp commands.
Quotes in ‘/as sysdba’ needs escape slashes …

/m001/oradata/EXPORT>expdp \'/ as sysdba\' schemas=owner1,owner2,owner3 directory=EXP_DIR2 dumpfile=<filename> logfile=<filename>
Read more →



export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORACLE_SID=sidje0;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql
export ORACLE_SID=sidje1;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql
export ORACLE_SID=sidje2;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql
Read more →

monitor progress of long running job with dbms_application_info.set_session_longops

Investigating a major performance issue with a PL/SQL loop running for more then 12 hours. Added dbms_application_info.set_session_longops to have entry in v$session_longnops. …


	-- @what: display elapsed msecs
	-- @link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2389164927483
	n_starttime NUMBER;
	n_endtime   NUMBER;
	n_elapsed   NUMBER;
	d_date			DATE   := SYSDATE;

	-- @what: dbms_application_info.set_session_longops
	-- @link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1099233454171#18173358903352
	l_nohint 		NUMBER DEFAULT dbms_application_info.set_session_longops_nohint;
	l_rindex 		NUMBER DEFAULT l_nohint;

  n_slno      NUMBER;
  n_totalwork NUMBER := 847; -- init total work, number of records in loop recordset is 847
  n_sofar     NUMBER := 0;


  FOR i IN
          SELECT  *********.DBKey  FloorDBKey,
                  *********.Value3 StoreVersionID
          FROM    *********,
          WHERE   *********.DBParentStoreKey = *********.DBKey
          AND     *********.DBKey = *********.DBParentFloorplanKey
          AND     *********.DBStatus = 1
          AND     *********.DBStatus = 1
Read more →

a winner, flashback query; AS OF TIMESTAMP

Recovering data from a malicious SQL update carried out on a production database. Instead of restoring the whole database to recover some lost data I gave flashback query a try and even thougg the ‘undo_retention’ parameter was set to only 5 minutes oracle recovered the data from the table from about 18 hours before. …

SQL Statements below recover the data from a timestamp just before the offending SQL update was carried out. I saved the data to a _BAK table. …

From: Visser, Remi
Sent: Wednesday, September 22, 2010 3:37 PM
To: ********
Subject: recoverd *********** table using oracle flashback query …

Importance: High …

Hi **** …

I was able to recover the table using flashback query. See below. …

Please check the data in the table ********_BAK it contains the data in ******** as of 2010-09-22 09:00:00. The offensing update was carried out at 9:10 hours. …

When the data is OK I can merge the data from the _BAK table to the original production table. …

SQL> create table ********_BAK
  2  as
  4     TO_TIMESTAMP('2010-09-22 09:00:00', 'YYYY-MM-DD HH:MI:SS')
  5  ;
Table created.

SQL> create public synonym ********_BAK for ********.********_BAK;
Synonym created.

SQL> grant select on ********.********_BAK to public;
Grant succeeded.

Dutch people, notice the schema owner name ‘BAGR.********’, Hello my name is ‘BAGR’, how nice… …

Read more →

Purging SYSAUX tablespace (Purging AWR reports)

Dump below is of a session clearing the sysaux tablespace. It turned out that something went wrong with the automatic AWR gathering/purging and the sysaux tablespace became very large. …

Thanks to this blog I found script below wich displays the various sysaux components and their sizes. …

SQL> col "Space (M)" for 999,999.99  
  2    occupant_name,  
  3    round( space_usage_kbytes/1024) "Space (M)",  
  4    schema_name, 
  5    move_procedure
  6  FROM 
  7    v$sysaux_occupants  
  8  ORDER BY 
  9    1  
 10  /
-------------------- ----------- -------------------- ----------------------------------------
AO                          1.00 SYS                  DBMS_AW.MOVE_AWMETA
EM                           .00 SYSMAN               emd_maintenance.move_em_tblspc
JOB_SCHEDULER               1.00 SYS
Read more →

Manually triggering Metric Collection (clearing -tablespace- Alerts)

Manually triggering metric collection in Oracle Enterprise Manager (GRID control) …

Here’s the scenario: …

You have an Alert that your tablespace is ‘full’,
You enlarge the tablespace,
The ‘Tablespace Full Alert’ is still in your dashbord for the next 24 (x) hours since you’ve configured this metric to be collected once every 24 (x) hours,
You want however to ‘remove’ (clear) this Alert. In order to achive this you will have to to re-evaluate this metric collection … …

Here is how you do it (for anything storage related on your database server): …

$ emctl control agent runCollection <hostname>:host host_storage

See the Oracle docs, 2.7.6 Reevaluating Metric Collections. It shows you how to retrieve the correct collection name for any metric. …

You will find yourself grep-ing through ‘$ORACLE_HOME/sysman/admin/metadata/*xml’ and ‘$ORACLE_HOME/sysman/admin/default_collection/*xml’ … …

Read more →

Increase idle time for OMS frontend sessions

Some additional configuration directives. …

To increase the idle time for the OMS frontend sessions edit the ‘oracle.sysman.eml.maxInactiveTime’ configuration parameter your Oracle Management Server’s emoms.properties file. The parameter value unit is in minutes and the default is 15 minutes which is a bit short I suppose. I have set it to 4 hours as you can see in the example below. …

$ cat $ORACLE_HOME/sysman/config/emoms.properties | grep maxInactiveTime

For more info see the ‘Enterprise Manager Advanced Configuration‘ book …

Read more →

Batch SQL statements in parallel (multiple) sessions

So it was decided ‘this batch’ had to be done ‘this weekend’. Thousands of SQL statements some of them would take minutes others hours… And MAKE SURE it’s finished monday. …

I hate babysitting multiple sessions having to create multiple SQL Scripts to spread the workload (it’s on a 20+ CPUS’s database server) So I wrote this PL/SQL ‘application’ in a hurry to enable starting multiple databases sessions in nohup, each session picks up the next SQL statement ‘available’ (WHERE endtime IS NULL). It will save me some sleep. …

Setup The table holding my SQL statements
 ( batchid              VARCHAR2(64)    NOT NULL
 , sqlid                INTEGER         NOT NULL UNIQUE
 , sqltext              varchar2(4000)  NOT NULL
 , starttime            DATE
 , endtime              DATE
 , sqlcode              INTEGER
 , sqlerrm              VARCHAR2(256)
 , error_stack          VARCHAR2(4000)
 , error_backtrace      VARCHAR2(4000)

would be nicer …

Read more →

Really delete (‘corrupt’) Agent from OMS repository

Here’s how to overcome ORA-20206 in Oracle GRID environment, agent status unclear… …


Removed (Windows) Agent from Grid. (With SQL Server Management plugin)
Tried to restart this SAME agent, errors below occur. …

# $ORACLE_HOME/sysman/log/emagent.log contains

2008-01-17 14:13:52 Thread-4032 EMAgent started successfully (00702)
2008-01-17 14:14:21 Thread-2720 <Upload Manager> Unsuccessful Upload attempts for XML file exceeds specified limit=3, 
      Agent will shutdown (00851)
2008-01-17 14:14:21 Thread-4032 EMAgent abnormal terminating (00704)
# $ORACLE_HOME/sysman/log/emdctl.trc contains

2008-01-17 14:13:32 Thread-752 WARN  http: snmehl_connect: connect failed to (<agent_hostname>:3872): 
      No connection could be made because the target machine actively refused it.
 (error = 10061)
2008-01-17 14:13:32 Thread-752 ERROR main: nmectla_agentctl: Error connecting to 
      https://<agent_hostname>:3872/emd/main/. Returning status code 1
2008-01-17 14:14:23 Thread-172 WARN  http: snmehl_connect: connect failed to 
      No connection could be made because the target machine actively refused it. (error = 10061)
2008-01-17 14:14:23 Thread-172 ERROR main: nmectla_agentctl: Error connecting to 
      https://<agent_hostname>:3872/emd/main/. Returning status code 1

# $ORACLE_HOME/sysman/log/emagent.trc contains

2008-01-17 14:14:18 Thread-192 WARN  TargetManager: Regenerating all Metadata
2008-01-17 14:14:18 Thread-192 DEBUG TargetManager: In getTypeAndNameForHostTarget
2008-01-17 14:14:18 Thread-192 DEBUG TargetManager: getTypeAndNameForHostTarget found host, <agent_hostname>
2008-01-17 14:14:18 Thread-192 DEBUG TargetManager: In getTypeAndNameForHostTarget
2008-01-17 14:14:18 Thread-192 DEBUG TargetManager: getTypeAndNameForHostTarget found host, <agent_hostname>
2008-01-17 14:14:18 Thread-192 WARN  collector: Regenerating all DefaultColls
2008-01-17 14:14:18 Thread-192 ERROR upload: Failed to upload file A0000001.xml, ret = -6
2008-01-17 14:14:19 Thread-192 WARN  upload: FxferSend: received fatal error in
Read more →

Clearing Alerts in Oracle GRID Control

Here’s how to clear alerts from the Oracle GRID manually. …

For some obscure reason Oracle is very reluctant in providing the information that is described here below. Search Metalink and you will find no entries… The information below is from dbasupport.com and helps me to clear alerts that the Agent does NOT clear for me. …

SQL>  @desc sysman.em_severity
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_TARGET_GUID                  RAW                     IN
 P_METRIC_GUID                  RAW                     IN
 P_KEY_VALUE                    VARCHAR2                IN

Generate SQL to remove alerts from specific targets
select t.target_name
,      t.target_type
,      collection_timestamp
,      message
Read more →

invalid objects after clean ctxsys installation (oracle text)

The following objects are invalid after a clean ctxsys install. …

Invalid objects
SQL> @oj-in
OWNER                  OBJECT_NAME                      OBJECT_TYPE     CREATED    LAST_DDL_TIME TIMESTAMP           STATUS
---------------------- -------------------------------- --------------- ---------- ------------- ------------------- -------
CTXSYS                 DBMS_DATA_MINING_DUMMY           PACKAGE         08-11-2007 08-11-2007    2007-11-08:16:26:32 INVALID
                       DBMS_DATA_MINING_DUMMY           PACKAGE BODY    08-11-2007 08-11-2007    2007-11-08:16:26:32 INVALID
                       DM_CLUSTER                       TYPE            08-11-2007 09-11-2007    2007-11-08:16:26:32 INVALID
Read more →

dbverify in nohup

Suffering from corruption in your database, it is nearly just as challenging as in real life… …

The other day we suffered from a curruption in a 500+ Gigabyte database, as a sequentual dberify would take about 16+ hours I wrote this one below which starts the dbv’s in nohup in parallel. My dbv finished within the hour, system performance will drop though;) …

SQL> l
  1  select
  2  'nohup dbv' ||
  3   ' file=' || file_name ||
  4   ' blocksize=4096' ||
  5   ' logfile=' || file_id || '_' || substr( file_name, instr(file_name,'/',-1) +1) || '.log &'
  6  from
  7* dba_data_files
SQL> /

nohup dbv file=C:\ORACLE\ORADATA\DB01\DB01\USERS01.DBF blocksize=4096 logfile=4_C:\ORACLE\ORADATA\DB01\DB01\USERS01.DBF.log &
nohup dbv file=C:\ORACLE\ORADATA\DB01\DB01\SYSAUX01.DBF blocksize=4096 logfile=3_C:\ORACLE\ORADATA\DB01\DB01\SYSAUX01.DBF.log &
nohup dbv file=C:\ORACLE\ORADATA\DB01\DB01\UNDOTBS01.DBF blocksize=4096 logfile=2_C:\ORACLE\ORADATA\DB01\DB01\UNDOTBS01.DBF.log &
nohup dbv file=C:\ORACLE\ORADATA\DB01\DB01\SYSTEM01.DBF blocksize=4096 logfile=1_C:\ORACLE\ORADATA\DB01\DB01\SYSTEM01.DBF.log &
nohup dbv file=C:\ORACLE\ORADATA\DB01\DB01\EXAMPLE01.DBF blocksize=4096 logfile=5_C:\ORACLE\ORADATA\DB01\DB01\EXAMPLE01.DBF.log &
nohup dbv file=C:\ORACLE\ORADATA\DB01\DB01\P1PERG001.DBF blocksize=4096 logfile=7_C:\ORACLE\ORADATA\DB01\DB01\P1PERG001.DBF.log &
nohup dbv file=C:\ORACLE\ORADATA\DB01\DB01\P1PERG002.DBF blocksize=4096 logfile=8_C:\ORACLE\ORADATA\DB01\DB01\P1PERG002.DBF.log &

Read more →

Oracle Advisor – DBMS_ADVISOR

Some ADDM dumps. …

Remove all advisory reports (ADM).
SQL> select 'exec dbms_advisor.delete_task(''' || TASK_NAME || ''' )' from dba_advisor_tasks;

exec dbms_advisor.delete_task('SYS_AUTO_SPCADV_1300592007' )
exec dbms_advisor.delete_task('SYS_AUTO_SPCADV_2700592007' )
exec dbms_advisor.delete_task('SYS_AUTO_SPCADV_2220592007' )
exec dbms_advisor.delete_task('ADDM:1488676071_1_33108' )
exec dbms_advisor.delete_task('ADDM:1488676071_1_33113' )

Read more →

new explain plan options (dbms_xplan)

I still need to upgrade to 10g … …

I still tend to use this old fashioned ‘set autot trace exp’
SQL> set autotrace trace exp
SQL> select * from dual;

Execution Plan
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=2)
   1    0   TABLE ACCESS (FULL) OF 'DUAL' (TABLE) (Cost=2 Card=1 Bytes

While in 10g the explain plan is pretty awseome – dbms_xplan package
SQL> explain plan for select * from dual;


SQL> select * from table(dbms_xplan.display());
Plan hash value: 3543395131

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |

8 rows selected.

Read more →

delete large number of records with intermediate commits

Have to delete a large number of records and running out of undo tablespace ‘ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDO’? Script below does a delete with intermediate commits ever ‘x’ records. …


  -- commit every 'i_commit' rows
  i_commit   pls_integer := 1000;

  -- row counter, 'i_rowcount' rows deleted
  i_rowcount pls_integer := 0;


  -- define 'infinite' loop

    -- delete 'i_commit' rows
    delete from obj
    where rownum <= i_commit;

    i_rowcount := i_rowcount + sql%rowcount;

    -- now it's time to exit the loop
    if sql%rowcount = 0 then
    end if;


  end loop;

  -- feedback
  dbms_output.put_line( trim( to_char( i_rowcount, '999999999999')) ||  ' rows deleted') ;


SQL> create table obj as select * from dba_objects;

Table created.

SQL> -- get current commit count for this session
SQL> select  s.*, n.name
  2  from  v$mystat s left outer join v$statname n
  3  on  s.statistic# = n.statistic#
  4  where   n.name = 'user commits'
  5  /

------- ---------- ---------- ----------------------------------------------------------
    130          4     
Read more →

Invalidated synonyms

In Oracle 10G synonyms are marked invalid when you perform DDL on the referenced object. Luckily the synonym is marked valid again when you simply touch the invalidated synonym. The status of the synonym becomes valid again and the DBA_OBJECTS.LAST_DDL_TIME and DBA_OBJECTS.TIMESTAMP columns reflect the time the synonym was validated again. Listing below shows an example for a create or replace of a view with an associated synonym. …

SQL> create view v_demo as select * from dual;

View created.

SQL> create synonym s_demo for remi.v_demo;

Synonym created.

SQL> select object_name, object_type, status
  2    from all_objects
  3   where object_name in ('V_DEMO', 'S_DEMO');
OBJECT_NAME                      OBJECT_TYPE    STATUS
-------------------------------- -------------- ---------------
S_DEMO                           SYNONYM        VALID
V_DEMO                           VIEW           VALID

SQL> create or replace view v_demo as select * from dual;

View created.

SQL> select object_name,
  2         object_type,
  3         status,
Read more →

overcome SP2-0734 when using purge command

Issue new Oracle 10G ‘purge‘ command results in ‘SP2-0734: unknown command beginning “purge user…” – rest of line ignored.’ …

Purge Command in pre 10G SQL*Plus release

Whenever you want to issue the ‘purge’ command in any pre 10G SQL*Plus client you will notice that SQL*Plus denies to send your command to the server and it will raise an ‘SP2-0734’ error. You can easiliy overcome this by making use of dynamic sql (or just upgrade your client of course). …

SQL> create table demo(k int);

Table created.

SQL> drop table demo;

Table dropped.

SQL> select * from cat;
TABLE_NAME                     TABLE_TYPE
------------------------------ -----------

SQL> drop table "BIN$HE/mKBNyRajgRAADuhSWrA==$0";
drop table "BIN$HE/mKBNyRajgRAADuhSWrA==$0"
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

SQL> [strong]purge user_recyclebin;
SP2-0734: unknown command beginning "purge user..." - rest of line ignored.[/strong]

Solution is beautiful in it’s simplicity, use dynamic sql and off you go. …

Purge recycle bin with a pre 10G SQL*Plus client
SQL> begin 
  2    execute immediate 'purge user_recyclebin';
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select * from cat;

no rows selected
Read more →

To trace or not to trace

Oracle Trace reference …


Script below enables trace in another user session. It will list all three possible ways to achive this, through a) SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION, b) SYS.DBMS_SYSTEM.SET_EV and c) ORADEBUG. …

col set_trace for a136 heading "ENABLE TRACE IN ANY SESSION"

 'EXEC sys.dbms_system.set_bool_param_in_session -
 ( sid => ' || s.sid || ', serial# => ' || s.serial# || ', parnam => ''timed_statistics'', bval => true);' 
 || chr&#40;10) ||
 'EXEC sys.dbms_system.set_int_param_in_session - 
 ( sid => ' || s.sid || ', serial# => ' || s.serial# || ', parnam => ''max_dump_file_size'', intval  => 2147483647);' 
 || chr&#40;10) || chr&#40;10) ||
 'EXEC sys.dbms_support.start_trace_in_session - 
 ( sid => ' || s.sid || ', serial => ' || s.serial# || ', waits => true, binds => true);' 
 || chr&#40;10) ||
 'EXEC sys.dbms_support.stop_trace_in_session - 
 ( sid => ' || s.sid || ', serial => ' || s.serial# || ');' 
 || chr&#40;10) || chr&#40;10) ||
 'EXEC sys.dbms_system.set_sql_trace_in_session - 
 ( sid => ' || s.sid || ', serial# => ' || s.serial# || ', sql_trace => TRUE); -- !! No waits and bind data'
 || chr&#40;10) ||
 'EXEC sys.dbms_system.set_ev - 
 ( si => ' || s.sid || ', se => ' || s.serial# || ', ev => 10046, LE => 12, nm => '''');'
 || chr&#40;10) || chr&#40;10) ||
 'ORADEBUG SETORAPID ' || p.pid || ' | ORADEBUG SETOSPID ' || p.spid
 || chr&#40;10) ||
  from v$session s, v$process p
Read more →