Datapump Does not Export Permissions on Objects Owned by SYS

This post was sponsored by Technimove 

I was reminded recently that Datapump does not export permissions on objects owned by SYS so I decided to write a post about it for my blog. It was tested on an Oracle 11.2.0.1 database. First I created a user called USER1:

SQL> conn / as sysdba
Connected.
SQL> create user user1
  2  identified by user1
  3  /

User created.

SQL> grant create session to user1
  2  /

Grant succeeded.

SQL>

I logged in as USER1 and showed that it did not have execute permission on SYS.DBMS_LOCK:

SQL> conn user1/user1
Connected.
SQL> exec dbms_lock.sleep(1);
BEGIN dbms_lock.sleep(1); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> 

I granted permission then logged in as USER1 again to check it had worked:

SQL> conn / as sysdba
Connected.
SQL> grant execute on dbms_lock to user1
  2  /

Grant succeeded.

SQL> conn user1/user1
Connected.
SQL> exec dbms_lock.sleep(1);

PL/SQL procedure successfully completed.

SQL>

I used expdp to export the schema with the following parameters:

content=all
directory=andrews_datapump_dir
dumpfile=andrew.dmp
logfile=andrew_exp.log
schemas=user1

I used impdp to import the dumpfile into a different schema with the following parameters:

content=all
directory=andrews_datapump_dir
dumpfile=andrew.dmp
logfile=andrew_imp.log
remap_schema=user1:user2 

…but when I logged in as USER2, it did not have execute permission on SYS.DBMS_LOCK:

SQL> conn user2/user1
Connected.
SQL> exec dbms_lock.sleep(1);
BEGIN dbms_lock.sleep(1); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>

Deferred Segment Creation not Supported for Partitioned Tables in Oracle 11.2.0.1

This post was sponsored by IMPERVA

I tried to create a partitioned table with deferred segment creation in an Oracle 11.2.0.1 database.

First I tried to do so explicitly but this did not work:

SQL> create table partitioned_table

  2  (refno number)

  3  segment creation deferred

  4  partition by range (refno)

  5  (partition partition1 values less than (10)

  6   tablespace users,

  7   partition partition2 values less than (maxvalue)

  8   tablespace users)

  9  /

create table partitioned_table

*

ERROR at line 1:

ORA-14223: Deferred segment creation is not supported

for this table

SQL>

Then I tried to set the appropriate parameter at session level but when I created a partitioned table, I found that it had 2 segments:

SQL> alter session set deferred_segment_creation = true

  2  /

Session altered.

SQL> create table partitioned_table

  2  (refno number)

  3  partition by range (refno)

  4  (partition partition1 values less than (10)

  5   tablespace users,

  6   partition partition2 values less than (maxvalue)

  7   tablespace users)

  8  /

Table created.

SQL> select count(*) from dba_segments

  2  where segment_name = 'PARTITIONED_TABLE'

  3  /

  COUNT(*)

----------

         2

SQL>

However, when I logged in to an Oracle 11.2.0.4 database, I found that I was able to create a partitioned table with deferred segment creation. (I understand that this was introduced in Oracle 11.2.0.2 but have no database to check this on):

SQL> create table partitioned_table

  2  (refno number)

  3  segment creation deferred

  4  partition by range (refno)

  5  (partition partition1 values less than (10)

  6   tablespace users,

  7   partition partition2 values less than (maxvalue)

  8   tablespace users)

  9  /

Table created.

SQL>

As you would expect, the table had no segments:

SQL> select count(*) from dba_segments

  2  where segment_name = 'PARTITIONED_TABLE'

  3  /

  COUNT(*)

----------

         0

SQL>

...and, as I added data, partitions were only created when they were actually needed:

SQL> insert into partitioned_table values (1)

  2  /

1 row created.

SQL> select count(*) from dba_segments

  2  where segment_name = 'PARTITIONED_TABLE'

  3  /

  COUNT(*)

----------

         1

SQL> insert into partitioned_table values (10)

  2  /

1 row created.

SQL> select count(*) from dba_segments

  2  where segment_name = 'PARTITIONED_TABLE'

  3  /

  COUNT(*)

----------

         2

SQL>

How to Automatically Trace a User's Sessions

This post was sponsored by arcserve 

It shows how you can use a logon trigger to automatically trace all sessions for a given user. This can be useful where an application is launched from a desktop but fails before the DBA has had time to identify the SID and SERIAL# to start tracing the session. I tested it in an Oracle 11.1.0.6 database running on Windows 8.

First I created a database user:

SQL> create user ford
  2  identified by fiesta
  3  /

User created.

SQL> grant create session,
  2        create trigger,
  3        alter session to ford
  4  /

Grant succeeded.

SQL>

Then I logged in as the user and created a trigger:

SQL> conn ford/fiesta
Connected.
SQL> create or replace trigger immediate_trace
  2  after logon on ford.schema
  3  begin
  4  execute immediate 'alter session set sql_trace = true';
  5  end;
  6  /

Trigger created.

SQL>

I logged in again and ran some SQL:

SQL> conn ford/fiesta
Connected.
SQL> select sysdate from dual
  2  /

SYSDATE
---------
03-SEP-16

SQL>

This produced a trace file ready for further analysis e.g. by tkprof. It included, among other statements, the following:

=====================
PARSING IN CURSOR #3 len=66 dep=1 uid=89 oct=47 lid=89 tim=16846197344563 hv=836160175 ad='b719a518' sqlid='21fcha0sxdkpg'
begin
execute immediate 'alter session set sql_trace = true';
end;
END OF STMT
EXEC #3:c=0,e=23597,p=0,cr=0,cu=0,mis=1,r=1,dep=1,og=1,tim=16846197344558
=====================

and

=====================
PARSING IN CURSOR #3 len=24 dep=0 uid=89 oct=3 lid=89 tim=16846207456189 hv=2343063137 ad='b71c8490' sqlid='7h35uxf5uhmm1'
select sysdate from dual
END OF STMT
PARSE #3:c=0,e=307,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=16846207456188
EXEC #3:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=16846207456292
FETCH #3:c=0,e=6,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=16846207456323
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'
FETCH #3:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=16846207502363

*** 2016-09-03 18:30:07.555
XCTEND rlbk=0, rd_only=1
=====================

Incidentally, if you find a trigger like this and you are not sure what it is for, you can find out as follows:

SQL> conn / as sysdba
Connected.
SQL> l
  1  select description
  2  from dba_triggers
  3  where owner = 'FORD'
  4* and trigger_name = 'IMMEDIATE_TRACE'
SQL> /

DESCRIPTION
--------------------------------------------------
immediate_trace
after logon on ford.schema

SQL> l
  1  select trigger_body
  2  from dba_triggers
  3  where owner = 'FORD'
  4* and trigger_name = 'IMMEDIATE_TRACE'
SQL> /

TRIGGER_BODY
----------------------------------------------------------
begin
execute immediate 'alter session set sql_trace = true';
end;

SQL> 

So far so good but the observant among you may notice that the above is simply a replacement for a couple of posts I published some time ago. You may be wondering what has changed since then. Well, I need to trace sessions for a database user again but this time:

(1) The database produces lots of other trace files which I don't want to look at.
(2) Several OS users log in with this database user but I only want trace files to be produced for one of them, who happens to be called Tobias.

I modified the logon trigger as follows:

SQL> conn ford/fiesta
Connected.
SQL> l
  1  create or replace trigger immediate_trace
  2  after logon on ford.schema
  3  declare
  4    osuser varchar2(200);
  5  begin
  6    select sys_context('USERENV', 'OS_USER')
  7      into osuser from dual;
  8    if osuser = 'NEWPC\Tobias'
  9      then
 10      execute immediate
 11        'alter session set tracefile_identifier = FORD';
 12      execute immediate
 13        'alter session set sql_trace = true';
 14    end if;
 15* end;
SQL> /

Trigger created.

SQL>

Then using my usual OS user i.e. Andrew, I logged into the database user:

SQL> conn ford/fiesta
Connected.
SQL> l
  1  select sys_context('USERENV', 'OS_USER')
  2* from dual
SQL> /

SYS_CONTEXT('USERENV','OS_USER')
--------------------------------------------------

NEWPC\Andrew

SQL> select 'Andrew was here' from dual
  2  /

'ANDREWWASHERE'
---------------
Andrew was here

SQL>

This did not create a trace file.

I went to the Windows 8 account creation screen and set up a user called Tobias. This isn't a Microsoft blog so I'm not going to show you how to do that. I connected to Windows with this new OS user and logged into the database with it:

SQL> conn ford/fiesta
Connected.
SQL> l
  1  select sys_context('USERENV', 'OS_USER')
  2* from dual
SQL> /

SYS_CONTEXT('USERENV','OS_USER')
--------------------------------------------------

NEWPC\Tobias

SQL> select 'Tobias was here' from dual
  2  /

'TOBIASWASHERE'
---------------
Tobias was here

SQL>

This created a trace file, which I could easily see among the other trace files as it was called:

orcl_ora_2548_FORD

... and when I looked inside it, I could see that it had been created by Tobias, not by Andrew:

=====================
PARSING IN CURSOR #4 len=34 dep=0 uid=89 oct=3 lid=89 tim=16850923590446 hv=3733526637 ad='b2ff1f78' sqlid='80cq653g8k63d'
select 'Tobias was here' from dual
END OF STMT
PARSE #4:c=0,e=343,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=16850923590444
EXEC #4:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=16850923590540
FETCH #4:c=0,e=5,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=16850923590565
STAT #4 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'
FETCH #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=16850923590715

*** 2016-09-03 20:06:37.565
XCTEND rlbk=0, rd_only=1
=====================

SUBSTR Versus LIKE in Oracle 11.2

I was reading an old SQL tuning book which was printed in 2002. It said that a where clause with like could often use an index whereas a similar clause using substr could not. I wondered if this might still be the case in an Oracle 11.2.0.1 database. To find out, I created a table:

SQL> conn andrew/reid
Connected.
SQL> create table tab1 as
  2  select table_name from dba_tables
  3  /

Table created.

SQL>

... and made sure it had plenty of data:

SQL> begin
  2  for a in 1..12 loop
  3  insert into tab1 select * from tab1;
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select count(*) from tab1
  2  /

  COUNT(*)
----------
  13348864

SQL>

I added an extra row which I could look for later:

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

1 row created.

SQL>

...added an index to help find it:

SQL> create index ind1 on tab1(table_name)
  2  /

Index created.

SQL>

...and collected statistics:

SQL> exec dbms_stats.gather_table_stats(-
> ownname=>'andrew', -
> tabname=>'tab1', -
> cascade=>true);

PL/SQL procedure successfully completed.

SQL>

I used like to find the row and it took 0.39 seconds:

SQL> alter session set sql_trace = true
  2  /

Session altered.

SQL> set timing on
SQL> select count(*) from tab1
  2  where table_name like 'DAILY%'
  3  /

  COUNT(*)
----------
         1

Elapsed: 00:00:00.39

SQL>

... but when I used substr, it took 28.79 seconds:

SQL> select count(*) from tab1
  2  where substr(table_name,1,5) = 'DAILY'
  3  /

  COUNT(*)
----------
         1

Elapsed: 00:00:28.79

SQL> set timing off
SQL> alter session set sql_trace = false
  2  /

Session altered.

SQL>

I ran the trace file through tkprof to see how Oracle had executed the SQL. The statement which used substr had done a full table scan:

********************************************************************************

select count(*) from tab1
where substr(table_name,1,5) = 'DAILY'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      7.16      28.78      38936      38940          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      7.16      28.78      38936      38940          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 8891  (ANDREW)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=38940 pr=38936 pw=0 time=0 us)
      1   TABLE ACCESS FULL TAB1 (cr=38940 pr=38936 pw=0 time=0 us cost=9169 size=2135824 card=133489)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   SORT (AGGREGATE)
      1    TABLE ACCESS   MODE: ANALYZED (FULL) OF 'TAB1' (TABLE)

********************************************************************************

...but the statement which used like had used the index:

********************************************************************************

select count(*) from tab1
where table_name like 'DAILY%'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.01          3          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02          3          3          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 8891  (ANDREW)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3 pr=3 pw=0 time=0 us)
      1   INDEX RANGE SCAN IND1 (cr=3 pr=3 pw=0 time=0 us cost=19 size=65792 card=4112)(object id 211183)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   SORT (AGGREGATE)
      1    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'IND1' (INDEX)

********************************************************************************

Making a Hot Backup and Doing an Incomplete Recovery

This post shows how to do a hot backup followed by an incomplete recovery. I ran it on an Oracle 11.2.0.4 test database. First I checked that the database was in ARCHIVELOG mode:

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>

Then I decided where to copy the hot backup.

The directory listing below shows a sub-directory called backup. This contains a cold backup I made earlier in case the test goes horribly wrong and I need to start again.

After this, you can see the database’s 3 control files. In a real life situation, these would be on separate physical disks but this is only a test so having them in the same place is OK.

Next comes a directory called hot_backup. The hot backup will go in here.

Finally you can see the database’s redo logs and datafiles:

GBASRDB1 /database/ANDREW/DB1 > ls -l
total 6585680
drwxr-xr-x   2 oracle   dba         4096 Jun  3 17:51 backup
-rw-r-----   1 oracle   dba      11517952 Aug  9 16:59 control01.ctl
-rw-r-----   1 oracle   dba      11517952 Aug  9 16:59 control02.ctl
-rw-r-----   1 oracle   dba      11517952 Aug  9 16:59 control03.ctl
drwxr-xr-x   2 oracle   dba         4096 Jun  7 18:40 hot_backup
-rw-r-----   1 oracle   dba      52429312 Aug  9 16:50 redo01a.log
-rw-r-----   1 oracle   dba      52429312 Aug  9 16:50 redo02a.log
-rw-r-----   1 oracle   dba      52429312 Aug  9 16:59 redo03a.log
-rw-r-----   1 oracle   dba      796925952 Aug  9 16:55 sysaux01.dbf
-rw-r-----   1 oracle   dba      796925952 Aug  9 16:58 system01.dbf
-rw-r-----   1 oracle   dba      524296192 Aug  9 16:50 temp01.dbf
-rw-r-----   1 oracle   dba      524296192 Aug  9 16:55 undotbs01.dbf
-rw-r-----   1 oracle   dba      524296192 Aug  9 16:50 users01.dbf
GBASRDB1 /database/ANDREW/DB1 >

I identified the files to backup:

SQL> l
  1  select tablespace_name, file_name
  2  from dba_data_files
  3* order by 1,2
SQL> /

TABLESPACE_NAME FILE_NAME
--------------- -----------------------------------
SYSAUX          /database/ANDREW/DB1/sysaux01.dbf
SYSTEM          /database/ANDREW/DB1/system01.dbf
UNDOTBS1        /database/ANDREW/DB1/undotbs01.dbf
USERS           /database/ANDREW/DB1/users01.dbf

SQL>

... and checked the sequence number of the current redo log:

SQL> l
  1  select group#, status, sequence#
  2  from v$log
  3* order by 1
SQL> /

    GROUP# STATUS            SEQUENCE#
---------- ---------------- ----------
         1 INACTIVE               1423
         2 CURRENT                1424
         3 INACTIVE               1422

SQL>

I put the database into hot backup mode:

SQL> alter database begin backup
  2  /

Database altered.

SQL>

I copied the datafiles into the hot_backup directory:

GBASRDB1 /database/ANDREW/DB1 > cp sysaux01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 > cp system01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 > cp undotbs01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 > cp users01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 >

I created a marker table:

SQL> create table system.andrew_was_here(col1 number)
  2  /

Table created.

SQL> insert into system.andrew_was_here values(1234567)
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL>

I took the database out of hot backup mode:

SQL> alter database end backup
  2  /

Database altered.

SQL> 

I archived any outstanding redo and forced a log switch:

SQL> alter system archive log current
  2  /

System altered.

SQL>

I checked the sequence number of the current redo log:

SQL> l
  1  select group#, status, sequence#
  2  from v$log
  3* order by 1
SQL> /

    GROUP# STATUS            SEQUENCE#
---------- ---------------- ----------
         1 INACTIVE               1423
         2 ACTIVE                 1424
         3 CURRENT                1425

SQL>

All the redo needed for recovery should be in log no 1424 so I copied this into the hot_backup directory: 

GBASRDB1 /database/ANDREW/DB1 > cp 1_1424_913830195.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 > 

Finally, I took a backup of the controlfile:

SQL> alter database backup controlfile
  2  to '/database/ANDREW/DB1/hot_backup/bkup.ctl'
  3  /

Database altered.

SQL>

As a first example, I decided to restore to this hot backup so I deleted the database’s datafiles, control files and online redo logs. Obviously, this loses any changes made after the backup:

GBASRDB1 /database/ANDREW/DB1 > rm *
rm: backup is a directory
rm: hot_backup is a directory
GBASRDB1 /database/ANDREW/DB1 > ls
backup      hot_backup
GBASRDB1 /database/ANDREW/DB1 > 

Then I closed the database:

GBASRDB1 /database/ANDREW/DB1 > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 10 17:39:59 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL>

I restored the files from the hot_backup directory and made sure there were 3 control files again:

GBASRDB1 /database/ANDREW/DB1 > cp hot_backup/* .
GBASRDB1 /database/ANDREW/DB1 > ls
1_1424_913830195.dbf  bkup.ctl              sysaux01.dbf          undotbs01.dbf
backup                hot_backup            system01.dbf          users01.dbf
GBASRDB1 /database/ANDREW/DB1 > mv bkup.ctl control01.ctl
GBASRDB1 /database/ANDREW/DB1 > cp control01.ctl control02.ctl
GBASRDB1 /database/ANDREW/DB1 > cp control01.ctl control03.ctl
GBASRDB1 /database/ANDREW/DB1 >

I mounted the database:

GBASRDB1 /database/ANDREW/DB1 > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 10 18:02:36 2016

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2252448 bytes
Variable Size             322961760 bytes
Database Buffers          188743680 bytes
Redo Buffers                7979008 bytes
Database mounted.
SQL>

I recovered the database using the backup controlfile, applying all the archived redo at my disposal:

SQL> recover database using backup controlfile until cancel
ORA-00279: change 4101278 generated at 08/09/2016 17:47:29 needed for thread 1
ORA-00289: suggestion : /database/ANDREW/DB1/1_1424_913830195.dbf
ORA-00280: change 4101278 for thread 1 is in sequence #1424

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 4102126 generated at 08/09/2016 18:06:23 needed for thread 1
ORA-00289: suggestion : /database/ANDREW/DB1/1_1425_913830195.dbf
ORA-00280: change 4102126 for thread 1 is in sequence #1425
ORA-00278: log file '/database/ANDREW/DB1/1_1424_913830195.dbf' no longer
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>

I opened the database:

SQL> alter database open resetlogs
  2  /

Database altered.

SQL> 

... and checked that my table was still there: 

SQL> select * from system.andrew_was_here
  2  /

      COL1
----------
   1234567

SQL> 

In the process, Oracle recreated the online redo log files for me:

SQL> select group#, sequence#, status
  2  from v$log
  3  /

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          0 UNUSED
         3          0 UNUSED

SQL>

ORA-39710 and ORA-00704

I tried to use dbua to upgrade a database from Oracle 11.2.0.4 to Oracle 12.1.0.2. Part way through, my PC lost connection with the UNIX server hosting the database. I tried to connect to the database but got an ORA-39710 so I forced the database to close with shutdown abort:

NLFINUT1 /export/home/oracle > sqlplus /

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 18 14:03:08 2016

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

ERROR:
ORA-39710: only connect AS SYSDBA is allowed when OPEN in UPGRADE mode

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL>

A colleague restored the database for me. dbua had updated /var/opt/oracle/oratab to show the new database version so I changed it back to 11.2.0.4:

NLFINUT1:/oracle/app/oracle/product/11.2.0.4:N

However, I forgot to rerun . oraenv to pick up the old Oracle software so when I tried to open the database, I got an ORA-00704:

NLFINUT1 /export/home/oracle > sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 18 14:13:38 2016

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  650117120 bytes
Fixed Size                  2917400 bytes
Variable Size             230693864 bytes
Database Buffers          411041792 bytes
Redo Buffers                5464064 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier
Process ID: 21064
Session ID: 212 Serial number: 60960

SQL>

I reran . oraenv then I was able to open the database:

NLFINUT1 /export/home/oracle > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 18 14:52:08 2016

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  622338048 bytes
Fixed Size                  2184112 bytes
Variable Size             327158864 bytes
Database Buffers          285212672 bytes
Redo Buffers                7782400 bytes
Database is aangekoppeld.
Database is geopend.
SQL>

(It is used in the Netherlands so some of the messages are in Dutch.)

ORA-01109

I read that you cannot take a tablespace offline if the database is only mounted so I decided to test this in an Oracle 11.2.0.4 database. I mounted the database and tried to take the USERS tablespace offline. Oracle returned an ORA-01109. I opened the database then I was able to take the tablespace offline:

 

SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2252448 bytes
Variable Size             310378848 bytes
Database Buffers          201326592 bytes
Redo Buffers                7979008 bytes
Database mounted.
SQL> alter tablespace users offline
  2  /
alter tablespace users offline
*
ERROR at line 1:
ORA-01109: database not open

SQL> alter database open
  2  /

Database altered.

SQL> alter tablespace users offline
  2  /

Tablespace altered.

SQL> alter tablespace users online
  2  /

Tablespace altered.

SQL>

How to See When an Oracle Role Was Created

You can get the date and time a role was created from the CTIME column in SYS.USER$. You can see what I mean in the example below, which I tested in an Oracle 11.2.0.4 database. First I created a role between DATE_AND_TIME1 and DATE_AND_TIME2. As they were the same, to the nearest second, the role must have been created at 16:11:05 on 27th May 2016.

I then waited 5 seconds and queried the CTIME column in SYS.USER$ for BLAH. This showed 16:11:05 on 27th May 2016 i.e. the date and time when the role was created:

SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS')
  2  date_and_time1
  3  from dual
  4  /

DATE_AND_TIME1
------------------
27-MAY-16 16:11:05

SQL> create role blah
  2  /

Role created.

SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS')
  2  date_and_time2
  3  from dual
  4  /

DATE_AND_TIME2
------------------
27-MAY-16 16:11:05

SQL> exec dbms_lock.sleep(5);

PL/SQL procedure successfully completed.

SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS')
  2  date_and_time3
  3  from dual
  4  /

DATE_AND_TIME3
------------------
27-MAY-16 16:11:10

SQL> select to_char(ctime,'DD-MON-YY HH24:MI:SS')
  2  role_created
  3  from sys.user$
  4  where name = 'BLAH'
  5  /

ROLE_CREATED
------------------
27-MAY-16 16:11:05

SQL>

%TYPE Declaration Gives PLS-00302

A developer reported a problem with a %TYPE declaration which was returning PLS-00302 in an Oracle 10 database. The cause turned out to be a variation on a problem which I have already reported. However, as it took me some time to work out, I have reproduced it below. First I created a user, called USER1, who would own a table:

SQL> conn / as sysdba

Connected.

SQL> create user user1

  2  identified by user1

  3  default tablespace users

  4  quota 10m on users

  5  /

 

User created.

 

SQL> grant create session, create table

  2  to user1

  3  /

 

Grant succeeded.

 

SQL>

Then I created a user, called USER2, to declare a variable using %TYPE, basing it on a column in the table created by USER1:

SQL> create user user2 identified by user2

  2  /

 

User created.

 

SQL> grant create session, create synonym

  2  to user2

  3  /

 

Grant succeeded.

 

SQL> 

USER1 created a table called TAB1 and allowed USER2 to see it:

SQL> conn user1/user1

Connected.

SQL> create table tab1

  2  (col1 number)

  3  /

 

Table created.

 

SQL> grant select on tab1 to user2

  2  /

 

Grant succeeded.

 

SQL> 

USER2 declared a variable called BLAH using %TYPE to base it on column COL1 in table TAB1. This was successful:

SQL> conn user2/user2

Connected.

SQL> declare

  2  blah user1.tab1.col1%type;

  3  begin

  4  null;

  5  end;

  6  /

 

PL/SQL procedure successfully completed.

 

SQL> 

USER2 created a synonym called USER1. N.B. It is not a good idea for an object in one schema to have the same name as a schema elsewhere in the database.

SQL> create synonym user1 for user_tables

  2  /

 

Synonym created.

 

SQL> 

USER2 tried to declare a variable called BLAH as before. This time, Oracle probably thought that USER1 referred to the synonym created in the previous step rather than the username created at the start of the post. The declaration therefore failed:

SQL> declare

  2  blah user1.tab1.col1%type;

  3  begin

  4  null;

  5  end;

  6  /

blah user1.tab1.col1%type;

           *

ERROR at line 2:

ORA-06550: line 2, column 12:

PLS-00302: component 'TAB1' must be declared

ORA-06550: line 2, column 6:

PL/SQL: Item ignored

 

SQL>