List of table and column privileges, including those via roles

I could not find this quickly enough in google so I wrote it myself. The list of table privileges, with a connect by subquery. COL roles FOR a60 COL table_name FOR a30 col privilege for a9 set lin 200 trims on pages 0 emb on hea on newp none SELECT * FROM ( SELECT CONNECT_BY_ROOT […]

Reduce the number of commits

“Oftentimes, a database administrator (DBA) simply looks at the symptoms and immediately starts changing the system to fix those symptoms” Op. Cit. Oracle Database Performance Tuning Guide 11g Release 2 (11.2) Ok, let’s do this 🙂 Finding Waits on event “log file sync” while performing COMMIT and ROLLBACK operations were consuming significant database time. Action […]

nothing in user_segments

I wrote on deferred segment creation recently. Today I was looking for specific storage attributes that I used to find in user_segments. They are no longer here. Where are they then? test case : create table t(x clob) store (x) as securefile x (retention max storage(maxsize 8192000000)); Where do I find the retention max max_size […]

How to unload blob from the database?

There is more than one post on how to unload blob from the database, mostly in plsql with utl_file.put_raw (see note 330146.1) and with java with FileOutputStream (see note 247546.1) Unfortunately both are terribly slow due to the 32k limitation of put_raw in utl_file and due to a low “optimum buffer size” retrieved by myBlob.getBufferSize(), […]

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

On deferred segment creation

What’s deferred segment creation? It is a feature that saves lots of time by releases and lots of space if you have a -legacy- application with 10’000 tables and most of them are empty. When was it introduced ? Partly in and partly in, depending on the object type. What’s the opposite ? […]

Datapump : table like ‘FOO%’ or like ‘BAR%’

Today I tried to put two like condition in an INCLUDE clause of datapump. I have the following tables SQL> select table_name from user_tables order by 1; TABLE_NAME —————————— AAA BAR1 *** BAR2 *** BLA FOO *** FOO1 *** GOZ and I want tables like BAR% and tables likes FOO% First try : $ expdp […]

On implicit commit

An explicit commit is when you issue a COMMIT statement SQL> create table t(x number); Table created. SQL> insert into t values(1); 1 row created. SQL> commit; Commit complete. An implicit commit is when a commit is issued without your approval. ex: AUTOCOMMIT (default is OFF) SQL> set autoc on SQL> insert into t values(1); […]

sqlplus -prelim

If you cannot login to the database, for instance due to ORA-00020 maximum number of processes exceeded, then chance exists that you could use the -prelim option. Documented in note 121779.1 for sqlplus version 10.1 and later : In some cases, no connections are allowed on the instance (in some ORA-20 situations for example). As […]

On using Toad against a database

I got this question once again today in a previous post. What’s wrong by using Toad against a database? The worst case scenario: – some non-technical staff is clicking around in your production database with read-write access 🙁 The best-case scenario : – nobody has access to your database 🙂 Here is a short list […]

Troubleshoot ORA-10878

You will probably not hit this bug unless you perform some media recovery in Ok. In case you hit ORA-10878: parallel recovery slave died unexpectedly during a DUPLICATE or a RESTORE command, you can disable parallel media recovery with _log_parallelism_max=1. The usual warning applies : do not use hidden parameter without guidance of Oracle […]

track ddl change (part 2)

I wrote about tracking ddl changes with a trigger there : track ddl changes Another option is to use auditing. A new and cool alternative is to use enable_ddl_logging (11gR2). This will track all ddl’s in the alert log ALTER SYSTEM SET enable_ddl_logging=TRUE Then later you issue create table t(x number) and you see in […]

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

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

Which index can you rebuild?

I recently wrote on table reorg and rebuild index Rule number one : you cannot rebuild a partitioned index in whole. You need to rebuild each individual (sub-)partition Rule number two : to rebuild an iot, move the table instead of trying to rebuild the underlying index Rule number three : a LOB index is […]

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

Return NULL if the column does not exist

It is a very common challenge for a dba to create scripts that work on every version. How do you return NULL if a column does not exists? Imagine I have a view that returns the table_name, column_name and retention_type of my LOBS. SQL> create table t1(c clob) lob(c) store as (retention); Table created. SQL> […]

On table reorg and index rebuild

Before you start reading : do not rebuild all your indexes and reorganize all your tables every Sunday morning. One day you may find one of your table missing or one index invalid. Ok, let’s take a case where table reorg and index rebuild is good. One of your table was never cleaned up, it […]

EXECUTE IMMEDIATE ‘SELECT’ does not execute anything

I am not sure whether some tuning guy at Oracle decided to ignore any SELECT statement after execute immediate to save time doing nothing. exec execute immediate ‘select 1/0 from dual connect by level

RMAN duplicate does change your DB_NAME !

I had a very serious issue last Friday with errors as weird as ORA-00322: log name of thread num is not current copy. After a clone from Prod to Test, the prod crashed. Both databases are located on the same server (I am not a virtualization fanatic) and clone from prod to test have been […]

How to solve ORA-4068

I was amazed by this oneliner in stackoverflow. First, let me introduce you my old foe, ORA-04068 : Session 1: SQL> CREATE OR REPLACE PACKAGE P AS 2 X NUMBER;Y NUMBER;END; 3 / Package created. SQL> exec P.X := 1 PL/SQL procedure successfully completed. Session 2: SQL> CREATE OR REPLACE PACKAGE P AS 2 X […]

make count(*) faster

I just install Oracle Enterprise Linux on my new notebook. I wanted to check how far could I improve the performance of a count(*) SQL> drop table big_emp; table big_emp dropped. 258ms elapsed SQL> create table big_emp as with l as(select 1 from dual connect by level alter table big_emp add primary key(empno) table big_emp […]

This version of TOAD provides READONLY access!

This Toad 11 beta new feature made me so happy ! Not only you can specify a color for your connection (ex: red=prod, green=devl), and this color is much more visible than it was in previous version of TOAD, but now you can set your connection to be read-only. For any reason, bored, ill, under […]

SQL developer for dba

Historically and semantically, SQL Developer is a developer tool. There is very little comparison with TOAD for the dba. But… Oracle is working on this! In the latest release, 3.0 EA1, you will be able to see Tablespaces, Redo logs and controlfile, finally. Still no session browser, but I am delighted that the database administrators […]

How to check if I have a pending transaction?

Pretty straightforward, check if dbms_transaction.step_id is null! SQL> select dbms_transaction.step_id from dual; STEP_ID ————— SQL> insert into t values (1); 1 row created. SQL> select dbms_transaction.step_id from dual; STEP_ID ————— 114352430549782 SQL> commit; Commit complete. SQL> select dbms_transaction.step_id from dual; STEP_ID ————— SQL> insert into t values (2); 1 row created. SQL> select dbms_transaction.step_id from […]

where is the TRIGGER ANY TABLE privilege?

You have your table data in one schema and your procedures in another one. But can you have triggers and tables in different schemas? SYS@lsc01> create user u1 identified by u1; User created. SYS@lsc01> create user u2 identified by u2; User created. SYS@lsc01> grant create table, unlimited tablespace to u1; Grant succeeded. SYS@lsc01> grant create […]

.plz dump file

What are those .plz dump files in my user dump directory ? -rw-r–r– 1 oracle dba 15168 Oct 6 14:34 _anon__3ca8c5e38__AB.plz -rw-r—– 1 oracle dba 15883 Oct 6 14:45 db01_ora_10061.trc -rw-r–r– 1 oracle dba 15168 Oct 6 14:45 _anon__3c929b088__AB.plz -rw-r—– 1 oracle dba 15895 Oct 6 14:47 db01_ora_10666.trc -rw-r–r– 1 oracle dba 15168 Oct 6 […]

future release dates of 11gR2

HPUX Itanium, Sun Sparc/x86_64, AIX –> october-december 2009 Windows, HPUX Risc –> april-june 2010 Ref: Release Schedule of Current Database Patch Sets Dates will change, keep an eye on the schedule, and do not base your business on expected dates ! Please be patient 🙂 is the latest patchset for 11gR1

You will get CPU and even PSU (Patch Set Updates are proactive cumulative patches comprised of recommended bug fixes that are released on a regular and predictable schedule) But no more patchset. This the first time I see a release with only 1 pachset. Well, if you use Linux, you can go to 11gR2. Otherwise […]

CONNECT no longer has CREATE VIEW, what’s next?

In my current project we are going to move from 9i to 10g really soon. One of the typical issue is to get the right privileges, because the schema owners typically had only CONNECT and RESOURCE, and that does no longer include CREATE VIEW in 10gR2. I was just reading the 11gR2 readme today : […]