Taking a Datafile Offline in NOARCHIVELOG Mode

I read some RMAN documentation which 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

 

SQL>

Then I tried to take a datafile offline but this failed, as the documentation had said it would: 

SQL> alter database datafile

  2  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'

  3  offline

  4  /

alter database datafile

*

ERROR at line 1:

ORA-01145: offline immediate disallowed unless media

recovery enabled

 

SQL>

 

I read somewhere in the Oracle documentation that you could do this if the tablespace was READ ONLY. This also seemed reasonable so I tried it too: 

SQL> alter tablespace users read only

  2  /

 

Tablespace altered.

 

SQL> alter database datafile

  2  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'

  3  offline

  4  /

 

Database altered.

 

SQL> 

Of course, once I had done this, I could not change the tablespace to READ WRITE mode again: 

SQL> l

  1* alter tablespace users read write

SQL> /

alter tablespace users read write

*

ERROR at line 1:

ORA-01135: file 6 accessed for DML/query is offline

ORA-01110: data file 6:

'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'

 

SQL> 

… until I had brought the datafile ONLINE: 

SQL> alter database datafile

  2  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'

  3  online

  4  /

 

Database altered.

 

SQL> alter tablespace users read write

  2  /

 

Tablespace altered.

 

SQL>

Can You Set a Datafile's Maxbytes / Maxsize to Less Than its Actual Size?

I was dealing with a message like this for one of our developers:

ORA-01653: unable to extend table ...

He was using a tablespace which had one datafile with AUTOEXTEND ON and this datafile had reached its MAXBYTES / MAXSIZE. I wondered what would happen if you tried to set a datafile's MAXBYTES / MAXSIZE to a value less than its actual size. I tried this first on Oracle 9. I started with a datafile which had an actual size of 20 megabytes and a MAXBYTES / MAXSIZE of 50 megabytes. I found that I was able to set its MAXBYTES / MAXSIZE to 1 megabyte and that the change was recorded in DBA_DATA_FILES:

SQL> select file_id, bytes, maxbytes, autoextensible

  2  from dba_data_files

  3  where tablespace_name = 'USER_DATA'

  4  /

 

   FILE_ID      BYTES   MAXBYTES AUT

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

        22   20971520   52428800 YES

 

SQL> alter database datafile 22

  2  autoextend on

  3  maxsize 1m

  4  /

 

Database altered.

 

SQL> select file_id, bytes, maxbytes, autoextensible

  2  from dba_data_files

  3  where tablespace_name = 'USER_DATA'

  4  /

 

   FILE_ID      BYTES   MAXBYTES AUT

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

        22   20971520    1048576 YES

 

SQL>

However, when I tried to do a similar thing in Oracle 11, the MAXBYTES value shown in DBA_DATA_FILES did not go below the actual size of the datafile:

SQL> select file_id, bytes, maxbytes

  2  from dba_data_files

  3  where tablespace_name = 'USERS'

  4  /

 

   FILE_ID      BYTES   MAXBYTES

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

         4   20971520   52428800

 

SQL> alter database datafile 4

  2  autoextend on maxsize 10m

  3  /

 

Database altered.

 

SQL> select file_id, bytes, maxbytes

  2  from dba_data_files

  3  where tablespace_name = 'USERS'

  4  /

 

   FILE_ID      BYTES   MAXBYTES

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

         4   20971520   20971520

 

SQL>

ORA-03297

This example, which I tested in an Oracle 9 database, explains the cause of this error message. First create a 20 megabyte tablespace with 1 datafile. Put 2 tables in it, each with a single 9 megabyte extent:

SQL> create tablespace andrew
  2  datafile '/database/andrew.dbf'
  3  size 20m
  4  extent management dictionary
  5  /

Tablespace created.

SQL> create table tab1(col1 number)
  2  tablespace andrew
  3  storage (initial 9m)
  4  /

Table created.

SQL> create table tab2(col1 number)
  2  tablespace andrew
  3  storage (initial 9m)
  4  /

Table created.

SQL>

Next create a map of the tablespace. I wrote this SQL in 2002 but it still seems to work. Its output shows the two 9 megabyte tables at the start of the datafile and a 2 megabyte free area at the end:

SQL> select file_id, block_id, segment_name, bytes

  2  from dba_extents

  3  where tablespace_name = 'ANDREW'

  4  union

  5  select file_id, block_id, 'FREE', bytes

  6  from dba_free_space

  7  where tablespace_name = 'ANDREW'

  8  order by file_id, block_id

  9  /

   FILE_ID   BLOCK_ID SEGMENT_NAME         BYTES

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

        26          2 TAB1               9441280

        26       2307 TAB2               9441280

        26       4612 FREE               2084864

SQL>

Now drop the first table and redo the tablespace map. This shows a 9 megabyte free area at the start of the datafile where TAB1 used to be:

SQL> drop table tab1

  2  /

Table dropped.

SQL> select file_id, block_id, segment_name, bytes

  2  from dba_extents

  3  where tablespace_name = 'ANDREW'

  4  union

  5  select file_id, block_id, 'FREE', bytes

  6  from dba_free_space

  7  where tablespace_name = 'ANDREW'

  8  order by file_id, block_id

  9  /

   FILE_ID   BLOCK_ID SEGMENT_NAME         BYTES

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

        26          2 FREE               9441280

        26       2307 TAB2               9441280

        26       4612 FREE               2084864

SQL>

Try to resize the datafile to 10 megabytes as it now only contains a 9 megabyte table. This fails as it can only recoup space from the end of the datafile and TAB2 is there already:

SQL> alter database

  2  datafile '/cisdpt/ebedpt1/ebe_tables/andrew.dbf'

  3  resize 10m

  4  /

alter database

*

ERROR at line 1:

ORA-03297: file contains used data beyond requested

RESIZE value

SQL>

You need to do something to free up the space at the end of the  datafile. On this occasion you are in luck. If you move TAB2 within the tablespace, it will take up the space at the start of the datafile:

SQL> alter table tab2 move

  2  /

Table altered.

SQL>

Redo the tablespace map. This shows a large enough area of free space at the end of the datafile to allow it to be resized successfully:

SQL> select file_id, block_id, segment_name, bytes

  2  from dba_extents

  3  where tablespace_name = 'ANDREW'

  4  union

  5  select file_id, block_id, 'FREE', bytes

  6  from dba_free_space

  7  where tablespace_name = 'ANDREW'

  8  order by file_id, block_id

  9  /

   FILE_ID   BLOCK_ID SEGMENT_NAME         BYTES

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

        26          2 TAB2               9441280

        26       2307 FREE              11526144

SQL> alter database

  2  datafile '/cisdpt/ebedpt1/ebe_tables/andrew.dbf'

  3  resize 10m

  4  /

Database altered.

SQL>

Redo the tablespace map to show the effect of the resize:

SQL> select file_id, block_id, segment_name, bytes

  2  from dba_extents

  3  where tablespace_name = 'ANDREW'

  4  union

  5  select file_id, block_id, 'FREE', bytes

  6  from dba_free_space

  7  where tablespace_name = 'ANDREW'

  8  order by file_id, block_id

  9  /

   FILE_ID   BLOCK_ID SEGMENT_NAME         BYTES

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

        26          2 TAB2               9441280

        26       2307 FREE               1040384

SQL>

Finally, drop the tablespace:

SQL> drop tablespace andrew
  2  including contents and datafiles
  3  /

Tablespace dropped.

SQL>

And now for a history lesson. I started to use Oracle 7 in the late 1990s. I had a development server all to myself and would often use create controlfile to clone databases. On one occasion I made the datafiles smaller beforehand using the resize command shown above. This caused the create controlfile to fail with an actual file size of <num> is smaller than correct size of <num> blocks message. The only workaround I found was to return the files to their original size and try again. For a long time I thought I was doing something wrong but I eventually discovered that this problem was caused by bug 309181, which was apparently fixed in Oracle 7.3.3.
 

ORA-01516

This example illustrates a problem I had recently. I created a tablespace:

  1  create tablespace andrew

  2  datafile '/usr/users/oracle/andrew.dbf'

  3* size 10m

SQL> /

Tablespace created.

SQL>

Some time later I decided to resize its datafile. I remembered how big it was but I was not sure of its name so I queried DBA_DATA_FILES:

SQL> col file_name format a40

SQL> l

  1  select file_name

  2  from dba_data_files

  3* where tablespace_name = 'ANDREW'

SQL> /

FILE_NAME

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

/usr/users/oracle/andrew.dbf

SQL>

But when I tried to resize it I had an ORA-01516 error:

SQL> alter database datafile

  2  '/usr/users/oracle/andrew.dbf'

  3  resize 100m;

alter database datafile

*

ERROR at line 1:

ORA-01516: nonexistent log file, datafile, or tempfile

"/usr/users/oracle/andrew.dbf"

SQL>

The reason for this was not obvious until I looked in the Oracle alert log:

Wed Mar  9 15:35:58 2011

create tablespace andrew

datafile '/usr/users/oracle/and^[[6~rew.dbf'

size 10m

Wed Mar  9 15:35:59 2011

Completed: create tablespace andrew

By careless use of a cursor control key, I had introduced a control character into the file name. This control character could not be seen when querying the file's name in DBA_DATA_FILES. The only way to resize the file was to use the full name displayed in the Oracle alert log:

SQL> alter database datafile
  2  '/usr/users/oracle/and^[[6~rew.dbf'

  3  resize 20m;

Database altered.

SQL>