How to Rename a Table

I tested this example in Oracle 12.1. First I created a table:

SQL> create table fred

  2  as select * from user_synonyms

  3  where 1 = 2

  4  /

 

Table created.

SQL>

Then I checked its object_id for later:

SQL> select object_id

  2  from user_objects

  3  where object_name = 'FRED'

  4  /

 

OBJECT_ID

----------

     92212

SQL>

... and described it:

SQL> desc fred

Name                       Null?    Type

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

SYNONYM_NAME               NOT NULL VARCHAR2(128)

TABLE_OWNER                         VARCHAR2(128)

TABLE_NAME                 NOT NULL VARCHAR2(128)

DB_LINK                             VARCHAR2(128)

ORIGIN_CON_ID                       NUMBER

SQL>

Then I changed its name:

SQL> rename fred to joe

  2  /

 

Table renamed.

SQL>

... and finally, to prove I was still looking at the same object, I used the new name to look up the object_id and description and confirmed that they had not changed:

SQL> select object_id

  2  from user_objects

  3  where object_name = 'JOE'

  4  /

 

OBJECT_ID

----------

     92212

 

SQL> desc joe

Name                       Null?    Type

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

SYNONYM_NAME               NOT NULL VARCHAR2(128)

TABLE_OWNER                         VARCHAR2(128)

TABLE_NAME                 NOT NULL VARCHAR2(128)

DB_LINK                             VARCHAR2(128)

ORIGIN_CON_ID                       NUMBER

 

SQL>

ORA-00069

A colleague asked what the table_lock column in dba_tables was for. I did not know so I decided to investigate.First I looked at the distribution of table_lock values in one of our test databases:

SQL> select table_lock, count(*)
  2  from dba_tables
  3  group by table_lock
  4  /

TABLE_LOCK  COUNT(*)
---------- ----------
ENABLED          825

SQL>
 

This suggested to me that the default setting is enabled so I tried this out by creating a test table:

SQL> create table andrew (col1 number)
  2  /

Table created.

SQL> 

As expected, the table_lock column was set to enabled for this new table:

SQL> select table_lock from dba_tables
  2  where table_name = 'ANDREW'
  3  /

TABLE_LOCK
----------
ENABLED

SQL> 

You can alter this as follows:

SQL> alter table andrew disable table lock
  2  /

Table altered.

SQL>

And this sets the table_lock value to disabled:

SQL> select table_lock from dba_tables
  2  where table_name = 'ANDREW'
  3  /

TABLE_LOCK
----------
DISABLED

SQL> 

Setting table_lock to disabled stops you locking that table:

SQL> lock table andrew in share mode
  2  /
lock table andrew in share mode
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks
disabled for ANDREW

SQL> 

It also stops you running other DDL against it:

SQL> rename andrew to fred
  2  /
rename andrew to fred
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks
disabled for ANDREW

SQL>

And setting it back to enabled allows you to lock the table:

SQL> alter table andrew enable table lock
  2  /

Table altered.

SQL>

And run other DDL on the table:

SQL> rename andrew to fred
  2  /

Table renamed.

SQL>

So the main purpose of the table_lock column is to show whether you are allowed to lock a table or not.

How to Rename an Oracle Tablespace

In Oracle 9 you got an error if you tried to rename a tablespace: 

SQL> l

  1* alter tablespace users rename to andrew

SQL> /

alter tablespace users rename to andrew

                              *

ERROR at line 1:

ORA-01904: DATAFILE keyword expected

 

SQL>

… but from Oracle 10 onwards you can rename tablespaces. I decided to demonstrate this by renaming the USERS tablespace to ANDREW. First I checked that there was a tablespace called USERS but no tablespace called ANDREW:

SQL> select tablespace_name from dba_tablespaces
  2  where tablespace_name in ('ANDREW', 'USERS')
  3  /

TABLESPACE_NAME
------------------------------
USERS

 
SQL> 

Then I showed that there was a table called CARS in the USERS tablespace: 

SQL> select tablespace_name from dba_tables
  2  where table_name = 'CARS'
  3  /

TABLESPACE_NAME
------------------------------
USERS

SQL>

I renamed the USERS tablespace and called it ANDREW instead:

SQL> alter tablespace users rename to andrew
  2  /

Tablespace altered.

SQL>

After that I showed that there was a tablespace called ANDREW but no tablespace called USERS:

SQL> select tablespace_name from dba_tablespaces
  2  where tablespace_name in ('ANDREW', 'USERS')
  3  /

TABLESPACE_NAME
------------------------------
ANDREW

SQL>

Then I checked that the CARS table was in the ANDREW tablespace:

SQL> select tablespace_name from dba_tables
  2  where table_name = 'CARS'
  3  /

TABLESPACE_NAME
------------------------------
ANDREW

SQL>

I took the ANDREW tablespace offline then tried to rename it back to USERS but found that you cannot rename a tablespace if it is offline:

SQL> alter tablespace andrew offline
  2  /

Tablespace altered.

SQL> alter tablespace andrew rename to users
  2  /
alter tablespace andrew rename to users
*
ERROR at line 1:
ORA-01135: file 4 accessed for DML/query is offline
ORA-01110: data file 4: 'C:\DOCUMENTS AND SETTINGS\ANDREW\MY
DOCUMENTS\ADHOC_DATABASE\ADHOC\USERS01.DBF'

SQL> 

Next I confirmed that you cannot rename the SYSTEM or SYSAUX tablespaces:

SQL> alter tablespace system rename to fred
  2  /
alter tablespace system rename to fred
*
ERROR at line 1:
ORA-00712: cannot rename system tablespace

SQL> alter tablespace sysaux rename to john
  2  /
alter tablespace sysaux rename to john
*
ERROR at line 1:
ORA-13502: Cannot rename SYSAUX tablespace

SQL> 

Finally, if you try to give your tablespace an invalid name, you get an ORA-02150: 

SQL> alter tablespace users rename to 123;

alter tablespace users rename to 123

                                 *

ERROR at line 1:

ORA-02150: invalid new tablespace name

 

SQL> 

 

So, now my problem is - how can I get an ORA-00711? 

 

Solaris > oerr ora 00711

00711, 00000, "new tablespace name is invalid"

// *Cause:  An attempt to rename a tablespace failed because the new name

//          is invalid.

// *Action: Choose a valid new name and retry the command.

Solaris >

ORA-04043 and ORA-00980

This example was tested on Oracle 9. First create a table:

 

SQL> create table table1 (col1 number)

  2  /

 

Table created.

 

SQL>

 

Then create a synonym pointing to that table:

 

SQL> create synonym synonym1 for table1

  2  /

 

Synonym created.

 

SQL> desc synonym1

Name                       Null?    Type

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

COL1                                NUMBER

 

SQL>

 

Rename the underlying the table:

 

SQL> rename table1 to table2

  2  /

 

Table renamed.

 

SQL>

 

Now, if you describe the table via the synonym, you get an ORA-04043, which I have shown elsewhere:

 

SQL> desc synonym1

ERROR:

ORA-04043: object "ORACLE"."TABLE1" does not exist

 

SQL>

 

But, if you select from the table via the synonym, you get an ORA-00980:

 

SQL> select * from synonym1

  2  /

select * from synonym1

              *

ERROR at line 1:

ORA-00980: synonym translation is no longer valid

 

SQL>

 

Depending on the circumstances, you could fix this as follows:

(1)  Rename the table back again, if it was renamed by mistake in the first place.

(2)  Drop and recreate the synonym so that it points to the new table name.

How to Partition an Existing Table

This example was tested on Oracle 9. First create an ordinary table with some data in and count the number of rows it contains:

SQL> create table andrew
  2  as select * from dba_tables
  3  /

Table created.

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
       831

SQL>

Next create an empty partitioned version of the table and show that it is empty:

SQL> create table andrew_partitioned
  2  partition by range(owner)
  3  (partition part01
  4  values less than (maxvalue))
  5  as select * from andrew
  6  where 1=2
  7  /

Table created.

SQL> select count(*) from andrew_partitioned
  2  /

  COUNT(*)
----------
         0

SQL>

Now move the data from the ordinary table to the partitioned table:

SQL> alter table andrew_partitioned
  2  exchange partition part01
  3  with table andrew
  4  /

Table altered.

SQL>

Show that the ordinary table is empty and that the partitioned table now has all the data:

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
         0

SQL> select count(*) from andrew_partitioned
  2  /

  COUNT(*)
----------
       831

SQL>

Give the partitioned table the same name as the original non-partitioned one:

SQL> drop table andrew
  2  /

Table dropped.

SQL> rename andrew_partitioned to andrew
  2  /

Table renamed.

SQL> 

Finally, you can split up the new partitioned table as you wish:

SQL> alter table andrew
  2  split partition part01 at ('O')
  3  into (partition part01, partition part02)
  4  /

Table altered.

SQL>