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

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 11.2.0.1 and partly in 11.2.0.2, 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 scott/tiger […]

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

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

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<9999999999999' PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 But it is really annoying… and not documented as far as I know. Imagine I […]

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 NUMBER;Z NUMBER;END;   3  / […]

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<=3000)   select rownum empno,ename,job,mgr,hiredate,sal,comm,deptno from emp,l,l table big_emp created. […]

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 14:47 _anon__3c8651198__AB.plz let’s check one, briefly : *** ASSERT at […]

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