Author: Laurent Schneider

Whenever you create a table, it is better to use double quotes to avoid invalid identified. SQL> CREATE TABLE /XXX(x number); CREATE TABLE /XXX(x number) * ERROR at line 1: ORA-00903: invalid table name SQL> CREATE TABLE “/XXX”(x number); Table created. Even in DBMS_STATS you should use double quotes SQL> exec dbms_stats.gather_table_stats(user,’/XXX’) BEGIN dbms_stats.gather_table_stats(user,’/XXX’); END; […]

Did you know you can make your backup at least twice faster with a single line ? Demo : RMAN> backup as compressed backupset database; Starting backup at 2013-06-05_13:08:01 … Finished backup at 2013-06-05_13:13:59 6 minutes for a compressed backup on a NAS with 24 Channels and 100Gb of raw data. Not bad. But look […]

For my readers who are preparing the ocm 11g exam, the environment just changed (From 13th May 2013 onwards) Instead of using OEM 10g, you will be using OEM 11g. The upgrade exam is still using OEM 10g and DB 11gR1 (!) but I did not care installing OEM 10g and I prepared with OEM […]

I wrote about not using DAY OF MONTH and DAY OF WEEK simultanously in how to cron The correct method is to use 15 14 15 05 * /tmp/run-my-job But… I wrote this five years ago. Hmmm ! Not that correct then since it would run every year 😉 Ok, periodically I check for jobs […]

To delete large number of rows, for instance rows with date until 2010, you can issue this simple statement. SQL> DELETE FROM T WHERE C

With large table scans, sometimes the estimated total work is far beyond reality SQL> select message from v$session_longops where target=’SCOTT.EMP’; MESSAGE ———————————————————— Table Scan: SCOTT.EMP: 7377612 out of 629683 Blocks done The total work is the Oracle estimation : SQL> select blocks from dba_tables where table_name=’EMP’; BLOCKS ———- 629683 This may differ quite a lot […]

In previous posts (e.g. Export to Excel) I wrote about using HTML format to export to Excel. Let’s do it for real, let’s dive into the .xls file format and learn how to generate dynamic excel from Unix! 1) create one time your excel file manually. With graphs, colors, sounds, up to you. Or Word, […]

In Oracle Universal Installer and OPatch User’s Guide it is documented that The first Oracle home is named the “DEFAULT_HOME” and registers itself in the Windows NT Registry. Remember, NT means New Technology 🙂 There is apparently a Home Selector that is a part of the installation software, maybe something like D:\oracle\product\11.2.0\client_1\bin\selecthome.bat. Sometimes. Not sure […]

When building a materialized view, you may want to postpone the loading to a later phase, for instance you install a new release, and the refresh happends every night. BUILD DEFERRED allow you to build an empty materialized view and refresh it later. But this may still takes ages. SQL> create materialized view mv1 build […]

I have not seen DBGSQL very often. But today again, a duplicate in RMAN was failing with, amoung other errors, sqlcode 911 RMAN> duplicate target database to DB02 until time “to_date(‘2013-01-29_00:00:00′,’YYYY-MM-DD_HH24:MI:SS’)” nofilenamecheck ; DBGSQL: TARGET> select 2013-01-29_00:00:00 from sys.dual DBGSQL: sqlcode = 911 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: […]

OCM Oracle ACE Porus Homi Havewala wrote a new book on Cloud Control Oracle Enterprise Manager Cloud Control 12c: Managing Data Center Chaos It is a book about Enterprise Manager, but also a book about the Chaos. There is no separation of roles […] there is no DBA Designer (the Senior DBA) […] and no […]

If you backup your database at least weekly, you could check the datafile size from your backup history. Without catalog : select to_char(d,'”W”IW-IYYY’) compl, sum(b)/1024/1024/1024*8192 GB from ( select max(DATAFILE_BLOCKS) b, trunc(completion_time,’IW’) d from v$backup_datafile group by FILE# ,trunc(completion_time,’IW’) ) group by d order by d; COMPL GB ——– —— W30-2012 3.73 W31-2012 4.84 W32-2012 […]

Ever wanted to find an accent insentive expression like “bébé” in a column ? Maybe you tried to list all possible accents. But Posix has the class for you, the list of éèëê could be refered as [=e=] SELECT * FROM TAB WHERE REGEXP_LIKE(C,’b[[=e=]]b[[=e=]]’) not only [=e=] is easier to read and to type, but […]

Calling a shell script from within sqlplus is buggy… I have reported bug 3798918 in 10.1.0.2 (back in 2004) and bug 13349119 in 11.2.0.3 because some metalink guru closed 3798918 as not reproducible. As written in return code, host does not return the correct code SQL> host exit 7 SQL> def _RC DEFINE _RC = […]

Oracle saves a lot of information on your behalf. Whenever you get yourself an AWR reported, you access some historic tables (included in the Diagnostic Pack). Those tables could also be accessed manually. SELECT savtime,owner,object_name,rowcnt,blkcnt FROM sys.WRI$_OPTSTAT_TAB_HISTORY w, dba_objects o WHERE o.owner=’SCOTT’ AND o.object_name=’EMP’ and o.object_id = W.OBJ# ORDER BY o.owner, o.object_name, w.savtime; SAVTIME OWNER […]

Mostly when you install your instant client, you will not have tnsping handy. You could well try to copy it from a full client, but this is cumbersome to just ping your instance. I just created one function in my .profile whence tnsping >/dev/null 2>&1 || tnsping() { 😐 sqlplus -L -s x/x@$1 | grep […]

#em12c still does not support SSL ! Encrypting network connection (https, ssh, sftp) is common sense in today’s business. In Enhancement Request 6512390, Created 19-Oct-2007, the customer requested support for SSL. Most recent update : it is postponed to 13cR2 at least ! *** 09/14/12 04:04 am DISCUSSION ***As we kick off 13c release, cleaning […]

Today I could not understand why I was getting ORA-1031 with create database link. After analysis, I found out a logon trigger that changed the current schema. As Tom always says, triggers are evil… DEMO: SQL> create user u1 identified by xxx; User created. SQL> grant create session, create database link to u2 identified by […]

Today I wrote this script : drop_database_link.sql accept owner char prompt “Enter database link owner : ” accept db_link char prompt “Enter link name : ” begin dbms_scheduler.create_job( job_name=>’&owner..drop_database_link’, job_type=>’PLSQL_BLOCK’, job_action=>’BEGIN execute immediate ”drop database link &db_link”;END;’ ); dbms_scheduler.run_job(‘&owner..drop_database_link’,false); dbms_lock.sleep(2); dbms_scheduler.drop_job(‘&owner..drop_database_link’); end; / I am using the scheduler to run a job as another user. […]

Your manager asked you to load an Excel file in a table. Ok, you look at the header, create a basic table with meaningfull datatype, you open Excel and save as csv, you load your data. But then you get ORA-1722. What happened ? Ok, let’s do it create table t(x number not null, y […]

Until pretty recently, only the dubious unsalted proprietary algorithm was available to store Oracle passwords. A bunch of tool where at the time able to decode any 6-8 characters in no time, and the rainbow approach was to precalculate all possibles passwords for a specific user. Those time are not really for away, only starting […]

On the certification portal, http://certview.oracle.com you can now view your certification. This replace the original Sun Certmanager (used to be certmanager.net/sun) and it provides you an interface to publish your information to third parties. Both Sun and Oracle Certifications are visible. If you publish your information, your recipient will receive a link that will show […]

I do not like shutdown abort (see this post). I always use shutdown immediate and it always work… well almost always. Today I discovered a 9iR2 new feature : shutdown timeout ! Shutdown Timeout If all events blocking the shutdown do not occur within one hour, the shutdown operation aborts with the following message: ORA-01013: […]

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

When I was a child, I used to go skiing in the alps, and occasionaly cross the borders. I remember that late in the season (Eastern skiing) restaurants were already empty in France when we had lunch, because our neithbough countries introduced summertime before us. It is a long way back, namely summers 1976 to […]

I used to believe you cannot have a dynamic number of columns. Today Tom referenced Anton on asktom. It leaded me there, back in time, 2006, on the OTN forums https://forums.oracle.com/forums/message.jspa?messageID=1297717#1297717 Difficult to write an article on this without copy-pasting most of Anton code, so just read it on the link above. Then you will […]

I prefer to use a powerful named user with dba rather than sys. It is more conform to the security policies in place regarding accounting of administrator operations. Very occasionaly, my user get ORA-1031 insufficient privileges even if I have the dba role. Amoung others, I have “PURGE DBA_RECYCLEBIN” and DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE update, 2012-07-24 For purge […]

Can I remove the current directory? I used to believe you cannot. Solaris: $ uname -s SunOS $ mkdir /tmp/bla $ cd /tmp/bla $ rm -r /tmp/bla rm: Cannot remove any directory in the path of the current working directory /tmp/bla $ AIX: $ uname -s AIX $ mkdir /tmp/bla $ cd /tmp/bla $ rm […]

I had the incredible behavior of having the same query running 10x faster in sqlplus depending on two different PCs. After analysis, I realised the super fast pc was American… at least in nls_lang Slow client: PC setup in German, NLS_LANG is set to GERMAN_SWITZERLAND.WE8MSWIN1252 C:\>set NLS_LANG=GERMAN_SWITZERLAND.WE8MSWIN1252 C:\>sqlplus scott/tiger@db01 SQL*Plus: Release 11.2.0.3.0 Production on Fr […]

Imagine my crontab * * * * * /usr/bin/date > /tmp/foo I am writing the date to /tmp/foo every minute $ cat /tmp/foo Thu Jul 5 08:45:01 CEST 2012 Now I want to view my crontab in my EDITOR (vi). $ crontab -e I do not quit yet. In the meantime, my colleague modify the […]

One year ago, I wrote about a side effect of deferred segment creation and drop tablespace : on deferred segment creation Today I discoved one more side effect : In the good old days (I read once that you are old as soon as you start talking about the good old days) and according to […]