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>

How to Move an Oracle Datafile

This worked example shows how to move a datafile. First create a tablespace:

SQL> create tablespace andrew

  2  datafile '/usr/users/oracle/andrew/file1'

  3  size 1m;

 

Tablespace created.

 

SQL>

Create a table in the tablespace then describe it and count the rows. This is only to check that the contents of the tablespace are OK after the datafile has been moved:

 

SQL> create table andrews_table

  2  tablespace andrew

  3  as select * from dba_objects;

 

Table created.

 

SQL> desc andrews_table

Name                       Null?    Type

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

OWNER                               VARCHAR2(30)

OBJECT_NAME                         VARCHAR2(128)

SUBOBJECT_NAME                      VARCHAR2(30)

OBJECT_ID                           NUMBER

DATA_OBJECT_ID                      NUMBER

OBJECT_TYPE                         VARCHAR2(18)

CREATED                             DATE

LAST_DDL_TIME                       DATE

TIMESTAMP                           VARCHAR2(19)

STATUS                              VARCHAR2(7)

TEMPORARY                           VARCHAR2(1)

GENERATED                           VARCHAR2(1)

SECONDARY                           VARCHAR2(1)

 

SQL> select count(*) from andrews_table;

 

  COUNT(*)

----------

      7933

 

SQL>

Note the name(s) of the tablespace's datafile(s).

SQL> select file_name from dba_data_files

  2  where tablespace_name = 'ANDREW';

 

FILE_NAME

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

/usr/users/oracle/andrew/file1

SQL>

Take the tablespace offline:

 

SQL> alter tablespace andrew offline;

 

Tablespace altered.

 

SQL>

Copy the file to its new location in the operating system. Normally you would be doing this to move the datafile(s) to a different disk but this is only an example on a test database so I will just copy the file within the same directory: 

UNIX /usr/users/oracle/andrew >cp file1 file2

UNIX /usr/users/oracle/andrew >

Rename the datafile at the Oracle level in SQL*Plus:

 

  1  alter tablespace andrew rename datafile

  2  '/usr/users/oracle/andrew/file1'

  3  to

  4* '/usr/users/oracle/andrew/file2'

SQL> /

 

Tablespace altered.

SQL>

Put the tablespace online again:

 

SQL> alter tablespace andrew online;

 

Tablespace altered.

 

SQL>

See that the new file name appears in dba_data_files: 

SQL> l

  1  select file_name from dba_data_files

  2* where tablespace_name = 'ANDREW'

SQL> /

 

FILE_NAME

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

/usr/users/oracle/andrew/file2

 

SQL>

Check that the table created above is still in the tablespace, describe it and count the rows again then compare the figures with those above:

SQL> select table_name from dba_tables

  2  where tablespace_name = 'ANDREW';

 

TABLE_NAME

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

ANDREWS_TABLE

 

SQL> desc andrews_table

Name                       Null?    Type

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

OWNER                               VARCHAR2(30)

OBJECT_NAME                         VARCHAR2(128)

SUBOBJECT_NAME                      VARCHAR2(30)

OBJECT_ID                           NUMBER

DATA_OBJECT_ID                      NUMBER

OBJECT_TYPE                         VARCHAR2(18)

CREATED                             DATE

LAST_DDL_TIME                       DATE

TIMESTAMP                           VARCHAR2(19)

STATUS                              VARCHAR2(7)

TEMPORARY                           VARCHAR2(1)

GENERATED                           VARCHAR2(1)

SECONDARY                           VARCHAR2(1)

 

SQL> select count(*) from andrews_table;

 

  COUNT(*)

----------

      7933

 

SQL>

You cannot use this method for system, undo or temporary tablespaces as you cannot take them offline. I will cover them in a future post:

SQL> alter tablespace system offline;

alter tablespace system offline

*

ERROR at line 1:

ORA-01541: system tablespace cannot be brought

offline; shut down if necessary

 

SQL> alter tablespace undo_1 offline;

alter tablespace undo_1 offline

*

ERROR at line 1:

ORA-30042: Cannot offline the undo tablespace

 

SQL> alter tablespace temp offline;

alter tablespace temp offline

*

ERROR at line 1:

ORA-03217: invalid option for alter of TEMPORARY

TABLESPACE

 

SQL>

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>

Drop Database

In version 10g, Oracle introduced the drop database SQL statement. This removes all datafiles, online redo log files, control files and server parameter files. I created a database with the Database Configuration Assistant so that I could try out this new command. To make it easier to demonstrate, I put the datafiles, online redo log files and control files all in the same directory:

SQL> col file_name format a55
SQL> select file_name from dba_data_files;

FILE_NAME
-------------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\USERS01.DBF
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\SYSAUX01.DBF
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\UNDOTBS01.DBF
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\SYSTEM01.DBF

SQL> select file_name from dba_temp_files;

FILE_NAME
-------------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\TEMP01.DBF
  
SQL> col member format a50
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\REDO03.LOG
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\REDO02.LOG
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\REDO01.LOG

SQL> col name format a55
SQL> select name from v$controlfile;

NAME
-------------------------------------------------------
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\CONTROL01.CTL
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\CONTROL02.CTL
C:\DOCUMENTS AND SETTINGS\ANDREW\TEST10\CONTROL03.CTL

SQL>

It’s easier to see in the screen print below (click to enlarge it):
   

The server parameter file was in a different directory:

SQL> col value format a50
SQL> select value from v$parameter where name = 'spfile';

VALUE
--------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILETEST10.ORA

SQL>

Then I tried to drop the database:

SQL> set lines 60
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

SQL>

I followed the instructions and tried again: 

C:\Documents and Settings\Andrew>set ORACLE_SID=TEST10

C:\Documents and Settings\Andrew>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 24 09:35:34 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250452 bytes
Variable Size 176163692 bytes
Database Buffers 432013312 bytes
Redo Buffers 2940928 bytes
Database mounted.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode

SQL>

Then putting the database into restricted session mode appeared to have the desired effect:

SQL> alter system enable restricted session;

System altered.

SQL> drop database;

Database dropped.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

The datafiles were removed:

C:\Documents and Settings\Andrew\test10>dir
Volume in drive C has no label.
Volume Serial Number is 18E4-B972

Directory of C:\Documents and Settings\Andrew\test10

24/12/2010 10:01 <DIR> .
24/12/2010 10:01 <DIR> ..
              0 File(s) 0 bytes
              2 Dir(s) 11,539,795,968 bytes free

C:\Documents and Settings\Andrew\test10>

And so was the spfile:

C:\oracle\product\10.2.0\db_1\dbs>dir
Volume in drive C has no label.
Volume Serial Number is 18E4-B972

Directory of C:\oracle\product\10.2.0\db_1\dbs

24/12/2010 10:00 <DIR> .
24/12/2010 10:00 <DIR> ..
              0 File(s) 0 bytes
              2 Dir(s) 11,539,415,040 bytes free

C:\oracle\product\10.2.0\db_1\dbs>