Friday, December 30, 2011

_trace_files_public

In an earlier post, I looked at the tracefile_identifier parameter. This post, tested on an Oracle 9 database, uses it in conjunction with _trace_files_public. First create a trace file without setting the _trace_files_public parameter:
 
  1* alter session set tracefile_identifier = before
SQL> /
 
Session altered.
 
SQL> select value from v$parameter
  2  where name = '_trace_files_public'
  3  /
 
no rows selected
 
SQL> alter session set sql_trace = true
  2  /
 
Session altered.
 
SQL> select sysdate from dual
  2  /
 
SYSDATE
---------
30-DEC-11
 
SQL> alter session set sql_trace = false
  2  /
 
Session altered.
 
SQL>
 
This creates a trace file with permissions of 640 so, if you are not in the dba group, you will not be able to read it:
 
TEST9 > ls -ltr
total 8
-rw-r----- 1 oracle dba 2303 Dec 30 11:46 mvltst_ora_59105_BEFORE.trc
TEST9 >
 
Set _trace_files_public to true and repeat the test:
 
SQL> alter session set tracefile_identifier = after
  2  /
 
Session altered.
 
SQL> select value from v$parameter
  2  where name = '_trace_files_public'
  3  /
 
VALUE
--------------------
TRUE
 
SQL> alter session set sql_trace = true
  2  /
 
Session altered.
 
SQL> select sysdate from dual
  2  /
 
SYSDATE
---------
30-DEC-11
 
SQL> alter session set sql_trace = false
  2  /
 
Session altered.
 
SQL>
 
This creates a trace file with permissions of 644, which anybody can read:
 
TEST9 > ls -ltr
total 24
-rw-r----- 1 oracle dba 2303 Dec 30 11:46 mvltst_ora_59105_BEFORE.trc
-rw-r--r-- 1 oracle dba 2302 Dec 30 12:07 mvltst_ora_56368_AFTER.trc
TEST9 >
 
This can be useful on a test machine, where developers want to read their trace files without DBA intervention.

Hidden Parameters

Oracle has several hidden initialisation parameters. These have names which begin with an underscore. You can see them using the following query, which I ran on an Oracle 9 database:
 
SQL> l
  1  select ksppinm
  2  from   SYS.X$KSPPI
  3* where  substr(KSPPINM,1,1) = '_'
SQL> /
 
 
KSPPINM
-------------------------------------------------------
_trace_files_public
_latch_recovery_alignment
_spin_count
_latch_miss_stat_sid
_max_sleep_holding_latch
_max_exponential_sleep
_use_vector_post
_latch_class_0
_latch_class_1
_latch_class_2
_latch_class_3
 
Etc.
 
KSPPINM
-------------------------------------------------------
_xsolapi_sql_use_bind_variables
_xsolapi_sql_prepare_stmt_cache_size
_xsolapi_sql_result_set_cache_size
_xsolapi_debug_output
_xsolapi_cursor_use_row_cache
_xsolapi_cursor_max_rows_to_cache_per_req
_xsolapi_cursor_max_time_for_partial_cache
_xsolapi_source_trace
 
613 rows selected.
 
SQL>
 
What does it mean when we say they are hidden?  We can see the answer by comparison with an ordinary parameter e.g. sql_trace:
 
SQL> l
  1  select name, value, isdefault
  2  from v$parameter
  3* where name = 'sql_trace'
SQL> /
 
NAME       VALUE      ISDEFAULT
---------- ---------- ---------
sql_trace  FALSE      TRUE
 
SQL>
 
In this case, sql_trace is set to its default value of FALSE but it still has an entry in V$PARAMETER. A hidden parameter, on the other hand, does not have an entry in V$PARAMETER:
 
  1  select name, value, isdefault
  2  from v$parameter
  3* where name = '_trace_files_public'
SQL> /
 
no rows selected
 
SQL>
 
... unless it has been set on purpose. The query below was run on a different Oracle 9 database, which had _trace_files_public set in its parameter file:
 
SQL> l
  1  select name, value, isdefault
  2  from v$parameter
  3* where name = '_trace_files_public'
SQL> /
 
NAME                 VALUE      ISDEFAULT
-------------------- ---------- ---------
_trace_files_public  TRUE       FALSE
 
SQL>
 
Documentation for hidden parameters is not freely available. Oracle will tell you when to implement them, usually when they are replying to a service request. Advice from Oracle to implement a hidden parameter relates to a given database running on a specific Oracle version. I went to an 11g release 2 seminar run by Oracle recently. They discussed upgrade policy there and said that hidden parameters should be removed before an upgrade.

Thursday, December 29, 2011

ORA-28008

This is a simple example using the replace clause of the alter user statement. First log in as an administrator and create a user:
  
SQL> conn system/manager
Connected.
SQL> grant create session to andrew
  2  identified by reid1
  3  /

Grant succeeded.
 
SQL>


Now change the user's password. The replace clause allows you to provide the old password. In this case the correct value is given:


SQL> alter user andrew
  2  identified by reid2
  3  replace reid1
  4  /

User altered.

SQL>

Change the user's password again. This time the replace clause supplies the incorrect password:
  
SQL> alter user andrew
  2  identified by reid3
  3  replace reid1
  4  /

User altered.

SQL>
  
The change was accepted. There are 2 reasons for this:
  1. Oracle does not store a password's unencrypted value.
  2. An administrator may not know the value of an old password.
Now restore the original password:
  
SQL> alter user andrew
  2  identified by reid1
  3  /

User altered.

SQL> 

  
Then connect as the new user and repeat the test:
  
SQL> conn andrew/reid1
Connected.
SQL> alter user andrew
  2  identified by reid2
  3  replace reid1
  4  /

User altered.

SQL> alter user andrew
  2  identified by reid3
  3  replace reid1
  4  /
alter user andrew
*
ERROR at line 1:
ORA-28008: invalid old password

SQL> 

The 2nd change was not accepted. There are a couple of reasons for this:
  1. A user should know the current value of his password.
  2. If Oracle changed a password when the old password was supplied incorrectly, there would be nothing to stop somebody else changing your password if you left your terminal unattended. 
Although Oracle does not store unencrypted passwords, it can still check old passwords if they are supplied. It does this by encrypting them and comparing that encrypted value with the encrypted value it has on file.

Automatic Parallel Execution

In 11g release 2, Oracle introduced the following new parameters:
 
parallel_degree_policy
parallel_min_time_threshold
 
The default setting of parallel_degree_policy is manual. With this value, the automatic parallel execution of SQL statements is disabled. This is how Oracle worked before 11g release 2.
 
Setting this parameter to auto enables this functionality. Oracle then parses an SQL statement, works out its execution plan and estimates how long it will take. If this value is greater than  parallel_min_time_threshold seconds, the statement runs in parallel. You can see what difference this makes in the example below, which runs the same query twice.
 
Automatic parallel execution is turned off for the first run:
 
SQL> select value from v$parameter
  2  where name = 'parallel_min_time_threshold'
  3  /
 
VALUE
--------------------
AUTO
 
SQL> select value from v$parameter
  2  where name = 'parallel_degree_policy'
  3  /
 
VALUE
--------------------
MANUAL
 
SQL> set timing on
SQL> select count(*) from
  2  (select a.table_name from
  3   dba_tables a, dba_tables b)
 4  /
 
  COUNT(*)
----------
   9247681
 
Elapsed: 00:02:00.41
SQL> set timing off
 
And the query takes just over 2 minutes. Automatic parallel execution is then turned on for the second run:
 
SQL> alter session set
  2  parallel_min_time_threshold = 1
  3  /
 
Session altered.
 
SQL> alter session set
  2  parallel_degree_policy = 'AUTO'
  3  /
 
Session altered.
 
SQL> set timing on
SQL> select count(*) from
  2  (select a.table_name from
  3   dba_tables a, dba_tables b)
  4  /
 
  COUNT(*)
----------
   9247681
 
Elapsed: 00:00:37.15
SQL> set timing off
 
And the query takes less than 40 seconds.

Long to LOB Conversion

I went on an Oracle 11g release 2 seminar recently. They said that LONG data types are still supported but that Oracle recommends converting them to LOB (i.e. CLOB or NCLOB). I’m not totally convinced by this as they are still using LONG columns themselves e.g. in table SYS.VIEW$, which is one of the underlying tables for the DBA_VIEWS view:
 
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 29 14:27:22 2011
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> desc sys.view$
Name                       Null?    Type
-------------------------- -------- ------------------
OBJ#                       NOT NULL NUMBER
AUDIT$                     NOT NULL VARCHAR2(38)
COLS                       NOT NULL NUMBER
INTCOLS                    NOT NULL NUMBER
PROPERTY                   NOT NULL NUMBER
FLAGS                      NOT NULL NUMBER
TEXTLENGTH                          NUMBER
TEXT                                LONG
 
SQL>
 
Converting a LONG to a CLOB is easy:
 
SQL> create table andrews_table
  2  (name varchar2(10),
  3   address long)
  4  /
 
Table created.
 
SQL> insert into andrews_table values
  2  ('Noddy', '10 High St, Anytown')
  3  /
 
1 row created.
 
SQL> alter table andrews_table modify (address clob)
  2  /
 
Table altered.
 
SQL> desc andrews_table
Name                       Null?    Type
-------------------------- -------- ------------------
NAME                                VARCHAR2(10)
ADDRESS                             CLOB
 
SQL> select * from andrews_table
  2  /
 
NAME       ADDRESS
---------- --------------------
Noddy      10 High St, Anytown
 
SQL>
 
But you need to be certain that you want to do this as there is no going back:
 
SQL> alter table andrews_table modify (address long)
  2  /
alter table andrews_table modify (address long)
                                  *
ERROR at line 1:
ORA-22859: invalid modification of columns
 
SQL>

Long Datatypes

I was looking at LONG datatypes in an Oracle 9 database recently. There are a couple of restrictions with them. You cannot do a create table as select or CTAS on a table or view which contains a LONG column:
  
SQL> desc dba_views
Name                       Null?    Type
-------------------------- -------- ------------------
OWNER                      NOT NULL VARCHAR2(30)
VIEW_NAME                  NOT NULL VARCHAR2(30)
TEXT_LENGTH                         NUMBER
TEXT                                LONG
TYPE_TEXT_LENGTH                    NUMBER
TYPE_TEXT                           VARCHAR2(4000)
OID_TEXT_LENGTH                     NUMBER
OID_TEXT                            VARCHAR2(4000)
VIEW_TYPE_OWNER                     VARCHAR2(30)
VIEW_TYPE                           VARCHAR2(30)
SUPERVIEW_NAME                      VARCHAR2(30)

SQL> create table andrews_views
  2  as select * from dba_views
  3  /
as select * from dba_views
          *
ERROR at line 2:
ORA-00997: illegal use of LONG datatype

SQL>


And you cannot have more than 1 LONG column in a table:

SQL> create table andrews_table
  2  (long1 long)
  3  /


Table created.

SQL> alter table andrews_table add
  2  (long2 long)
  3  /
(long2 long)
*
ERROR at line 2:
ORA-01754: a table may contain only one column of type LONG

SQL>

Wednesday, December 28, 2011

CPU_COUNT

According to Oracle’s own documentation for 10g release 1:
 
On most platforms, Oracle automatically sets the value of CPU_COUNT to the number of CPUs available to your Oracle instance. Do not change the value of CPU_COUNT.
 
The following test was done on an Oracle 9 database running on Tru64:
 
Tru64 > psrinfo -n
number of processors on system = 1
Tru64 > psrinfo -v
Status of processor 0 as of: 12/02/11 15:19:04
  Processor has been on-line since 02/19/2011 17:29:11
  The alpha EV6.7 (21264A) processor operates at 618 MHz,
  has a cache size of 2097152 bytes,
  and has an alpha internal floating point processor.
 
Tru64 > sqlplus '/ as sysdba'
 
SQL*Plus: Release 9.2.0.5.0 - Production on Fri Dec 2 15:19:28 2011
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
 
SQL> col value format a10
SQL> col isdefault format a9
SQL> select value, isdefault from v$parameter
  2  where name = 'cpu_count'
  3  /
 
VALUE      ISDEFAULT
---------- ---------
1          TRUE
 
SQL>
 
The following test was done on an Oracle 9 database running on Linux:
 
Linux > cat /proc/cpuinfo | grep -i 'processor' | wc -l
2
Linux > sqlplus '/ as sysdba'
 
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Dec 2 15:40:12 2011
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
SQL> col value format a10
SQL> col isdefault format a9
SQL> select value, isdefault from v$parameter
  2  where name = 'cpu_count'
  3  /
 
VALUE      ISDEFAULT
---------- ---------
2          TRUE
 
SQL>
 
The following test was done on an Oracle 10 database running on Solaris:
 
Solaris > psrinfo -p -v
The physical processor has 2 virtual processors (0 16)
  UltraSPARC-IV+ (portid 0 impl 0x19 ver 0x22 clock 1500 MHz)
The physical processor has 2 virtual processors (2 18)
  UltraSPARC-IV+ (portid 2 impl 0x19 ver 0x22 clock 1500 MHz)
Solaris > sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Dec 2 15:52:40 2011
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
 
SQL> col isdefault format a9
SQL> col value format a5
SQL> select value, isdefault from v$parameter
  2  where name = 'cpu_count'
  3  /
 
VALUE ISDEFAULT
----- ---------
4     TRUE
 
SQL>
 
So it looks as if it works as intended. I went to an Oracle seminar recently, where they said that cpu_count works differently in 11g release 2. Once I have investigated, I will cover this in a future post.

ORA-01950

I was in a hurry to get something finished today. I created a user and specified its default tablespace. I logged in as that user then tried to create a table. This failed with an ORA-01950 as the user had no quota on its default tablespace. I granted the user a quota of 5 megabytes and it was then able to create the table. You can see what I mean in the example, which was tested on an Oracle 9 database:
 
SQL> conn / as sysdba
Connected.
SQL> create user andrew
  2  identified by reid
  3  default tablespace user_data
  4  /
 
User created.
 
SQL> grant create session, create table
  2  to andrew
  3  /
 
Grant succeeded.
 
SQL> conn andrew/reid
Connected.
SQL> create table my_table
  2  (col1 varchar2(10))
  3  /
create table my_table
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USER_DATA'
 
SQL> conn / as sysdba
Connected.
SQL> alter user andrew
  2  quota 5m on user_data
  3  /
 
User altered.
 
SQL> conn andrew/reid
Connected.
SQL> create table my_table
  2  (col1 varchar2(10))
  3  /
 
Table created.
 
SQL>

Saturday, December 24, 2011

Oracle Managed Files (Part 1)

Oracle managed files were introduced in version 9. You can implement them using initialisation parameters. These can be set:
  1. In the init.ora or server parameter file.
  2. In an alter session or alter system statement.
The parameters specify directories which Oracle should use for datafiles in subsequent DDL statements such as create tablespace etc. You can see what I mean in the example below, which I ran on Oracle 9.2.0.4.0:

First, specify the directory where files should be created using the db_create_file_dest parameter:

SQL> alter session set db_create_file_dest = '/mnt/redhat';
 
Session altered.
 
SQL>
 
Now create a tablespace. Oracle is managing the creation of datafiles so no filename is required:
 
SQL> create tablespace andrew
  2  datafile size 10m
  3  /
 
Tablespace created.
 
SQL>
 
Check the name(s) of the datafile(s) in the tablespace. There is only one and Oracle has created it in the location specified by the db_create_file_dest parameter:
 
SQL> l
  1  select file_name, bytes from dba_data_files
  2* where tablespace_name = 'ANDREW'
SQL> /
 
FILE_NAME                                     BYTES
---------------------------------------- ----------
/mnt/redhat/o1_mf_andrew_7fh2qylt_.dbf     10485760
 
SQL>
 
Add a datafile to the tablespace and check the name(s) of the datafile(s) again:
 
SQL> alter tablespace andrew add datafile size 5m
  2  /
 
Tablespace altered.
 
SQL> select file_name, bytes from dba_data_files
  2  where tablespace_name = 'ANDREW'
  3  /
 
FILE_NAME                                     BYTES
---------------------------------------- ----------
/mnt/redhat/o1_mf_andrew_7fh2qylt_.dbf     10485760
/mnt/redhat/o1_mf_andrew_7fh2zndg_.dbf      5242880
 
SQL>
 
Look at the files at the Linux level:
 
TEST9 > pwd
/mnt/redhat
TEST9 > ls -1
o1_mf_andrew_7fh2qylt_.dbf
o1_mf_andrew_7fh2zndg_.dbf
TEST9 >
 
Drop the tablespace:
 
SQL> drop tablespace andrew
  2  /
 
Tablespace dropped.
 
SQL>
 
Oracle deletes managed files once they are no longer required.Check that the files have gone at the Linux level:
 
TEST9 > pwd
/mnt/redhat
TEST9 > ls -l
total 0
TEST9 >

Thursday, December 08, 2011

ORA-01940

If you try to drop a user who is currently connected to the database, you get an ORA-01940 message. You can see what I mean in the example below: 

First, Andrew connects to the database:

SQL> conn andrew/reid
Connected.
SQL>
 
Then simultaneously, in another session, SYSTEM tries to drop user Andrew and Oracle returns an error message:
 
SQL> conn system/manager
Connected.
SQL> drop user andrew;
drop user andrew
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
 
SQL>