Friday, December 24, 2010

Finding Your Trace File

This is an example using the tracefile_identifier parameter, which can make it easier to find your tracefiles. First show the value of this parameter, which will normally be null:

SQL> col tracefile_identifier format a30
SQL> select nvl(value, 'NULL') tracefile_identifier
2 from v$parameter where name = 'tracefile_identifier'
3 /

TRACEFILE_IDENTIFIER
------------------------------
NULL

SQL>

   
Now change this parameter:

SQL> alter session set tracefile_identifier = 'ANDREW'
2 /

Session altered.

SQL>

 
Start tracing your session and run some SQL:

SQL> alter session set sql_trace = true
2 /

Session altered.

SQL> col systimestamp format a40
SQL> select systimestamp from dual
2 /

SYSTIMESTAMP
----------------------------------------
22-DEC-10 04.11.27.125000 AM +00:00

SQL> alter session set sql_trace = false
2 /

Session altered.

SQL>

 

Now, when you look for the tracefile, the tracefile_identifier will form part of the name. Note that the SYSTIMESTAMP shown above is the time on the underlying operating system, which is not necessarily the same as the SYSDATE in the database. The SYSTIMESTAMP matches the update time of the tracefile as shown below:

C:\app\Andrew\diag\rdbms\adhoc\adhoc\trace>dir *ANDREW.trc
Volume in drive C has no label.
Volume Serial Number is 18E4-B972

Directory of C:\app\Andrew\diag\rdbms\adhoc\adhoc\trace

22/12/2010 04:11 2,041 adhoc_ora_2988_ANDREW.trc
            1 File(s)         2,041 bytes
            0 Dir(s) 13,303,558,144 bytes free

C:\app\Andrew\diag\rdbms\adhoc\adhoc\trace>

Thursday, December 23, 2010

How to Delete Duplicate Rows

First you need to set up some data:
  
SQL> CREATE TABLE DUPLICATE_ROWS
  2   (FIRST_NAME VARCHAR2(10))
  3  /

Table created.

SQL> INSERT INTO DUPLICATE_ROWS VALUES ('ANDREW')
  2  /

1 row created.

SQL> INSERT INTO DUPLICATE_ROWS VALUES ('COLIN')
  2  /

1 row created.

SQL> INSERT INTO DUPLICATE_ROWS VALUES ('GRAHAM')
  2  /

1 row created.

SQL> INSERT INTO DUPLICATE_ROWS VALUES ('JOHN')
  2  /

1 row created.

SQL> INSERT INTO DUPLICATE_ROWS VALUES ('SAMUEL')
  2  /

1 row created.

SQL> BEGIN
  2   FOR A IN 1..16
  3   LOOP
  4    INSERT INTO DUPLICATE_ROWS
  5     SELECT * FROM DUPLICATE_ROWS;
  6   END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> COMMIT
  2  /

Commit complete.

SQL>

Now use a group by to show the duplicate rows:
 
SQL> SELECT FIRST_NAME, COUNT(*)
  2   FROM DUPLICATE_ROWS
  3    GROUP BY FIRST_NAME
  4  /

FIRST_NAME   COUNT(*)
---------- ----------
ANDREW          65536
COLIN           65536
JOHN            65536
GRAHAM          65536
SAMUEL          65536

SQL>

Delete the duplicate rows. This method is shown on several other web sites:

SQL> SET TIMING ON
SQL> DELETE FROM DUPLICATE_ROWS A
  2   WHERE ROWID >
  3   (SELECT MIN(ROWID) FROM DUPLICATE_ROWS
  4    WHERE FIRST_NAME = A.FIRST_NAME)
  5  /

327675 rows deleted.

Elapsed: 00:00:12.91
SQL> SET TIMING OFF
SQL>

Show that the duplicates have gone:

SQL> SELECT FIRST_NAME, COUNT(*)
  2   FROM DUPLICATE_ROWS
  3    GROUP BY FIRST_NAME
  4  /

FIRST_NAME   COUNT(*)
---------- ----------
ANDREW              1
COLIN               1
JOHN                1
GRAHAM              1
SAMUEL              1

SQL>

Rollback the deletion:

SQL> ROLLBACK
  2  /

Rollback complete.

SQL>

Check that the duplicate rows have returned:

SQL> SELECT FIRST_NAME, COUNT(*)
  2   FROM DUPLICATE_ROWS
  3    GROUP BY FIRST_NAME
  4  /

FIRST_NAME   COUNT(*)
---------- ----------
ANDREW          65536
COLIN           65536
JOHN            65536
GRAHAM          65536
SAMUEL          65536

SQL>

Now try a different method to delete the duplicate rows. This is similar to how we used to do it with COBOL:
 
SQL> SET TIMING ON
SQL> DECLARE
  2   CURSOR C1 IS
  3    SELECT ROWID, FIRST_NAME
  4    FROM DUPLICATE_ROWS
  5    ORDER BY FIRST_NAME;
  6   PREVIOUS_FIRST_NAME
  7    DUPLICATE_ROWS.FIRST_NAME%TYPE;
  8  BEGIN
  9   PREVIOUS_FIRST_NAME :=' ';
 10   FOR REC IN C1
 11    LOOP
 12     IF REC.FIRST_NAME = PREVIOUS_FIRST_NAME THEN
 13      DELETE FROM DUPLICATE_ROWS
 14      WHERE ROWID = REC.ROWID;
 15     END IF;
 16     PREVIOUS_FIRST_NAME := REC.FIRST_NAME;
 17    END LOOP;
 18  END;
 19  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.09
SQL> SET TIMING OFF
SQL>

Check that the duplicate rows have gone:

SQL> SELECT FIRST_NAME, COUNT(*)
  2   FROM DUPLICATE_ROWS
  3   GROUP BY FIRST_NAME
  4  /

FIRST_NAME   COUNT(*)
---------- ----------
ANDREW              1
COLIN               1
GRAHAM              1
JOHN                1
SAMUEL              1

SQL>

Sunday, December 19, 2010

ORA-01647

I went on a course once. I think it was an Oracle 8 new features one in the late 90's but I could be wrong. The lecturer asked us if you could drop a table in a READ ONLY tablespace. For some reason I was the only person who knew the answer. What I didn't tell him was that I did not understand the answer. In fact, I don't understand it even now but never mind. Here is a tablespace in READ WRITE mode:

SQL> select status from dba_tablespaces
  2  where tablespace_name = 'USERS'
  3  /

STATUS
---------
ONLINE

SQL>


I created a table in it:

SQL> create table andrew1 (one_col number)
  2  tablespace users
  3  /

Table created.

SQL>

I made the tablespace READ ONLY:
 
SQL> alter tablespace users read only
  2  /

Tablespace altered.

SQL> select status from dba_tablespaces
  2  where tablespace_name = 'USERS'
  3  /

STATUS
---------
READ ONLY

SQL>

I tried to create a new table in this tablespace:
 
SQL> create table andrew2 (one_col number)
  2  tablespace users
  3  /
create table andrew2 (one_col number)
*
ERROR at line 1:
ORA-01647: tablespace 'USERS' is read only, cannot allocate
space in it

SQL>

That did not work, which seems reasonable. Then I tried to drop the table I created earlier:
 

SQL> drop table andrew1
  2  /

Table dropped.

SQL>

That worked, which has always seemed strange to me. Finally I put the tablespace back into READ WRITE mode for future use:
 

SQL> alter tablespace users read write
  2  /

Tablespace altered.

SQL> select status from dba_tablespaces
  2  where tablespace_name = 'USERS'
  3  /

STATUS
---------
ONLINE

SQL>

Saturday, December 18, 2010

Selecting Data at Random from an Oracle Table

This example shows how to select data at random. First I created a table and counted the number of rows it had:

SQL> create table andrew as
  2  select owner, object_name, object_type
  3  from dba_objects
  4  /

Table created.

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
     68299

SQL>


Then I used the sample keyword to select 0.01% of the rows at random. In theory this should have produced 6 or 7 rows depending on how the rounding was done but the actual number was slightly different. This is normal:
  
SQL> col object_name format a30
SQL> select object_name from andrew sample (0.01)
  2  /

OBJECT_NAME
------------------------------
DBMS_STREAMS_AUTH
sun/nio/cs/ext/EUC_TW$Encoder
/55848856_BasicTableUIMouseInp
/e081aebc_LLNIFieldDefsRes
sun/awt/PlatformFont
/e3d5b152_DSAKeyPairGenerator
oracle/jms/AQjmsMessages_it
X$PR6UAG759GKO8J6PU11C34F52MCK
/eb2968cb_MlibConvolveRIF
WWV_META_CLEANUP

10 rows selected.

SQL>


I repeated the process and got a different sample. The number of rows shown was not the same either:

SQL> select object_name from andrew sample (0.01)
  2  /

OBJECT_NAME
------------------------------
/54e20e2_MIRSSAHeapVariableMan
/ad0cf592_BasicInternalFrameUI
/a0cd2aae_KDCReq
/ac90f805_DelegateImpl
/6a03f6a7_OraCustomizerErrorsT
SDO_NET_PARTITION
MGMT_CRED_ROW_ARRAY
WWV_FLOW_FLASH_CHARTS_PK
WWV_BIU_FLOW_LOV_VAL_AUDIT

9 rows selected.

SQL>


If the number of rows you see is important, the following method is better. I ran it twice to show that the number of rows returned was consistent. The number of rows selected was not displayed due to the setting of FEEDBACK:

SQL> show feedback
FEEDBACK ON for 6 or more rows
SQL> select object_name from
  2  (select object_name from andrew
  3   order by dbms_random.value)
  4  where rownum < 6
  5  /

OBJECT_NAME
------------------------------
CWM2$LEVELUPD
oracle/sql/CharacterSetJAEUC
DM_SVM_ATTRIBUTE
oracle/xquery/exec/XpathExpr
/b5483d4c_ByteArrayLexOrder

SQL> select object_name from
  2  (select object_name from andrew
  3   order by dbms_random.value)
  4  where rownum < 6
  5  /

OBJECT_NAME
------------------------------
/e28402f8_ObjectClosedExceptio
/a9973a55_BufferedOutputStream
/96361fa8_ShortBufferException
/b40ab093_AssignBitAndExpressi
/f5e722c3_XSLJDWPDebugger

SQL>

Friday, December 17, 2010

DDL Causes Implied Commit

If you add, modify or remove table data, you can use rollback to reverse that change if you have not done a commit to save it. To demonstrate this I created a table and showed that it was empty:

SQL> create table andrew
  2  (one_column number)
  3  /

Table created.


SQL> select * from andrew
  2  /

no rows selected

SQL>


Then I added a row to the table and displayed it afterwards:

SQL> insert into andrew values (1)
  2  /

1 row created.

SQL> select one_column from andrew
  2  /

ONE_COLUMN
----------
         1

SQL>


I used rollback to reverse the transaction and showed that the table was empty again:

SQL> rollback
  2  /

Rollback complete.

SQL> select * from andrew
  2  /

no rows selected

SQL>


I repeated the process but did a commit to save the change before doing the rollback:

SQL> insert into andrew select 2 from dual
  2  /

1 row created.

SQL> select * from andrew
  2  /

ONE_COLUMN
----------
         2

SQL> commit
  2  /

Commit complete.

SQL> rollback
  2  /

Rollback complete.

SQL>


This time the row was still in the table:

SQL> select * from andrew
  2  /

ONE_COLUMN
----------
         2

SQL>


Finally, I multiplied the value in the row by 2 but did not commit the change:

SQL> update andrew set one_column = one_column * 2
  2  /

1 row updated.

SQL> select * from andrew
  2  /

ONE_COLUMN
----------
         4

SQL>


I ran a data definition language (DDL) statement. This caused an implied commit:

SQL> create public synonym andrew for andrew
  2  /

Synonym created.

SQL>


So when I tried to rollback the change this had no effect and the value stayed the same:

SQL> rollback
  2  /

Rollback complete.


SQL> select * from andrew
  2  /

ONE_COLUMN
----------
         4

SQL>

Tuesday, December 14, 2010

Selecting Maximum and Minimum Values

You can find the maximum value in a column as follows:

SQL> select max(username) from dba_users
  2  /

MAX(USERNAME)
------------------------------
ZEBRA

SQL>


I'm not suggesting you do this at home but you can check the value returned as follows. This SQL uses a correlated subquery. I will try to look at these in more detail in a future post.

SQL> select username from dba_users x
  2  where not exists
  3  (select username from dba_users
  4   where username > x.username)
  5  /

USERNAME
------------------------------
ZEBRA

SQL>


This is how you find the minimum value:

SQL> select min(username) from dba_users
  2  /

MIN(USERNAME)
------------------------------
ANONYMOUS

SQL>