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

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. […]

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

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

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”, […]

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

sqlnet.wallet_override=true

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

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

Oracle Password Repository

I checked this tool today : http://sourceforge.net/projects/opr 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 […]

set my ORACLE_HOME, PATH, ORACLE_SID

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

to_char(interval)

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

difference between EXPLAIN PLAN and SET AUTOTRACE TRACEONLY EXPLAIN

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

change MAXDATAFILES, MALOGFILES, MAXINSTANCES, MAXLOGMEMBERS without downtime

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

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 developpez.com (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 […]

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) […]

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

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)(HOST=dbsrv85a.ex.zkb.ch)(PORT=10061)(QUEUESIZE=200)) )) PASSWORDS_LISTENER_LSC61 = 1234567890ABCDEF this 64bit encrypted string can be used in 9i to stop the listener $ lsnrctl LSNRCTL […]

post from palindnilap

I just answered a question about the data dictionary on forums.oracle.com. 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_ […]

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; […]