How to get rid of corrupted blocks without a backup

First, you identify the blocks in alert log or with db verify $ dbv BLOCKSIZE=8192 file=sysaux01.dbf DBV-00201: Block, DBA 12629823, marked corrupt for invalid redo application … DBVERIFY – Verification complete Total Pages Examined : 131072 Total Pages Processed (Data) : 69691 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 28669 Total Pages… Continue reading How to get rid of corrupted blocks without a backup

How much is + ‘x’ ?

Sounds like an april fool, but I wonder what is + ‘x’ supposed to do 🙂 SQL> select + ‘x’ from dual; + – x it does not convert to number as 0 + ‘1’ would do

On using ROWID

I have been challenged to assert the safety of rowid in a sql statement. Against all my beliefs, it is not safe to assume ROWID is consistent over one sql statement. If the ROWID changes, and you use ROWID in your query, you may get inconsistent results. Obviously I would not write such a post… Continue reading On using ROWID

Published
Categorized as sql Tagged

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… Continue reading List of table and column privileges, including those via roles

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… Continue reading Reduce the number of commits

Published
Categorized as 11gR2, dba, sql

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… Continue reading nothing in user_segments

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(),… Continue reading How to unload blob from the database?

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

On star transformation

How to configure a schema to support a star transformation query? This is one of the topic I am currently preparing for the OCM DBA 11g upgrade exam for 9i OCM’s. Let’s create a star schema with a fact table T1 and two dimension tables T2 and T3 : > create table t2(y number constraint… Continue reading On star transformation

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);… Continue reading On implicit commit

CSV part 4, fast !!

I got some comments that my other csv solutions were slow to export gigabytes of data. One more try. thanks to the feedbacks, I provided a new version This could generate very large files in just a few minutes (instead of hours). I use bulk collect and utl_file to boost performance CREATE TYPE collist IS… Continue reading CSV part 4, fast !!

on materialized view constraints

Oracle is pretty strong at enforcing constraint. Table for this blog post: create table t(x number primary key, y number); For instance if you alter table t add check (y insert into t values (1,2000); insert into t values (1,2000) Error at line 1 ORA-02290: check constraint (SCOTT.SYS_C0029609) violated I believe this code to be… Continue reading on materialized view constraints

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… Continue reading track ddl change (part 2)

How does random=random evaluates?

I had fun answering a question about random on the technical forums. What is in your opinion the boolean value of DBMS_RANDOM.VALUE=DBMS_RANDOM.VALUE? Or, how many rows would select * from dual where dbms_random.value=dbms_random.value; return? It is wrong to assume the function will be evaluated twice. The short answer would be : do not rely on… Continue reading How does random=random evaluates?

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… Continue reading Which index can you rebuild?

Published
Categorized as dba, sql Tagged

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… Continue reading make count(*) faster

jdbc ssl

I already wrote about jdbc hello world and listener with tcps. Let’s combine both technologies ! TCPS.java import java.util.Properties; import java.security.Security; import java.sql.*; import javax.net.ssl.*; public class TCPS { public static void main(String argv[]) throws SQLException { String url = “jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(Host=dbsrv001)(Port=12345))(CONNECT_DATA=(SID=DB01)))”; Properties props = new Properties(); props.setProperty(“user”, “scott”); props.setProperty(“password”, “tiger”); props.setProperty(“javax.net.ssl.trustStore”,”cwallet.sso”); props.setProperty(“javax.net.ssl.trustStoreType”,”SSO”); Security.addProvider(new oracle.security.pki.OraclePKIProvider()); DriverManager.registerDriver(new… Continue reading jdbc ssl

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… Continue reading How to check if I have a pending transaction?

Hardcoding SYSDATE

I see TRUNC(SYSDATE) in the code very often. Is this good or bad? Over my past two years as a developer, I can tell you it is pretty bad. What does TRUNC(SYSDATE) mean? It is today’s date. But when does today starts and ends? And at the time of the writing, it is already tomorrow… Continue reading Hardcoding SYSDATE

connect by and recursive with (part 2)

According to the doc The subquery_factoring_clause now supports recursive subquery factoring (recursive WITH), which lets you query hierarchical data. This feature is more powerful than CONNECT BY in that it provides depth-first search and breadth-first search, and supports multiple recursive branches. A new search_clause and cycle_clause let you specify an ordering for the rows and… Continue reading connect by and recursive with (part 2)