Category: 11g

OCM 11g upgrade

Last Friday I went to Munich for THE upgrade exam. I have a few recommendations based on my preparation. DISCLAIMER: no recommendation is based on the exam Check you have a similar environment. You will get 11gR1 database and 10g entreprise manager. Personnaly I did my preparation on 11gR2 and 11g/12c em, I did not… Continue reading OCM 11g upgrade

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… Continue reading TNSNAMES and Active Directory

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… Continue reading how to run UTL_TCP, UTL_SMTP and the like in 11g

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… Continue reading The reasons why I always avoid to shutdown abort

my first ADR package

You got an internal error and want to create a zip of all relevant files. First, let’s generate an internal error. I found a quick way to generate an ora-600 or an ora-700 (which is a harmless ora-600 in 11g, read 737878.1) on oradeblog SQL> oradebug unit_test dbke_test dde_flow_kge_soft foo bar baz Statement processed. Now… Continue reading my first ADR package

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… Continue reading List events in session, process or system

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… Continue reading EZCONNECT and HOSTNAME resolution methods

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()… Continue reading select from comma-separated list

Published
Categorized as 11g, sql, xml Tagged

Oracle on Mac

I just read a post from Barry Mc Gillin about apex on MacOsX. After having being using Vista for one year, I would really welcome a Mac as my next notebook. But what about Oracle Database on Mac OS X? According to otn, the latest release as of today is a deprecated 10gR1 for the… Continue reading Oracle on Mac

Oracle Streams

If you have a datawarehouse and the data are getting to big for a full duplicate or tablespace transport, if you want to experience more about Streams or simply if you are in San Francisco and wants some distraction on Thursday after at 1pm, do not miss Chen session Oracle Streams – Live Demo Oracle… Continue reading Oracle Streams

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… Continue reading 11g release 1 patchset 1

About case sensitivity

SQL is a case insensitive language. That means, you can write any SQL statement in uppercase or lowercase. SQL> SELECT DUMMY FROM DUAL; D – X SQL> select dummy from dual; D – X Some formatters like Toad and some courseware like Skillsoft E-Learning advise to use UPPERCASE for commands and lowercase for tables and… Continue reading About case sensitivity

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… Continue reading read uncommitted

My book is available on amazon.com

One year ago I started writing a book on SQL. Writing a book is an amazing amount of work and I am glad I got helped from my five reviewers Chen, Andrew, Tom, Marco and Lutz. It will ship in December 2008. http://amazon.com/Advanced-Oracle-SQL-Programming-Focus/dp/0977671585

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… Continue reading alter user identified by values in 11g

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… Continue reading milliseconds in alert log

11g certification

The 11g OCP certification should be available this year. You can register for the production exam 1Z0-050 New Features on Prometrics and pass the exam on Mon Feb 18th or later, the OCA exams 1Z1-051 SQL Fundamentals and 1Z1-052 Admin I are in beta and the OCP exam 1Z1-053 Admin II is planned for early… Continue reading 11g certification

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.… Continue reading Oracle 11g Hot patching

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… Continue reading backup bigfile tablespace

Published
Categorized as 11g, Blogroll

Create edition

In 2006, I blogged about 11g new features part II. At that time 11g was in alpha or early beta stage. One of the new feature announced at OpenWorld 2006 was multiversioning. Like you have multiple versions of your packages running at the same time. Unfortunately, the feature has not been implemented in 11g Release… Continue reading Create edition

Please RTFOM !

Today I opened two SR about flashback archive in 11g. In one of them, I complained that user SCOTT was not allowed to create a flashback archive. In the doc that I downloaded a few weeks ago I read : Prerequisites You must have the FLASHBACK ARCHIVE ADMINISTER system privilege to create a flashback data… Continue reading Please RTFOM !

isNumber in sql

I tried this in 11g TABLE T X 123 -1.2e-3 abc select x, to_number( xmlquery(‘number($X)’ passing x as x returning content)) n from t; X N ——- ———- 123 123 -1.2e-3 -.0012 abc it is quite a common task to extract numbers from varchar2 and to dig out poor quality data. select x, to_number(x) from… Continue reading isNumber in sql

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… Continue reading Oracle Database 11g: The Top Features for DBAs and Developers

flashback archive table

One of the problem with flashback queries in 10g and before is that you never know if it will works, especially you cannot expect to have flashback queries working for very old tables. Let’s imagine you want to export your CUSTOMER as of 30/6/2007. No chance in 10g… Well, with 11g, you can create a… Continue reading flashback archive table

errorlogging in 11g

This is a very neat feature in 11g. I have a script called foo.sql create table t(x number primary key); insert into t(x) values (1); insert into t(x) values (2); insert into t(x) values (2); insert into t(x) values (3); commit; It is eyes-popping that this script will return an error, but which one? Let’s… Continue reading errorlogging in 11g

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… Continue reading the password is not longer displayed in dba_users.password in 11g

11g certification matrix

as of today, 24-AUG-2007, here is the certification matrix for Linux x86 OS Product Status SLES-10 11g Certified Red Hat Enterprise AS/ES 5 11g Certified Red Hat Enterprise AS/ES 4 11g Certified Oracle Enterprise Linux 5 11g Certified Oracle Enterprise Linux 4 11g Certified