ISDEFAULT

There is a column in V$PARAMETER called ISDEFAULT:

SQL> col name format a10
SQL> col value format a10
SQL> select name, value, isdefault
  2  from v$parameter
  3  where name = 'recyclebin'
  4  /

NAME       VALUE      ISDEFAULT
---------- ---------- ---------
recyclebin on         TRUE

SQL>

You might think that this tells you whether a particular parameter is set to its default value but this is not the case. If you add a recyclebin=on line to the parameter file and bounce the database, the same query runs as follows:

SQL> col name format a10
SQL> col value format a10
SQL> select name, value, isdefault
  2  from v$parameter
  3  where name = 'recyclebin'
  4  /

NAME       VALUE      ISDEFAULT
---------- ---------- ---------
recyclebin on         FALSE

SQL>

That’s because an ISDEFAULT value of FALSE simply means that the parameter has been included in the parameter file. You can see what I mean on the screen print below (click to enlarge it). On the left is a SQL*Plus session which lists those parameters with ISDEFAULT = FALSE and on the right is the database’s parameter file. You will see that they match:

FLOOR and CEIL

FLOOR returns the smallest integer less than or equal to the expression in brackets. CEIL returns the smallest integer greater than or equal to the expression in brackets. I tried to catch them out but failed:

SQL> select floor(-3.2), ceil(-3.2)

  2  from dual;

FLOOR(-3.2) CEIL(-3.2)

----------- ----------

         -4         -3

1 row selected.

SQL> select floor(-0), ceil(-0)

  2  from dual;

 FLOOR(-0)   CEIL(-0)

---------- ----------

         0          0

1 row selected.

SQL> select floor(0/0) from dual;

select floor(0/0) from dual

              *

ERROR at line 1:

ORA-01476: divisor is equal to zero
  

SQL> select ceil(0/0) from dual;

select ceil(0/0) from dual

             *

ERROR at line 1:

ORA-01476: divisor is equal to zero
  

SQL> select floor(0/1), ceil(0/1)

  2  from dual;

FLOOR(0/1)  CEIL(0/1)

---------- ----------

         0          0

1 row selected.

SQL> select floor(2.5*2.5), ceil(2.5*2.5)

  2  from dual;

FLOOR(2.5*2.5) CEIL(2.5*2.5)

-------------- -------------

             6             7

1 row selected.

SQL> select floor(12.0), ceil(12.0)

  2  from dual;

FLOOR(12.0) CEIL(12.0)

----------- ----------

         12         12

1 row selected.

SQL> select floor(14.6), ceil(14.6)

  2  from dual;

FLOOR(14.6) CEIL(14.6)

----------- ----------

         14         15

1 row selected.

SQL>

Sorting (Part 1)

The statement below joins DBA_TABLES to itself without a join condition. This forces Oracle to sort a large amount of data:

SQL> show user
USER is "ANDREW"
SQL> select a.* from dba_tables a, dba_tables b
  2  order by a.owner, a.table_name
  3  /

The statement will take some time to return any output. While it is executing, you can see the sort space usage increasing by querying V$SORT_USAGE every few seconds like this:

SQL> show user
USER is "SYSTEM"
SQL> select blocks from v$sort_usage
  2  where username = 'ANDREW';

no rows selected

SQL> exec sys.dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

SQL> select blocks from v$sort_usage
  2  where username = 'ANDREW';

    BLOCKS
----------
      4480

SQL> exec sys.dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

SQL> select blocks from v$sort_usage
  2  where username = 'ANDREW';

    BLOCKS
----------
      8320

SQL> exec sys.dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

SQL> select blocks from v$sort_usage
  2  where username = 'ANDREW';

    BLOCKS
----------
     12160

SQL>
 
To get the values in bytes, simply multiply the figures by the DB_BLOCK_SIZE entry from V$PARAMETER.

Calculating Averages (Part 1)

You can calculate average values with the AVG function as shown below. The second statement double checks the answer:

SQL> select avg(num_rows)

  2  from dba_tables

  3  where num_rows is not null

  4  /

AVG(NUM_ROWS)

-------------

   153051.912

SQL> select sum(num_rows) / count(*)

  2  from dba_tables

  3  where num_rows is not null

  4  /

SUM(NUM_ROWS)/COUNT(*)

----------------------

            153051.912

SQL>

Footnote: Since writing this post I have noticed that it is slightly misleading.
Go to part 2 for details.

NUMWIDTH

You can use NUMWIDTH to prevent large numbers being displayed in scientific notation. First check the current value of NUMWIDTH:

SQL> show numwidth

numwidth 10

SQL>

Next, display a large number. As it is more than NUMWIDTH digits long, it appears in scientific notation:

SQL> select sum(bytes) from dba_data_files;

SUM(BYTES)

----------

1.2863E+11

SQL>

This will need a NUMWIDTH of 12 or more to be displayed as a normal number:

SQL> set numwidth 12

SQL> l

  1* select sum(bytes) from dba_data_files

SQL> /

  SUM(BYTES)

------------

128625672192

SQL>

SQL*Plus PASSWORD Command

Most of you will know how to use the ALTER USER command to change your password:

SQL> conn system/manager1@test10
Connected.
SQL> alter user system identified by manager2;
User altered.
SQL> conn system/manager2@test10
Connected.
SQL>

But SQL*Plus also has a PASSWORD command which allows you to do the same thing. In the example below, the user tries it out. Oracle asks for the old password then asks for the new password twice. Neither the old nor the new password are displayed on the screen:

SQL> password
Changing password for SYSTEM
Old password: ********
New password: ********
Retype new password: ********
Password changed
SQL> conn system/manager3@test10
Connected.
SQL>

An administrator can use the PASSWORD command to change another user's password. In this case, Oracle does not ask for the old password. The example below is from Oracle 11g release 2:

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> password andrew

Changing password for andrew

New password:

Retype new password:

Password changed

SQL>

How to ensure that the index for a constraint does not go in your default tablespace

This is a simple example, which I did for a colleague several years ago. He wanted to ensure that the index for a constraint he was creating did not go in his default tablespace (I cannot remember why he wanted to do this). I don't think it needs any more explanation than that:

SQL> alter table emp disable constraint pk;

Table altered.

SQL> alter table emp enable constraint pk

  2  using index tablespace appl_data02;

Table altered.

SQL> select tablespace_name from user_segments

  2  where segment_name = 'PK';

TABLESPACE_NAME

------------------------------

APPL_DATA02

SQL> select default_tablespace from user_users;

DEFAULT_TABLESPACE

------------------------------

APPL_DATA01

SQL>

Slashes in SQL*Plus and PL/SQL

I had a problem recently with a large package, which used to compile without errors on
Oracle 9. When I moved it to Oracle 10 and tried to compile it there, it produced several pages of SP2-0734 messages. The package had about a dozen statements something like this:

SELECT
NUMERICAL_EXPRESSION1
         /
NUMERICAL_EXPRESSION2
FROM
SOME_TABLE_OR_VIEW
ETC

Each statement was doing a division and the slash was on a line by itself several places from the start of that line. These statements were interpreted correctly under Oracle 9 but not by Oracle 10 nor 11. I did an experiment with an SQL statement doing a simple division and ran it on Oracle 9, 10 and 11. You can see the results on the screen print below (click to enlarge it):

In the top left hand corner, the Oracle 9 version works correctly.

In the bottom row, the Oracle 10 and 11 versions both fail as soon as they see the slash in column 8. I believe Oracle sees it as an instruction to run the SQL typed so far rather than a division symbol.

In the top right hand corner, the Oracle 10 version is rewritten with the slash at the end of
line 1 and it works correctly again.
  
I then went back to the package with the compilation errors, changed the dozen or so division statements in the same way and the compilation errors disappeared.

When I had some spare time I raised a Service Request with Oracle. They confirmed that it was bug 4219339 and also provided a different workaround, setting SQLPLUSCOMPATIBILITY to a value less than 9. That worked too, as you can see in the example below, which I ran on Oracle 10.2.0.1.0 on Linux:

SQL> select 1
  2         /
select 1
       *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> set sqlpluscompatibility 8.1.7
SQL> select 1
  2         /
  3         2
  4  from dual;

       1/2
----------
        .5

SQL>

Adding an Extra Control File

When you create an Oracle database, a control file is set up at the same time. This records the physical structure of the database. You only need one control file and this might be enough for a test database. Here is one that I created earlier. The data files, redo log files and control file are all in the same directory:

C:\Test10>dir
 Volume in drive C has no label.
 Volume Serial Number is 18E4-B972

 Directory of C:\Test10

24/12/2010  16:34    <DIR> .
24/12/2010  16:34    <DIR> ..
24/12/2010  16:40         7,061,504 ORIG.CTL
24/12/2010  16:33        52,429,312 REDO01.LOG
24/12/2010  16:33        52,429,312 REDO02.LOG
24/12/2010  16:40        52,429,312 REDO03.LOG
24/12/2010  16:40       241,180,672 SYSAUX01.DBF
24/12/2010  16:40       492,838,912 SYSTEM01.DBF
24/12/2010  16:28        20,979,712 TEMP01.DBF
24/12/2010  16:40        26,222,592 UNDOTBS01.DBF
24/12/2010  16:40        5,251,072 USERS01.DBF
               9 File(s)   950,822,400 bytes
               2 Dir(s) 10,268,143,616 bytes free

C:\Test10>
 
You can see in v$controlfile that this is the only control file on the machine:

SQL> col name format a40
SQL> select name from v$controlfile;

NAME
----------------------------------------
C:\TEST10\ORIG.CTL

SQL>
 
Oracle recommends that you should have two or more control files on different disks and you should always follow this advice for any important database. You can add extra control files as follows. First you need to close the database:

C:\oracle\product\10.2.0\db_1\dbs>set ORACLE_SID=TEST10

C:\oracle\product\10.2.0\db_1\dbs>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 24 17:38:53 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Then you need to edit the parameter file and change the control_files entry as shown below. The first line shows the original entry commented out by a #. The second line shows the new entry with an extra file name added on the end:

#control_files='C:\Test10\orig.ctl'
control_files='C:\Test10\orig.ctl','G:\Test10\extra.ctl'

Then, at the OS level, copy the original control file into the new location:

C:\Test10>copy ORIG.CTL G:\Test10\EXTRA.CTL
        1 file(s) copied.

C:\Test10>

Open the database again:

C:\oracle\product\10.2.0\db_1\dbs>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 24 18:04:08 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup pfile=initTEST10.ora
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250452 bytes
Variable Size             167775084 bytes
Database Buffers          440401920 bytes
Redo Buffers                2940928 bytes
Database mounted.
Database opened.
SQL>

Then if you look at v$controlfile again, the new control file will appear in the list:

SQL> l
  1* select name from v$controlfile
SQL> /

NAME
----------------------------------------
C:\TEST10\ORIG.CTL
G:\TEST10\EXTRA.CTL

SQL>

Drop Database

In version 10g, Oracle introduced the drop database SQL statement. This removes all datafiles, online redo log files, control files and server parameter files. I created a database with the Database Configuration Assistant so that I could try out this new command. To make it easier to demonstrate, I put the datafiles, online redo log files and control files all in the same directory:

SQL> col file_name format a55
SQL> select file_name from dba_data_files;

FILE_NAME
-------------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\USERS01.DBF
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\SYSAUX01.DBF
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\UNDOTBS01.DBF
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\SYSTEM01.DBF

SQL> select file_name from dba_temp_files;

FILE_NAME
-------------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\TEMP01.DBF
  
SQL> col member format a50
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\REDO03.LOG
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\REDO02.LOG
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\REDO01.LOG

SQL> col name format a55
SQL> select name from v$controlfile;

NAME
-------------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\CONTROL01.CTL
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\CONTROL02.CTL
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\CONTROL03.CTL

SQL>

It’s easier to see in the screen print below (click to enlarge it):
   

The server parameter file was in a different directory:

SQL> col value format a50
SQL> select value from v$parameter where name = 'spfile';

VALUE
--------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILETEST10.ORA

SQL>

Then I tried to drop the database:

SQL> set lines 60
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

SQL>

I followed the instructions and tried again: 

C:\Documents and Settings\Andrew>set ORACLE_SID=TEST10

C:\Documents and Settings\Andrew>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 24 09:35:34 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250452 bytes
Variable Size 176163692 bytes
Database Buffers 432013312 bytes
Redo Buffers 2940928 bytes
Database mounted.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode

SQL>

Then putting the database into restricted session mode appeared to have the desired effect:

SQL> alter system enable restricted session;

System altered.

SQL> drop database;

Database dropped.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

The datafiles were removed:

C:\Documents and Settings\Andrew\test10>dir
Volume in drive C has no label.
Volume Serial Number is 18E4-B972

Directory of C:\Documents and Settings\Andrew\test10

24/12/2010 10:01 <DIR> .
24/12/2010 10:01 <DIR> ..
              0 File(s) 0 bytes
              2 Dir(s) 11,539,795,968 bytes free

C:\Documents and Settings\Andrew\test10>

And so was the spfile:

C:\oracle\product\10.2.0\db_1\dbs>dir
Volume in drive C has no label.
Volume Serial Number is 18E4-B972

Directory of C:\oracle\product\10.2.0\db_1\dbs

24/12/2010 10:00 <DIR> .
24/12/2010 10:00 <DIR> ..
              0 File(s) 0 bytes
              2 Dir(s) 11,539,415,040 bytes free

C:\oracle\product\10.2.0\db_1\dbs>