search for a string in all tables of a schema

this is often asked on the forums. I also needed this a while ago while reverse engineering a database model. Here is my today solution: 1) select * and extract the first column found per table with regexp (10g) SQL> select table_name,column_name from (select rownum,table_name, regexp_substr(dbms_xmlgen.getxml(‘select * from “‘||table_name||’”‘),'<[^>]*>&string</[^<]*>’) column_name from user_tables) where length(column_name)!=0; Enter… Continue reading search for a string in all tables of a schema

su in sqlplus

How to switch user in Oracle ? One approach is to change the password : SQL> connect / as sysdba Connected. SQL> select password from dba_users where username=’SCOTT’; PASSWORD —————————— F894844C34402B67 SQL> alter user scott identified by abc123; User altered. SQL> connect scott/abc123 Connected. SQL> create table t… SQL> connect / as sysdba Connected. SQL>… Continue reading su in sqlplus

Restrict network access to listener

If I have a limited number of db clients which are authorized to access my listener, I can restrict access to my listener by setting only two parameters in sqlnet.ora. TCP.VALIDNODE_CHECKING = yes TCP.INVITED_NODES = (dbclient001,chltlxlsc1) chltlxlsc1 is my db server, I include it in the list, it is required to start the listener locally.… Continue reading Restrict network access to listener

alias oraver 2.0

Thanks to an anonymous comment in my post yesterday, I can now provide a more flexible version of my alias, which do not require the database to be running nor the sysdba privilege $ alias oraver oraver=’echo ‘\”ORACLE_SID VERSION %CPU RSZ VSZ START_TIME’\”;awk -F: ‘\”/^[^ *#]/{print “printf 42%-9s %11s %5s %8s %8s %s\\n42″,$1,”$(ORACLE_HOME=”$2,$2″/bin/sqlplus -v 2>/dev/null|cut… Continue reading alias oraver 2.0

Categorized as Blogroll, dba

ps -ef |grep pmon alternative

I just wrote a new alias to check if the databases are up and running. I added the version and a dash for non-running database. Needed is /etc/oratab + sysdba access to the database. Here it is : awk -F: ‘/^[^*# ]/{system(“echo 42select 47+ “$1″ 1147||version from v\\$instance;42|ORACLE_SID=”$1″ ORACLE_HOME=”$2” “$2″/bin/sqlplus -s 42/ as sysdba42 2>/dev/null|grep… Continue reading ps -ef |grep pmon alternative

Categorized as Blogroll, dba

rac automation

Werner Puschitz talked yesterday about Automated Oracle Real Application Clusters Deployment: How Dell Does IT. The result of the automation is, install a 6 nodes cluster in 30 minutes. Which is quite impressive. Werner divided the automation in three RPM packages. 1) configure the host: set up private and virtual interface (according to a naming… Continue reading rac automation


There is a bug about NLS_LANG and SWITZERLAND in 10g. This is because the tausend separator has changed from 9i (.) to 10g (‘). Check Note 4598613.8 The workaround is to not use NLS_LANG=german_switzerland, but if you have different NLS parameters on the client and on the server, than you will have other problems, ex:… Continue reading NLS_LANG=german_switzerland

Categorized as Blogroll, dba

generate dml

I just discovered that cool new function in SQL/Developer just right click on the table and chose export sql insert — INSERTING into EMP Insert into “EMP” (“EMPNO”,”ENAME”,”JOB”,”MGR”, “HIREDATE”,”SAL”,”COMM”,”DEPTNO”) values (7369 ,’SMITH’,’CLERK’,7902,to_date(‘1980-12-17’, ‘DD-MON-RR’),800,null,20); Insert into “EMP” (“EMPNO”,”ENAME”,”JOB”,”MGR”, “HIREDATE”,”SAL”,”COMM”,”DEPTNO”) values (7499 ,’ALLEN’,’SALESMAN’,7698,to_date(‘1981-02-20’, ‘DD-MON-RR’),1600,300,30); Insert into “EMP” (“EMPNO”,”ENAME”,”JOB”,”MGR”, “HIREDATE”,”SAL”,”COMM”,”DEPTNO”) values (7521 ,’WARD’,’SALESMAN’,7698,to_date(‘1981-02-22’, ‘DD-MON-RR’),1250,500,30); Insert into “EMP” (“EMPNO”,”ENAME”,”JOB”,”MGR”,… Continue reading generate dml

Categorized as Blogroll, dba


How often I did meet ORA-01555: snapshot too old: rollback segment too small ? I cannot count. On one of the database I am currently administrating it is about once a day. Back to Oracle 7 and 8, the solution was usually to size the rollback segments properly. Using big rollback segments for big jobs,… Continue reading TUNED_UNDORETENTION

Categorized as Blogroll, dba

tablespace maintenance tasks

I just had to guide one of our DBA this morning. How to get free of segments in SYSTEM tablespace : tables SQL> select owner, table_name from dba_tables where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’); OWNER TABLE_NAME —– ———- SCOTT T SQL> alter table scott.t move tablespace users; Table altered. SQL> select owner, table_name from… Continue reading tablespace maintenance tasks

Categorized as Blogroll, dba


I recently posted about Oracle Password Repository (OPR). I did get a comment from Andreas Piesk about something similar in Oracle, the wallet. Let’s do a quick test. First, I create a .sqlnet.ora in my home directory (I do not want to mess up the system-wide sqlnet.ora). $ cat /home/lsc/.sqlnet.ora SQLNET.WALLET_OVERRIDE=TRUE WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/lsc))) I now create… Continue reading sqlnet.wallet_override=true

select last rows

I just read about a query to retrieve last modification row of a date SQL> SELECT ora_rowscn FROM tab_test; ORA_ROWSCN ———- 351744 351744 351744 351744 351744 351744 6 rows selected. SQL> UPDATE tab_test SET valeur=valeur*1.1 WHERE col_id=1; 3 rows updated. SQL> commit; Commit complete SQL> SELECT ora_rowscn FROM tab_test: ORA_ROWSCN ———- 351744 351744 351744 371423… Continue reading select last rows

Oracle Password Repository

I checked this tool today : This tool provide a simple way of not hardcoding passwords in shell scripts. Hardcoding passwords in shell scripts is a bad practice. The source code may be shared by many developers, may resides on unsecure servers (CVS), may be printed, etc… The passwords may change often too. This… Continue reading Oracle Password Repository


EXP / IMP has a quite a lot of bugs and limitations. Today I had a trouble with a function based index first, generating ORA-942 table does not exist on import. I also had an error with AQ. I have a few invalid objects too : before exp, on source system (tru64/ SQL> select count(*)… Continue reading exp/imp


I posted yesterday some of my aliases. My favorite (and most obfuscated) one is the following (for bash) eval $(awk -F: ‘/^[+a-zA-Z]/{l=tolower($1); sub(“^+”,””,l); print “alias “l”=47x=”$2″;PATH=${PATH//$ORACLE_HOME/$x}; ORACLE_HOME=$x; ORACLE_SID=”$1″; echo ORACLE_SID=”$1”; 47; “}’ /etc/oratab 2>/dev/null) I am setting my path and a default sid/home in my .profile [ -z “$ORACLE_SID” ] && export ORACLE_SID=LSC01 export ORACLE_HOME=$(sed… Continue reading set my ORACLE_HOME, PATH, ORACLE_SID

dbms_sheduler jobs

I read in blog from Pete Finnigan about the potential security hole in DBMS_SCHEDULER package. DBMS_SCHEDULER as a new alternative for DBMS_JOB by Patrick Sinke Note that on some OS, like AIX5L / oracle, the job runs as ORACLE, not as NOBODY

dba workshop

Last week I gave an internal 2-days dba course. All three students had good experience of filesystem backup and unix, but hardly any oracle experience. I figured out recovery is more complex than I thought! By explaining to other, how often do you realise do you do not know the answer yourself? It has been… Continue reading dba workshop


There is no to_char function available for intervals. Or at least it does not work as expected SQL> select to_char(interval ‘1234’ second, ‘HH24:MM’) from dual; TO_CHAR(INTERVAL’12 ——————- +00 00:20:34.000000 I just write my own one, with some new format elements For interval day to second, I have DDD number of days, HH number of hours… Continue reading to_char(interval)


most of the time I use set autot trace exp in order to get the execution plan. It seems more easy than explain plan for [query]; followed by select * from table (dbms_xplan.display);. However, take care, set autotrace traceonly explain does modify the rows if you explain a plan for insert/update/delete. SQL> set autot trace… Continue reading difference between EXPLAIN PLAN and SET AUTOTRACE TRACEONLY EXPLAIN

Categorized as Blogroll, dba


this is a 10gR2 new feature. It is no longer necessary to recreate the control file to increase those parameters. Actually, you do not have to change them, they change “automatically” Demo SQL> CREATE DATABASE MAXDATAFILES 5 MAXINSTANCES 1 MAXLOGFILES 2 MAXLOGMEMBERS 1 extent management local default tablespace users default temporary tablespace temp undo tablespace… Continue reading change MAXDATAFILES, MALOGFILES, MAXINSTANCES, MAXLOGMEMBERS without downtime

Categorized as Blogroll, dba

security bug revealed

Pete Finnigan just mentioned a bug, which allow any user in any oracle version to get dba privilege Imperva discovers a critical access control bypass in login bug. This is incredible! Well, Pete urged you to apply CPU2006January asap.

select * from test where my_long like ‘%toto%’

A good way to learn is to try to answer user questions. Instead of referencing other posts, I tried today to answer that frequently asked question myself on (french forum) SQL> create table test ( my_long long); Table created. SQL> insert into test values (‘hello toto !’); 1 row created. SQL> exec for r… Continue reading select * from test where my_long like ‘%toto%’

clear screen reports cleared columns, breaks and computes

$ echo clear screen|sqlplus scott/tiger SQL> columns cleared breaks cleared computes cleared but if I quit properly, it does not report that $ echo “clear screen quit”|sqlplus scott/tiger SQL> a good reason to improve the quality of your shell script by quitting at the end 😉

Do you shu or do you spo?

When I quit ftp command line, I do not type quit, nor bye, because it is too long. I rather type “by”. Does it sound strange and meaningless to abbreviate “bye” in “by” ? Well, I have a few favorites SQL> set lin 999 SQL> rollb SQL> spo f SQL> shu SQL> spo off

lock system, restrict dbsnmp

An unlocked user is a security problem. Currently, all my unlocked users have only CREATE SESSION as system privilege, evtl ALTER SESSION. Except SYS, SYSTEM and DBSNMP To minimize this security problem, I implemented the following strategy on my test system. 1) delete password file, set remote_login_passwordfile=NONE, O7_DICTIONARY_ACCESSIBILITY=FALSE 2) alter user SYSTEM account lock; 3a)… Continue reading lock system, restrict dbsnmp

idle events in 10gR2

I just noticed this morning that idle events are very easily identifiable by a new column called wait_class in 10gR2 To ignore idle event, I just wrote select WAIT_CLASS, event from ( select * from V$SYSTEM_EVENT where WAIT_CLASS#!=6 order by TIME_WAITED_MICRO desc) where rownum

Categorized as Blogroll, dba

sys_connect_by_path in 8i or the danger to use undocumented parameters…

I have been posting on metalink technical forum about a query that I run against all my test databases but did not work in production. as it simpliest form select sys_connect_by_path(dummy,’:’) from dual connect by 1=2; well, there is nothing wrong with this query. I tried it on 8i, 9iR2, 10gR1 10gR2 and it worked… Continue reading sys_connect_by_path in 8i or the danger to use undocumented parameters…

Categorized as Blogroll, dba


I just tried today to limit power of rman : REVOKE ALTER SESSION, CREATE DATABASE LINK FROM RECOVERY_CATALOG_OWNER; It seems I can still do a backup… probably those privilege are not needed by rman, maybe just inherited from Connect in an older released !?

encrypted listener password

There a few major changes in the database administration and the database security between 9i and 10g. In 9i, I used to grep in the listener.ora to find out the password. LISTENER_LSC61 = (DESCRIPTION=(ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)( )) PASSWORDS_LISTENER_LSC61 = 1234567890ABCDEF this 64bit encrypted string can be used in 9i to stop the listener $ lsnrctl LSNRCTL… Continue reading encrypted listener password

post from palindnilap

I just answered a question about the data dictionary on It is all about the dictionary views. How many tables should I know in the dictionary ? SQL> select count(*) from dict; 1857 Well, that’s too much. But I can remove the GV$ view, which contain the instance for RAC, and the DBA_ ALL_… Continue reading post from palindnilap

Categorized as Blogroll, dba

oracle voyage worm

I wrote a mini script to protect my customer from being attacked by an “oracle voyage worm” variant : revoke CREATE DATABASE LINK from CONNECT; revoke ALL on SYS.UTL_FILE from PUBLIC; revoke ALL on SYS.UTL_HTTP from PUBLIC; revoke ALL on SYS.UTL_SMTP from PUBLIC; revoke ALL on SYS.UTL_TCP from PUBLIC; grant EXECUTE on SYS.UTL_FILE to XDB;… Continue reading oracle voyage worm