FIXED_DATE and LAST_ANALYZED

I tested this on Oracle 11.2.0.1. You need to be careful when looking at the LAST_ANALYZED column in USER_TABLES. I created a table and removed the FIXED_DATE parameter from the database:

 

SQL> create table t1 (c1 number)

  2  /

 

Table created.

 

SQL> alter system set fixed_date = none

  2  /

 

System altered.

 

SQL>

 

…then I used DBMS_STATS.GATHER_TABLE_STATS and the old ANALYZE command to create statistics for the table. Both of them set the LAST_ANALYZED column in USER_TABLES to the current date:

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'ORACLE',tabname=>'T1');

 

PL/SQL procedure successfully completed.

 

SQL> select last_analyzed

  2  from user_tables

  3  where table_name = 'T1'

  4  /

 

LAST_ANALYZED

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

10-SEP-14

 

SQL> analyze table t1 compute statistics

  2  /

 

Table analyzed.

 

SQL> select last_analyzed

  2  from user_tables

  3  where table_name = 'T1'

  4  /

 

LAST_ANALYZED

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

10-SEP-14

 

SQL>

 

I set FIXED_DATE to 25^th December 2014 and tried again:

 

SQL> alter system set fixed_date = '25-DEC-2014'

  2  /

 

System altered.

 

SQL>

 

DBMS_STATS.GATHER_TABLE_STATS set the LAST_ANALYZED column to the new FIXED_DATE value. I believe this is not what Oracle intended and may be a result of bug 8892343, which states:

 

The value of the LAST_ANALYZED column in USER_TABLES views is affected by the setting of the FIXED_DATE parameter when it should be independent of that value.

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'ORACLE',tabname=>'T1');

 

PL/SQL procedure successfully completed.

 

SQL> select last_analyzed

  2  from user_tables

  3  where table_name = 'T1'

  4  /

 

LAST_ANALYZED

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

25-DEC-14

 

SQL>

 

…whereas the old ANALYZE command set the LAST_ANALYZED column to the underlying system date:

 

SQL> analyze table t1 compute statistics

  2  /

 

Table analyzed.

 

SQL> select last_analyzed

  2  from user_tables

  3  where table_name = 'T1'

  4  /

 

LAST_ANALYZED

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

10-SEP-14

 

SQL>

DBMS_SESSION.SET_SQL_TRACE

This was tested on Oracle 11.1. In previous posts, I have looked at enabling tracing. You can start and stop a trace of your current session like this:

SQL> alter session set sql_trace = true;

 

Session altered.

 

SQL> alter session set sql_trace = false;

 

Session altered.

 

SQL>

You can start and stop tracing an entire instance as follows:

SQL> alter system set sql_trace = true;

 

System altered.

 

SQL> alter system set sql_trace = false;

 

System altered.

 

SQL>

You can also start and stop tracing an instance by setting the sql_trace initialization parameter.

 

Going through some old notes, I saw a different way of tracing your current session and decided to try it out (the first statement is just to make it easy to see the trace file):

SQL> l

  1* alter session set tracefile_identifier = 'ANDREW'

SQL> /

 

Session altered.

 

SQL> exec dbms_session.set_sql_trace(true);

 

PL/SQL procedure successfully completed.

 

SQL> select * from dual

  2  /

 

D

-

X

 

SQL> exec dbms_session.set_sql_trace(false);

 

PL/SQL procedure successfully completed.

 

SQL>

Then I looked in the directory specified by the user_dump_dest parameter and saw the associated trace file below:

Solaris > ls *ANDREW.trc

PQEDPT1_ora_27267_ANDREW.trc

Solaris >

ORA-30036

I was recently using an Oracle 9 database. It had an undo tablespace with 1 datafile, which was set to autoextend. However, it could not do so as the file system it was on was full. This produced an ORA-30036 error. I decided to demonstrate this on Oracle 11.1.0.6.0 running on Windows XP. First I created a 500k undo tablespace. By the time I ran the SQL below, it had grown to 647168 bytes:

SQL> l
  1  select file_name, bytes, autoextensible
  2  from dba_data_files
  3* where tablespace_name = 'UNDOTBS2'
SQL> /

FILE_NAME                 BYTES AUT
-------------------- ---------- ---
A:\UNDOTBS2.DBF          647168 YES

SQL>

Notice that it is on the A: drive. This is the 3.5” floppy disk drive (remember them?). It contained a 720k disk. I did this on purpose to stop the undo tablespace growing any bigger than that. I told the database to use this new undo tablespace:

SQL> alter system set undo_tablespace = undotbs2;

System altered.

SQL>

Then I tried to create a table but Oracle returned an ORA-30036:

SQL> create table fred
  2  as select * from dba_tables
  3  /
as select * from dba_tables
                 *
ERROR at line 2:
ORA-00604: error occurred at recursive SQL level 1
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'

SQL>

The following errors appeared in the alert log:

Sun Sep 09 23:43:01 2012
GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file c:\app\andrew\diag\rdbms\orcl\orcl\trace\orcl_j003_2916.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'
Sun Sep 09 23:44:48 2012
Thread 1 advanced to log sequence 37
  Current log# 1 seq# 37 mem# 0: C:\APP\ANDREW\ORADATA\ORCL\REDO01.LOG
Mon Sep 10 00:06:16 2012
ORA-1652: unable to extend temp segment by 8 in tablespace                 UNDOTBS2 
Mon Sep 10 00:13:05 2012

And when I looked at the floppy disk in Windows, it was full:

If this happens to you, you need to add an extra datafile to the undo tablespace (on a file system with enough free space). Alternatively, you can extend the file system, although in this case it was not possible as a floppy disk has a finite size.

ORA-00990

This was tested on Oracle 11. You can grant roles and system privileges in the same statement. But if you want to include object privileges, you need to use a new GRANT statement:

SQL> grant dba, alter system,

  2  execute on sys.dbms_lock

  3  to andrew

  4  /

grant dba, alter system,

      *

ERROR at line 1:

ORA-00990: missing or invalid privilege

SQL> grant dba, alter system

  2  to andrew

  3  /

Grant succeeded.

SQL> grant execute on sys.dbms_lock

  2  to andrew

  3  /

Grant succeeded.

SQL>

Advanced Replication (Part 1 - Setting up the Master Destination Databases)

Advanced Replication allows you to propagate changes among 2 or more databases. Each database is called a Master Destination database and 1 database is called a Master Definition database.

This worked example, tested in Oracle 9, sets it up in 2 databases.

The first is referred to as DB1.WORLD in the tnsnames.ora file and has a global name of NODE1.WORLD. This database is the Master Definition database as well as being one of the Master Destination databases.

The second is referred to as DB2.WORLD in the tnsnames.ora file and has a global name of NODE2.WORLD. This database is one of the Master Destination databases.

  

This post explains how to set up Advanced Replication in each Master Destination database:

The first few parts are run as user SYS:

SQL> conn / as sysdba

Connected.

SQL>

Before you can use Advanced Replication, you need to check that it has been installed in each database involved. If it has, the database will have a table called SYSTEM.REPCAT$_REPCAT:

SQL> desc system.repcat$_repcat

Name                       Null?    Type

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

GOWNER                     NOT NULL VARCHAR2(30)

SNAME                      NOT NULL VARCHAR2(30)

MASTER                              VARCHAR2(1)

STATUS                              NUMBER(38)

SCHEMA_COMMENT                      VARCHAR2(80)

FLAVOR_ID                           NUMBER

FLAG                                RAW(4)

SQL>

It will also have several objects with names beginning with REPCAT in the SYSTEM schema:

SQL> select count(*) from dba_objects

  2  where owner = 'SYSTEM'

  3  and substr(object_name,1,6) = 'REPCAT'

  4  /

  COUNT(*)

----------

       143

SQL>

If Advanced Replication has not been installed, you must run $ORACLE_HOME/rdbms/admin/catrep.sql. However, this should not be necessary for Oracle 9 and above as catproc.sql now calls catrep.sql.

Parameter job_queue_processes must be >= 1 otherwise no jobs will run. You can change it without bouncing the database if necessary:

SQL> select value from v$parameter

  2  where name = 'job_queue_processes'

  3  /

VALUE

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

0

SQL> alter system set job_queue_processes = 1

  2  scope = both

  3  /

System altered.

SQL> select value from v$parameter

  2  where name = 'job_queue_processes'

  3  /

VALUE

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

1

SQL>

Some sites suggest that _job_queue_interval needs to be <= the smallest job interval. Other sites say that this does not apply for Oracle 9 or above. Either way, I am going to leave it alone because:

1. I no longer use any version below Oracle 9.
2. This is a hidden parameter, which should not be changed without speaking to Oracle first.
3. It is measured in seconds and looks as if it is small enough already:

SQL> select ksppinm, ksppstvl

  2  from x$ksppi a, x$ksppsv b

  3  where a.indx = b.indx

  4  and ksppinm like '_job%'

  5  /

KSPPINM                   KSPPSTVL

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

_job_queue_interval       5

SQL>

Parameter global_names must be set to true. It can be changed without bouncing the database:

SQL> select value from v$parameter

  2  where name = 'global_names'

  3  /

VALUE

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

FALSE

SQL> alter system set global_names = true

  2  scope = both

  3  /

System altered.

SQL> select value from v$parameter

  2  where name = 'global_names'

  3  /

VALUE

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

TRUE

... and you need to ensure that the global name is correct:

SQL> alter database rename global_name to node1.world

  2  /

Database altered.

SQL> select global_name from global_name

  2  /

GLOBAL_NAME

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

NODE1.WORLD

SQL>

Each Master Destination database needs to be linked to every other Master Destination database with a public database link:

SQL> create public database link node2.world

  2  using 'db2.world'

  3  /

Database link created.

SQL>

You need an administrator to send and receive data:

SQL> create user replication_administrator

  2  identified by replication_administrator

  3  default tablespace user_data

  4  temporary tablespace temporary_data

  5  quota unlimited on user_data

  6  /

User created.

SQL>

You allow the administrator to send data to other databases as follows:

SQL> execute dbms_defer_sys.register_propagator -

> (username => 'replication_administrator');

PL/SQL procedure successfully completed.

SQL>

... and you allow the administrator to receive data like this:

SQL> grant execute any procedure to replication_administrator

  2  /

Grant succeeded.

SQL>

Next you allow the administrator to administer replication groups and schemas:

SQL> execute dbms_repcat_admin.grant_admin_any_repgroup -

> (userid => 'replication_administrator');

PL/SQL procedure successfully completed.

SQL> execute dbms_repcat_admin.grant_admin_any_schema -

> (username => 'replication_administrator');

PL/SQL procedure successfully completed.

SQL>

The administrator also needs to lock and comment tables:

SQL> grant lock any table, comment any table

  2  to replication_administrator

  3  /

Grant succeeded.

SQL>

The administrator does the remaining steps. First he creates private database links from each master destination database to all the others:

SQL> conn replication_administrator/replication_administrator

Connected.

SQL> create database link node2.world

  2  connect to replication_administrator

  3  identified by replication_administrator

  4  using 'db2.world'

  5  /

Database link created.

SQL>

Then a job is set up to send data to each master destination sites:

SQL> execute dbms_defer_sys.schedule_push -

> (destination   => 'node2.world',   -

>  next_date     => sysdate+1/24/60,   -

>  interval      => 'sysdate+1/24/60', -

>  stop_on_error => false,             -

>  delay_seconds => 0,                 -

>  parallelism   => 1);

PL/SQL procedure successfully completed.

SQL>

Finally a job is set up to delete data after it has been successfully sent:

SQL> execute dbms_defer_sys.schedule_purge -

> (next_date => sysdate+1/24/60,         -

>  interval  => 'sysdate+1/24/60');

PL/SQL procedure successfully completed.

SQL>