Remi Visser
Oracle DBA

busy bee in IT

Hire me About & Contact LinkedIn

Latest Braindumps

init parameter plsql_code_type braindump

it’s not who you are underneath, it’s what you do that defines you. …

Some code for plsql_code_type below; …

Current setting

select *
from v$parameter 
where name = 'plsql_code_type'
/

breakdown per plsql_code_type

col owner for a18
col plsql_code_type for a18


select owner,
Read more →

Script to find duplicate datafile names

Whenever you want to duplicate (copy/scp) a database and place all datafiles into one single directory you always have to check if there are no duplicate filenames. …

Eg like; …

/m001/oradata/oracle_sid/filename1.dbf
/m002/oradata/oracle_sid/filename1.dbf …

scp /m*/oradata/oracle_sid/*dbf remote_host:/m001/oradata/dup_oracle_sid/. …

SQL> select substr ( file_name, instr( file_name,
Read more →

Trace you own session using dbms_system / set events

For debugging … Make sure the session has a direct execute grant on dbms_system package. …

Level 12 makes sure to include the waits and bind data. dbms_system …

begin
for i in (select sid, serial# from v$session where audsid=SYS_CONTEXT('USERENV','SESSIONID'))
loop
	sys.dbms_system.set_ev ( si
Read more →

ORA-01114: IO error writing block to file – returns wrong file number – can’t find file

I was notifed by developer team that they get a whole lot of ORA-01114 errors. …

********_ora_17587.trc:ORA-01114: IO error writing block to file 201 (block # 96000)
********_ora_17587.trc:ORA-01114: IO error writing block to file 201 (block # 96000)
********_ora_17587.trc:2012-07-26 <!--more-->19:02:32.484306*:9C82F5B9:sql_mon_query:keswx.c@3633:keswxWriteEndInfoToStream(): done
Read more →

Overcome “ERROR: ‘${ORACLE_HOME}/agent11g’ subdirectory already exists in Oracle Inventory. Specify another home location.” when doing a silent install in a non-empty Oracle home.

While testing our silent Oracle Grid agents installs I ran into error below when (re-)installing into a non-empty Oracle home directory. …

$ ./runInstaller -silent -responseFile /home/oracle/additional_agent.rsp -invPtrLoc /var/opt/oracle/oraInst.loc
.
.
ERROR: Error:"${ORACLE_HOME}/agent11g" subdirectory already exists in Oracle Inventory. Specify another home location.
.
.

This …

Read more →

SQL query to show tables vs indexes in your tablespaces

SQL query below displays a tables / indexes breakdown of your tablespaces. …

SELECT tablespaces.tablespace_name, 
			NVL( tables, 0) as tables, 
			NVL( indexes, 0) as indexes
FROM 	dba_tablespaces tablespaces
	LEFT OUTER JOIN ( 
				SELECT COUNT(*) tables, tablespace_name 
				FROM dba_segments 
				WHERE segment_type = 'TABLE'
Read more →

Why Oracle May Really Be Doomed This Time

http://pandodaily.com/2012/02/11/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 …

@See:
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_advis.htm
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2049277600346543592
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

as

  obj_id number;
  task
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 →

ORAENV_ASK

ORAENV_ASK=no …

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 →

Most read

Remove SQL Server database from single-user mode Increase idle time for OMS frontend sessions overcome SP2-0734 when using purge command MSSQL cheat sheet for the Oracle minded shutdown immediate taking ages (two days) to complete – or – ‘No, I will NOT enlarge the UNDO tablespace’ – or – patience … Oracle Advisor – DBMS_ADVISOR net use: map network share Really delete (‘corrupt’) Agent from OMS repository Progress OpenEdge Database 10.1b install on Centos (RHEL) Windows handies expdp / impdp SQL query to show tables vs indexes in your tablespaces Happy holidays freaking hilarious incident… Date Time functions ORA-01114: IO error writing block to file – returns wrong file number – can’t find file Standalone Internet Explorer 7 install Invalidated synonyms Using Email (utl_smtp) in Oracle 11g (results in ORA-24247) – Network Access Control List (ACL). dbverify in nohup Transporting an Oracle database to another os platform the fastest way Control Structures Clearing Alerts in Oracle GRID Control Why Oracle May Really Be Doomed This Time awk new explain plan options (dbms_xplan) 11g installation on Linux (Centos) Make Joomla! multi sites enabled. delete large number of records with intermediate commits Batch SQL statements in parallel (multiple) sessions Trace you own session using dbms_system / set events init parameter plsql_code_type braindump ORAENV_ASK invalid objects after clean ctxsys installation (oracle text) dba$log_blocker Purging SYSAUX tablespace (Purging AWR reports) PL/SQL quickie; generate RMAN set newname script to different mountpoints SQL Trace Reference Generate scp datafiles script with regular expression SQL script to check available space in your recoveryarea (db_recovery_file_dest_size) return variables from dynamic sql – sp_executesql cursors in tsql Create a progress database Use filters to keep your traces meaningfull Centos 5.0 install fails, kernel panic monitor progress of long running job with dbms_application_info.set_session_longops DBMS_NETWORK_ACL_ADMIN.CREATE_ACL returns ORA-30992 Manually triggering Metric Collection (clearing -tablespace- Alerts) sp_configure change database id of database mailx Segment Advisor dump (R 10.1) Overcome “ERROR: ‘${ORACLE_HOME}/agent11g’ subdirectory already exists in Oracle Inventory. Specify another home location.” when doing a silent install in a non-empty Oracle home. OFF TOPIC: The voice of Europe Restore SQL Server database with TSQL To trace or not to trace a winner, flashback query; AS OF TIMESTAMP Script to find duplicate datafile names osql switches nohup Automatic Workload Repository Fix ORA-00257: archiver error. Connect internal only, until freed – in Oracle 11G pipe Create, drop and rename a database Download and Install of Java (JRE) on Centos Microsoft SQL Server 2005 JDBC Driver (oracle GRID)

Categories

Contractees