_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.

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>

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>

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 >

ORA-01507

I had to put a database into archivelog mode today. The problem when I do this is that I can never remember if the database has to be mounted or not. The answer is that it does. If it isn't, you get an ORA-01507:

ORACLE10 > sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Dec 6 15:23:22 2011

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

Connected to an idle instance.

 

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area  176160768 bytes

Fixed Size                  2028688 bytes

Variable Size             134220656 bytes

Database Buffers           33554432 bytes

Redo Buffers                6356992 bytes

SQL> alter database archivelog

  2  /

alter database archivelog

*

ERROR at line 1:

ORA-01507: database not mounted

 

SQL> alter database mount

  2  /

 

Database altered.

 

SQL> alter database archivelog

  2  /

 

Database altered.

 

SQL> alter database open

  2  /

 

Database altered.

 

SQL> select log_mode from v$database

  2  /

 

LOG_MODE

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

ARCHIVELOG

 

SQL>

Oracle Database 11g Release 2 Upgrade Seminar

I went to an Oracle seminar at CPC Venues in London recently. It explained how and why you should move to Oracle 11g release 2. The speakers were Mike Appleyard, John Nangle and Peter Alsop. It was extremely well done and, if you get the chance, you should go on it.

When I returned to work, I asked for a copy of the slides from the presentation and they sent me a link to download them a few hours later.

I decided to try out a new feature, which allows you to use solid state disk storage to extend your database's buffer cache. I then found a test database running on 11g release 2. Unfortunately I did not have a solid state disk to hand so I had to make do with an ordinary one.

To use this feature, you need to add two new parameters to your init.ora file. This is the first one:

db_flash_cache_file='/export/home/oracle/andrew/ssd'

It gives the name of a file on a solid state disk, which will be used as the buffer cache extension. Oracle creates it if it does not exist.

The second parameter gives the size of the file:

db_flash_cache_size=10m

Then I bounced the database:

11gR2 > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 2 18:24:58 2011

Copyright (c) 1982, 2009, 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> startup force

ORA-01078: failure in processing system parameters

ORA-12427: invalid input value for db_flash_cache_size parameter

SQL>

This failure did not come as much of a surprise as the notes from the presentation suggested a size of 120 gigabytes! I tried again with a size of 100m and the database opened OK.

This is the file which Oracle created:

 

11gR2 > pwd

/export/home/oracle/andrew

11gR2 > ls -l ssd

-rw-r-----   1 oracle   dba      104857600 Dec  2 18:08 ssd

11gR2 >

That's as far as I can go with this new feature for now. Once I have a solid state disk, I will try it out properly and report my findings.

In the meantime, I will read through the slides from the presentation, try out some other new features and let you know how I get on.