Taking a Datafile Offline in NOARCHIVELOG Mode

I was reading a colleague’s copy of Oracle RMAN 11g Backup and Recovery by Robert Freeman. It said that you could not take a datafile offline if the database was in NOARCHIVELOG mode. I don’t know much about taking datafiles offline but this seemed reasonable so I gave it a try in Oracle 12.1. First I checked the database’s LOG_MODE: 

SQL> select log_mode from v$database
  2  /
 
LOG_MODE
------------
NOARCHIVELOG

SHUTDOWN TRANSACTIONAL

If you run this command, Oracle waits until all users have committed or rolled back any outstanding transactions before closing the database. To test this out in Oracle 12.1, I tried to close a database from a session with an outstanding transaction. This failed with an ORA-01097. Once I had committed the transaction, I was able to close the database successfully:

SQL> show user
USER is "SYS"
SQL> create table tab1(col1 number)
  2  (more...)

Wrap Utility

This is an example using the wrap utility, which allows you to hide stored code. Software suppliers can use it to prevent customers stealing their PL/SQL. First I created a table:
 
SQL> create table name_list
  2  as select 'ANDREW' name from dual
  3  /
 
Table created.
 
SQL> select * from name_list
  2  /
 
NAME
------
ANDREW
 
SQL>
 
Then I created a procedure to add names to (more...)

The USER Keyword

Here are a couple of examples with the USER keyword, which I tested on Oracle 11.2. You can use it after the SHOW command or in a SELECT statement as follows. It returns the name of the user running the current session: 

SQL> conn fred/bloggs
Connected.
SQL> show user
USER is "FRED"
SQL> select user from dual
  2  /
 
USER
------------------------------
FRED

SQL>

You can also include it in a WHERE (more...)

ORA-00031, ORA-03113 and ORA-03114

This was tested on Oracle 11.2. I had a session in the middle of a long-running operation: 

SQL> conn user1/user1
Connected.
SQL> exec dbms_lock.sleep(3600);
 
I tried to kill it like this but Oracle gave me an ORA-00031 instead:
 
SQL> select sid, serial# from v$session
  2  where username = 'USER1'
  3  /
 
       SID    SERIAL#
---------- ----------
         7         62
 
SQL> alter system kill session '7,62'

FAST Refresh of Materialized View Returns ORA-12004

I tried to do a FAST refresh of a materialized view in Oracle 11.2 but it failed with an ORA-12004:
 
SQL> begin
  2  dbms_mview.refresh('ebase.m_gridpoint2',method=>'F');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12004: REFRESH FAST cannot be used for materialized view
"EBASE"."M_GRIDPOINT2"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 2

DBMS_STATS Causes ORA-00600 [15851]

Uncategorized
| Apr 3, 2013
I noticed this in an Oracle 11.2.0.1.0 database. DBMS_STATS failed with an ORA-00600 and the first argument was [15851]. On investigation, it seemed to have something to do with the fact that the table had a function based index:
 
SQL> SELECT COUNT(*) FROM DBA_INDEXES