Why Oracle May Really Be Doomed This Time

I was laying awake early this morning thinking about Oracle. …

O my …

Typically I think big public companies are inept, but I would not count Oracle out. Ellison has long shown he viscerally gets where the stock market, the customer and …

Read more →

Generate scp datafiles script with regular expression

Need to transfer a bunch of databases with pattern {a…9} to {t…8}. …

Please copy databases below; …

adummy19 to tdummy18 – server070 (*)
adummy29 to tdummy28 – server526
adummy39 to tdummy38 – server526
adummy49 to tdummy48 – server526
adummy59 to tdummy58 – server526

Read more →

Segment Advisor dump (R 10.1)

Dump of running segment advisor in Oracle 10.1 …

http://www.oracle-base.com/articles/10g/SpaceObjectTransactionManagement10g.php / …

For tablespace …

create or replace procedure sa_tablespace
  piv_tablespace in varchar2,
  piv_task		   in varchar2 default null
  authid current_user


  obj_id number;
Read more →

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'
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   :=
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 …

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 …

Read more →

Restore SQL Server database with TSQL

Recently I had troubles restoring a database in my SQL Server 2005 instance (SP3). I used the ‘wizard’ in the SQL Server Enterprise manager but after I got the message ‘restore completed successfully’ the database hung in ‘Restoring’ state, blocking …

Read more →

freaking hilarious incident…

**** production DB issue
User ID :****
Phone :+****
Location :****
PC nr :**** …

Software/hardware :****
Problem & TS :
A job hangs in ****,
Job name: ****

User would like to be called back on the phone number provided

Read more →

osql switches

Some osql switches. …

Specify non default tcp ports
C:\>osql -Stcp:<hostname>,<portname>[\<instance_name>] -E

See http://support.microsoft.com/kb/313295 …

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 …

Read more →

Remove SQL Server database from single-user mode

Braindump of a session where I had to remove a SQL Server database from single-user mode. …

execute sp_dboption
1> exec sp_dboption 'testdb01', 'single user', 'FALSE';
2> go
Msg 5064, Level 16, State 1, Server REMIDIAN01, Line 1
Changes to the state or options
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 …

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
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 …

Read more →