ORA-02494

This was tested on Oracle 11.2. If you create a tablespace with a datafile with autoextend on, the file’s maxsize cannot be less than its size. If it is, you get an ORA-02494: 

SQL> l

  1  create tablespace andrew

  2  datafile '/tmp/andrew.dbf'

  3* size 200m autoextend on maxsize 100m

SQL> /

size 200m autoextend on maxsize 100m

                                   *

ERROR at line 3:

ORA-02494: invalid or missing maximum file size in

MAXSIZE clause

 

SQL> l

  1  create tablespace andrew

  2  datafile '/tmp/andrew.dbf'

  3* size 200m autoextend on maxsize 300m

SQL> /

 

Tablespace created.

 

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>

Oracle Managed Files (Part 1)

Oracle managed files were introduced in version 9. You can implement them using initialisation parameters. These can be set:

1. In the init.ora or server parameter file.
2. In an alter session or alter system statement.

The parameters specify directories which Oracle should use for datafiles in subsequent DDL statements such as create tablespace etc. You can see what I mean in the example below, which I ran on Oracle 9.2.0.4.0:

First, specify the directory where files should be created using the db_create_file_dest parameter:

SQL> alter session set db_create_file_dest = '/mnt/redhat';

 

Session altered.

 

SQL>

 

Now create a tablespace. Oracle is managing the creation of datafiles so no filename is required:

 

SQL> create tablespace andrew

  2  datafile size 10m

  3  /

 

Tablespace created.

 

SQL>

 

Check the name(s) of the datafile(s) in the tablespace. There is only one and Oracle has created it in the location specified by the db_create_file_dest parameter:

 

SQL> l

  1  select file_name, bytes from dba_data_files

  2* where tablespace_name = 'ANDREW'

SQL> /

 

FILE_NAME                                     BYTES

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

/mnt/redhat/o1_mf_andrew_7fh2qylt_.dbf     10485760

 

SQL>

 

Add a datafile to the tablespace and check the name(s) of the datafile(s) again:

 

SQL> alter tablespace andrew add datafile size 5m

  2  /

 

Tablespace altered.

 

SQL> select file_name, bytes from dba_data_files

  2  where tablespace_name = 'ANDREW'

  3  /

 

FILE_NAME                                     BYTES

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

/mnt/redhat/o1_mf_andrew_7fh2qylt_.dbf     10485760

/mnt/redhat/o1_mf_andrew_7fh2zndg_.dbf      5242880

 

SQL>

 

Look at the files at the Linux level:

 

TEST9 > pwd

/mnt/redhat

TEST9 > ls -1

o1_mf_andrew_7fh2qylt_.dbf

o1_mf_andrew_7fh2zndg_.dbf

TEST9 >

 

Drop the tablespace:

 

SQL> drop tablespace andrew

  2  /

 

Tablespace dropped.

 

SQL>

 

Oracle deletes managed files once they are no longer required.Check that the files have gone at the Linux level:

 

TEST9 > pwd

/mnt/redhat

TEST9 > ls -l

total 0

TEST9 >

Coalescing Free Space

This example was tested on an Oracle 9 database. It shows the coalescing of free space in a dictionary managed tablespace where pctincrease=0:

1. When new extents have to be allocated.
2. When the alter tablespace <<tablespace_name>> coalesce command is run.

First create a tablespace:

SQL> conn / as sysdba
Connected.
SQL> create tablespace andrews_tablespace
  2  extent management dictionary
  3  datafile '/test9/datafiles/andrew.dbf'
  4  size 10 m
  5  default storage(pctincrease 0)
  6  /

Tablespace created.

SQL>

Create a map of the tablespace. It will have a single free area:

SQL> select file_id, block_id,
  2  substr(segment_name,1,20) Object_Name, bytes
  3  from dba_extents
  4  where tablespace_name = 'ANDREWS_TABLESPACE'
  5  union
  6  select file_id, block_id, 'FREE', bytes
  7  from dba_free_space
  8  where tablespace_name = 'ANDREWS_TABLESPACE'
  9  order by file_id, block_id
 10  /

   FILE_ID   BLOCK_ID OBJECT_NAME               BYTES
---------- ---------- -------------------- ----------
        25          2 FREE                   10481664

SQL>

Create a table in the tablespace:

SQL> create table andrews_table1(col1 number)
  2  tablespace andrews_tablespace
  3  storage (initial 2m next 2m minextents 4)
  4  /

Table created.

SQL> 

Create a map of the tablespace. This will show the new table's 4 extents and a small free area at the end of the tablespace:

SQL> select file_id, block_id,
  2  substr(segment_name,1,20) Object_Name, bytes
  3  from dba_extents
  4  where tablespace_name = 'ANDREWS_TABLESPACE'
  5  union
  6  select file_id, block_id, 'FREE', bytes
  7  from dba_free_space
  8  where tablespace_name = 'ANDREWS_TABLESPACE'
  9  order by file_id, block_id
 10  /

   FILE_ID   BLOCK_ID OBJECT_NAME               BYTES
---------- ---------- -------------------- ----------
        25          2 ANDREWS_TABLE1          2109440
        25        517 ANDREWS_TABLE1          2109440
        25       1032 ANDREWS_TABLE1          2109440
        25       1547 ANDREWS_TABLE1          2109440
        25       2062 FREE                    2043904

SQL>

Drop the table:

SQL> drop table andrews_table1
  2  /

Table dropped.

SQL> 

Create a map of the tablespace. This will show 5 free areas:

SQL> select file_id, block_id,
  2  substr(segment_name,1,20) Object_Name, bytes
  3  from dba_extents
  4  where tablespace_name = 'ANDREWS_TABLESPACE'
  5  union
  6  select file_id, block_id, 'FREE', bytes
  7  from dba_free_space
  8  where tablespace_name = 'ANDREWS_TABLESPACE'
  9  order by file_id, block_id
 10  /

   FILE_ID   BLOCK_ID OBJECT_NAME               BYTES
---------- ---------- -------------------- ----------
        25          2 FREE                    2109440
        25        517 FREE                    2109440
        25       1032 FREE                    2109440
        25       1547 FREE                    2109440
        25       2062 FREE                    2043904

SQL>

Create a table in the tablespace. Its extents are 3 megabytes each i.e. they are bigger than any of the free areas in the tablespace so Oracle has to do some coalescing first:

SQL> create table andrews_table2(col1 number)
  2  tablespace andrews_tablespace
  3  storage (initial 3m next 3m minextents 3)
  4  /

Table created.

SQL> 

Create a map of the tablespace. This shows the new table made up of 3 megabyte extents plus a free area at the end of the tablespace:

SQL> select file_id, block_id,
  2  substr(segment_name,1,20) Object_Name, bytes
  3  from dba_extents
  4  where tablespace_name = 'ANDREWS_TABLESPACE'
  5  union
  6  select file_id, block_id, 'FREE', bytes
  7  from dba_free_space
  8  where tablespace_name = 'ANDREWS_TABLESPACE'
  9  order by file_id, block_id
 10  /

   FILE_ID   BLOCK_ID OBJECT_NAME               BYTES
---------- ---------- -------------------- ----------
        25          2 ANDREWS_TABLE2          3153920
        25        772 ANDREWS_TABLE2          3153920
        25       1542 ANDREWS_TABLE2          3153920
        25       2312 FREE                    1019904

SQL>

Drop the table:

SQL> drop table andrews_table2
  2  /

Table dropped.

SQL>

Create a map of the tablespace. This shows 3 new free areas previously occupied by ANDREWS_TABLE2:

SQL> select file_id, block_id,
  2  substr(segment_name,1,20) Object_Name, bytes
  3  from dba_extents
  4  where tablespace_name = 'ANDREWS_TABLESPACE'
  5  union
  6  select file_id, block_id, 'FREE', bytes
  7  from dba_free_space
  8  where tablespace_name = 'ANDREWS_TABLESPACE'
  9  order by file_id, block_id
10  /

   FILE_ID   BLOCK_ID OBJECT_NAME               BYTES
---------- ---------- -------------------- ----------
        25          2 FREE                    3153920
        25        772 FREE                    3153920
        25       1542 FREE                    3153920
        25       2312 FREE                    1019904

SQL> 

Finally, show the effect of coalescing the tablespace manually:

SQL> alter tablespace andrews_tablespace coalesce
  2  /

Tablespace altered.

SQL>

Create a map of the tablespace. It will have 1 single free area created by the merging of the 4 free areas which were there before:

SQL> select file_id, block_id,
  2  substr(segment_name,1,20) Object_Name, bytes
  3  from dba_extents
  4  where tablespace_name = 'ANDREWS_TABLESPACE'
  5  union
  6  select file_id, block_id, 'FREE', bytes
  7  from dba_free_space
  8  where tablespace_name = 'ANDREWS_TABLESPACE'
  9  order by file_id, block_id
 10  /

   FILE_ID   BLOCK_ID OBJECT_NAME               BYTES
---------- ---------- -------------------- ----------
        25          2 FREE                   10481664

SQL> 

Drop the tablespace:

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

Tablespace dropped.

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>