A Simple Example With V$BACKUP

I tested this in an Oracle 12.1 database. The V$BACKUP view tells you if a datafile is in hot backup mode. I started off with none of the datafiles in hot backup mode so they were all shown as NOT ACTIVE:

SQL> select file#, status from v$backup
  2  /
 
     FILE# STATUS
---------- ------------------
         1 NOT ACTIVE
         2 NOT ACTIVE
         3 NOT ACTIVE
         5 NOT ACTIVE
    (more...)

Re-Creating Datafiles When Backups Are Unavailable

I found an old copy of the Oracle 9i User-Managed Backup and Recovery Guide and read the following:
 
If a datafile is damaged and no backup of the file is available, then you can still recover the datafile if:
 
·         All archived log files written after the creation of the original datafile are available
·         The control file contains the name of the damaged file (that is, the control file is (more...)

ALTER DATABASE BEGIN BACKUP and ALTER DATABASE END BACKUP

I logged into an Oracle 12.1 database, checked it was in ARCHIVELOG mode then ran the ALTER DATABASE BEGIN BACKUP command. This told Oracle I was about to do a hot backup:

C:UsersAndrew>sqlplus / as sysdba
 
SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 18 14:45:56 2015
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1. (more...)

ORA-28221

This post shows that if a profile has a password verify function and you assign it to a user who does not have the ALTER USER privilege, that user will not be able to change his or her password without using the REPLACE option. You can see what I mean in the example below, which I tested in Oracle 11.1. First I created a password verify function:

SQL> conn / as sysdba
Connected.

What is an INDEX SKIP SCAN?

I tested this on Oracle 11.2. First I created a table with two columns. The first column, OWNER, had low cardinality i.e. it had only two possible values, PUBLIC and SYS. The second column, OBJECT_NAME, had the names of all objects owned by PUBLIC and/or SYS so it had high cardinality i.e. it had many different values:

SQL> conn system/manager
Connected.
SQL> create table t1 as
  2  select owner, (more...)

Using NOT = in a WHERE Clause

I ran this test in Oracle 12.1. First I created a table, added some data to it and made sure that the bytes column was set to zero in every row:
 
SQL> conn system/manager
Connected.
SQL> alter session set optimizer_mode = first_rows
  2  /
 
Session altered.
 
SQL> create table t1
  2  tablespace users
  3  as select segment_name, bytes
  4  from dba_segments
  5  /
 
Table created.

ORA-04000

I created a table in an Oracle 11.2 database. I did not specify a pctfree or pctused so it was given the defaults of 10% and 40% respectively:

SQL> conn system/manager
Connected.
SQL> create table t1 (c1 number)
  2  /

Table created.

SQL> select pct_free, pct_used
  2  from user_tables
  3  where table_name = 'T1'
  4  /

  PCT_FREE   PCT_USED
---------- ----------
        10         40 




SQL>

When Oracle inserts rows into (more...)

FULL and NO_INDEX Hints

I was reading about hints and decided to try out a couple on an Oracle 11.2 database. First I created a table, added some data and created an index:

SQL> create table t1 (c1 varchar2(30))
  2  /
 
Table created.
 
SQL> insert into t1 select table_name from dba_tables
  2  /
 
3159 rows created.
 
SQL> create index i1 on t1(c1)
  2  /
 
Index created.
 
SQL>

I ran (more...)

Default Size of a CHAR Column

If you do not specify a size for a CHAR column, the default is 1. You can see what I mean in the example below, which I tested on Oracle 11.2:

SQL> create table t1
  2  (c1 char,
  3   c2 char(1))
  4  /
 
Table created.
 
SQL> desc t1
Name                       Null?    Type
-------------------------- -------- ------------------
C1                                  CHAR(1)
C2                                  CHAR(1)
 
SQL> 

However, if you rely on defaults like (more...)

VARCHAR and VARCHAR2

If you create a table with a VARCHAR column in Oracle 11.2, Oracle sets it up as a VARCHAR2:

SQL> l
  1  create table t1
  2  (c1 varchar(1),
  3*  c2 varchar2(1))
SQL> /
 
Table created.
 
SQL> desc t1
Name                       Null?    Type
-------------------------- -------- ------------------
C1                                  VARCHAR2(1)
C2                                  VARCHAR2(1)
 
SQL>

According to a book I am working through, this has been the case since Oracle 8. However, (more...)

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