TRUNCATE TABLE SYS.AUD$ Gives ORA-00942

I saw that the SYSTEM tablespace in an Oracle 11.2 database was getting quite big so I checked the size of SYS.AUD$:
 
C:\Users\AJ0294094>sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 16 13:20:54 2014
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, (more...)

ORA-01511, ORA-01512 and ORA-27037

This was run on an Oracle 11 database. I used dbca to create a database recently and, by mistake, I put redo03a.log in the wrong directory (ent_red01 with a zero instead of ent_redo1 with an o). dbca had no way of knowing this was wrong and simply created the directory name I specified in the GUI. You can see what I mean in the output below:

In the database: 

  1  (more...)

_single_process Initialization Parameter

The _single_process initialization parameter allows you to start an instance in single process mode. As it begins with an underscore, you should only use it when Oracle tell you to. When I tried it on Oracle 10 and Oracle 11 test databases, they promptly fell over. However, I was able to get it to work on Oracle 9, although it produced an ORA-00600. I then left the SYSDBA session open: 

Oracle 9: sqlplus '/ (more...)

AFTER DELETE Triggers do not Run After a Truncate

In this example, I want to show that Oracle does not execute an AFTER DELETE trigger after doing a TRUNCATE. I tested it on Oracle 11.2. First I created a table:

SQL> create table tab1 (my_name varchar2(10))
  2  /

Table created. 

SQL>

Then I inserted a row into it: 

SQL> insert into tab1 values ('Andrew')
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL> select * (more...)

ORA-01031 Creating a View on a Table Accessed via a Role

This example shows that you cannot create a view on a table which you access via a role. It was tested on Oracle 11. First I created a role:

SQL> create role andrews_role
  2  /

Role created. 

SQL>

Then I created a user called ANDREW1, who will create a table:

SQL> create user andrew1
  2  identified by reid1
  3  default tablespace users
  4  quota unlimited on users

The Recycle Bin does not Store Tables from the SYSTEM Tablespace

Oracle introduced the recyclebin in version 10. This post shows that tables in the SYSTEM tablespace do not go into the recyclebin when they are dropped. It was tested on Oracle 11.2.0.2.7. First I created a table in the USERS tablespace: 

SQL> conn / as sysdba
Connected.
SQL> create table tab1
  2  (col1 number)
  3  tablespace users
  4  /
 
Table created.

SQL>

Then I dropped the (more...)

impdp REMAP_DATAFILE Parameter

I was asked to copy one database into another, which I usually do by copying the datafiles then recreating the control file. However, on this occasion, although both databases were on Oracle 11.2.0.4, the machine hosting the input database was X86 whereas the machine hosting the output database was Sparc. Data Pump seemed to be a better option.
 
I created the output database using dbca, which set up the SYSTEM (more...)

ORA-32771 and ORA-32772

Bigfile tablespaces were introduced, and this example was run, in Oracle 10. They have only one datafile. Usually, this will be very large, although this is not the case in the example below:
 
SQL> create bigfile tablespace andrew
  2  datafile '/tmp/andrew.dbf' size 10m
  3  /
 
Tablespace created.
 
SQL>
 
There are two ways to extend an ordinary tablespace. You can add an extra datafile or you can extend an (more...)

RMAN Copy Adds Hidden Parameters

Some testers had a problem with an application running against an Oracle 11.2.0.1.0 database. It kept failing with ORA-03114 and/or ORA-24909 messages. The only clues were that production was copied into test around 6 weeks ago, production was OK but test was not. I looked the errors up on the Internet, saw a suggestion that I should compare initialization parameters in both databases and decided to give this a try. I (more...)

ORA-12988

Tables in the SYS schema are usually created by Oracle but you can create them yourself. If they are created by Oracle you should not be trying to change them. If they are created by you, you should seriously consider moving them into another schema. Either way, if you try to drop a column from a table owned by SYS, you get an ORA-12988. You can see what I mean in the example below, (more...)

Unusable Indexes

I tested this post on an Oracle 9 database. It shows that if you have an index which is marked as UNUSABLE, truncating the underlying table makes it VALID again.

First I created a table:

SQL> CREATE TABLE ANDREWS_TABLE AS
  2  SELECT * FROM DBA_TABLES
  3  /

Table created.

SQL>

Then I added an index to the table:

SQL> CREATE INDEX ANDREWS_INDEX
  2  ON ANDREWS_TABLE(TABLE_NAME)
  3  /

Index created.

SQL>

... (more...)

ORA-01700 and/or ORA-01711

I tested these on Oracle 11.2. You can grant a privilege like this:

SQL> grant create table to andrew
  2  /
 
Grant succeeded.

SQL>

... but if you try to grant a privilege more than once in the same grant statement, you get an ORA-01711:

SQL> grant create table,
  2  select any table,
  3  create table to andrew
  4  /
create table to andrew
*
ERROR at line 3:

Invisible Indexes (Part 2)

In Oracle 11.1.0.6.0, you could not run DBMS_STATS against an invisible index or a table with an invisible index. I found 2 workarounds. You could run the old ANALYZE command or you could make the index visible on a temporary basis:

SQL> create table my_table
  2  as select * from dba_tables
  3  /

Table created.

SQL> create index my_index
  2  on my_table(owner) invisible
  3  /

Index created.

Invisible Indexes (Part 1)

Oracle introduced INVISIBLE indexes in version 11. The example below creates an INVISIBLE index, makes it VISIBLE then makes it INVISIBLE again. The VISIBILITY column in USER_INDEXES is used to check that each change has worked:

SQL> col visibility format a10
SQL> create table my_table
  2  as select * from dba_tables
  3  /

Table created.

SQL> create index my_index
  2  on my_table(owner)
  3  invisible
  4  /

Index created.

SQL> select (more...)

ORA-28010

I wondered what would happen if you tried to do a password expire on an externally identified user so I tried it out on an Oracle 11.2 database. As you might expect, it failed:
 
SQL> alter user system identified externally
  2  /
 
User altered.
 
SQL> alter user system password expire
  2  /
alter user system password expire
*
ERROR at line 1:
ORA-28010: cannot expire external or global accounts

SQL92_SECURITY

I tested the examples in this post in two Oracle 11.2 databases. In the first example, Andrew creates a table, inserts a row into it then grants UPDATE privilege on it to Fred, who is then able to work out the value in the table, without changing it permanently, using a series of UPDATE and ROLLBACK statements: 

SQL> conn / as sysdba
Connected.
SQL> select value from v$parameter
  2  where name = (more...)

ORA-01446

This was tested on Oracle 11.2. First I created a table and inserted 2 identical values in it:

SQL> create table tab1 (col1 number)
  2  /
 
Table created.
 
SQL> insert into tab1 values (1)
  2  /
 
1 row created.
 
SQL> insert into tab1 values (1)
  2  /
 
1 row created.

SQL>

Then I looked at the rowids of the values I had inserted:

SQL> select rowid, (more...)

Does Having TIMED_STATISTICS on Affect Performance?

I was reading through an Oracle publication called Oracle 9i Database Performance Planning recently and saw the following:

In order to get meaningful database statistics, the TIMED_STATISTICS parameter must be enabled for the database instance. The performance impact of having TIMED_STATISTICS enabled is minimal compared to instance performance. The performance improvements and debugging value of a complete set of statistics make this parameter crucial to effective performance analysis.

I decided to check this out on (more...)

ORA-02019

I had not seen this problem for a long time so, when it happened again recently, it took me a while to see the cause. I therefore decided to record it for future reference. First I created a database link:

ORCL /export/home/oracle/andrew > sqlplus /

SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 17 11:55:54 2013

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

Connected to:
Oracle Database 11g Enterprise Edition (more...)

Database Links and Case Sensitive Passwords

I connected to an Oracle 11 database:

Oracle 11: sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 23 18:22:40 2014
 
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>
 
I created a user with a (more...)