Category Archives: 11g

TNSNAMES and Active Directory

It is highly probable you already have MS AD in your company. Probably you use a local tnsnames.ora. Apart from setting a Oracle Internet Directory or Oracle Virtual Directory, there is one more option that you may want to consider : AD. Ok, here is a bit of a road map : – Schema Extension […]

how to run UTL_TCP, UTL_SMTP and the like in 11g

After we upgrade a db to 11g someone complained about an ORA-24248: XML DB extensible security not installed I thought, it should be easy to revert to 10g mechanism. Probably wrong after reading Marco : The default behavior for access control to network utility packages has been changed to disallow network operations to all nonprivileged […]

The reasons why I always avoid to shutdown abort

It is a common practice to always shutdown abort the database before restarting and shutting in down immediate. This is because sometimes SHUTDOWN IMMEDIATE takes ages. For instance due to a huge transaction to be rollback. I do not like it. At all. First, chance exists that you won’t be able to start the database […]

List events in session, process or system

There is a new command in 11g to display the current events, which is oradebug eventdump. For instance : SQL> alter session set events ‘10046 trace name context forever,level 12:942 trace name ERRORSTACK level 3’; SQL> oradebug setmypid Statement processed. SQL> oradebug eventdump session sql_trace level=12 942 trace name ERRORSTACK level 3 Read metalink note […]

EZCONNECT and HOSTNAME resolution methods

EZCONNECT is the easy connect protocol, available in 10g, whenever you want to connect to a database without tnsnames and without ldap. $ grep -iw directory_path $TNS_ADMIN/sqlnet.ora names.directory_path=EZCONNECT $ sqlplus scott/tiger@//srv01:1521/db01 connect to server srv01 on port 1521 for service db01 HOSTNAME was the old-fashion way to connect to a database, where hostname = sid […]

select from comma-separated list

This is asked over and over in the forums, but why not proposing an 11g solution here 😉 create table t(description varchar2(12) primary key, numbers varchar2(4000)); insert into t(description, numbers) values (‘PRIME’,’2,3,5,7′); insert into t(description, numbers) values (‘ODD’,’1,3,5,7,9′); commit; DESCRIPTION NUMBERS PRIME 2,3,5,7 ODD 1,3,5,7,9 Now I want to unpivot numbers in rows select description,(column_value).getnumberval() […]

11g release 1 patchset 1

I just notice on Sven Blog that 11.1.0.7 is available. I have recently upgraded my connection at home so it took a bit less than half an hour to download this 1.5G patchset $ wget -O p6890831_111070_Linux-x86-64.zip http://oracle-updates.oracle… –10:17:40– http://oracle-updates.oracle.com/ARUConnect/p6890831_111070_Linux-x86-64.. Resolving oracle-updates.oracle.com… 87.248.199.23, 87.248.199.24 Connecting to oracle-updates.oracle.com|87.248.199.23|:80… connected. HTTP request sent, awaiting response… 200 OK […]

read uncommitted

the default isolation level is READ COMMITTED. It means, a session read the committed data. Session 1: SQL> set transaction isolation level read committed; Transaction set. Session 2: SQL> update emp set sal=4000 where ename=’SCOTT’; 1 row updated. Session 1: SQL> select sal from emp where ename=’SCOTT’; SAL ———- 3000 Session 2: SQL> commit; Commit […]

alter user identified by values in 11g

I wrote about dba_users changes in 11g . When spooling alter user commands in 11g, it is important to understand the mechanism. Oracle 11g supports both sensitive and insensitive passwords. When issuing an CREATE/ALTER USER IDENTIFIED BY PASSWORD, both the insensitive and the sensitive hashes are saved. SQL> create user u identified by u; User […]

milliseconds in alert log

In Oracle11g the alert log is an XML file. The old style alertSID.log is created out of the log.xml for backward compatibility only. However, some exciting enhancement are not noticable in the old one. $ tail -3 alert*.log Mon Feb 04 15:52:38 2008 ALTER SYSTEM SET recyclebin=’OFF’ SCOPE=SPFILE; ALTER SYSTEM SET recyclebin=’ON’ SCOPE=SPFILE; If I […]

Oracle 11g Hot patching

Online Patching : you can apply or roll back online patches while the RDBMS instance is running 1) download an interim patch for 11g, f.ex. dummy patch 6198642 2) unzip p6198642_111060_LINUX.zip 3) cd 6198642 4) $ORACLE_HOME/OPatch/opatch apply -silent -connectString LSC08 -runSql Invoking OPatch 11.1.0.6.0 Oracle Interim Patch Installer version 11.1.0.6.0 Copyright (c) 2007, Oracle Corporation. […]

backup bigfile tablespace

One may pretend bigfile tablespace is bad, because you cannot backup/restore in parallel. SQL> create bigfile tablespace big datafile size 1g; Tablespace created. RMAN> configure device type disk parallelism 4; using target database control file instead of recovery catalog old RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET; new […]

Oracle Database 11g: The Top Features for DBAs and Developers

I am always delighted to read the top features by Arup Nanda. He started his 11g series : Oracle Database 11g: The Top Features for DBAs and Developers There are many partitioning enhancements. The most exciting feature for me is the INTERVAL partitioning. A huge cause of downtime and waste of storage is the range […]

the password is not longer displayed in dba_users.password in 11g

By reading Pete Finnigan’s Oracle security weblog today, I discovered that the password is no longer displayed in DBA_USERS in 11g. select username,password from dba_users where username=’SCOTT’; USERNAME PASSWORD ——– —————————— SCOTT select name,password from sys.user$ where name=’SCOTT’; NAME PASSWORD —– —————————— SCOTT F894844C34402B67 on the one hand, it is good for the security. On […]