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-01754

The first example below was tested on Oracle 11.2. If you try to have more than one long column in a table, you get an ORA-01754:

SQL> create table tab1 (col1 long, col2 long)

  2  /

create table tab1 (col1 long, col2 long)

                              *

ERROR at line 1:

ORA-01754: a table may contain only one column of type LONG

 

SQL> 

That was fairly obvious. Now here is another example, which I wrote at the start of 2012. I do not know which Oracle version I did it on. First I created a table with one column:

SQL> create table andrews_table

  2  (col1 varchar2(10))

  3  /

Table created.

SQL>

Then I added a long column to the table and described it to show the column I had just added:

SQL> alter table andrews_table

  2  add (col2 long)

  3  /

Table altered.

SQL> desc andrews_table

Name                      Null?    Type

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

COL1                                VARCHAR2(10)

COL2                                LONG

SQL>

Next I set the long column to unused. This does not restore the disk space used by the column:

SQL> alter table andrews_table
  2  set unused column col2
  3  /

Table altered.

SQL>

I described the table again to show that the long column had gone:

SQL> desc andrews_table
Name                      Null?    Type
-------------------------- -------- ------------------
COL1                                VARCHAR2(10)

SQL>

Then I tried to add another long column. This failed as you can only have 1 long column per table. How would you diagnose this problem if it happened to you in real life?

SQL> alter table andrews_table
  2  add (col3 long)
  3  /
add (col3 long)
    *
ERROR at line 2:
ORA-01754: a table may contain only one column of type
LONG

SQL>

You can get a clue by looking in user_unused_col_tabs, which confirms that the table has 1 unused column:

SQL> select count(*) from user_unused_col_tabs

  2  where table_name = 'ANDREWS_TABLE'

  3  /

  COUNT(*)

----------

         1

SQL>

Drop the unused column from the table. This frees up the disk space which the column occupied:

SQL> alter table andrews_table
  2  drop unused columns
  3  /

Table altered.

SQL>

The table no longer has an entry in user_unused_col_tabs:

SQL> select count(*) from user_unused_col_tabs
  2  where table_name = 'ANDREWS_TABLE'
  3  /

no rows selected

SQL>

And you can add another long column if you wish:

SQL> alter table andrews_table
  2  add (col3 long)
  3  /

Table altered.

SQL>

The Curious Case of the Missing PL/SQL Procedure

I have to run files of SQL on databases for developers almost every day. These files often create or recreate PL/SQL packages. Any errors usually go back to the developers for correction as I am not a PL/SQL expert. Yesterday I ran some of these files then a tester asked me why a screen had stopped working. The screen was calling a PL/SQL package which I had just recreated for the developer. I will call it package_b. It had compilation errors and these were causing the screen to fail. The developer was on leave so I decided to have a look at the problem. I listed the compilation errors and could see that they happened when package_b tried to call a new procedure in another package. I will call the other package package_a and I will call the new procedure procedure_a2. I looked for compilation errors in package_a but there were none. I looked at the description for package_a but procedure_a2 was not mentioned. This was because the developer had provided a new body for package_a but no new header. The new header was found and package_a was recompiled. It was then possible to recompile package_b and the tester’s screen started to work again. I have reproduced this problem below on Oracle 11.2.0.2.7:

 

I created package_a and showed that its description contained procedure_a1:

 

SQL> create or replace package package_a is

  2  procedure procedure_a1;

  3  end package_a;

  4  /

 

Package created.

 

SQL> create or replace package body package_a is

  2  procedure procedure_a1 is

  3  begin

  4  null;

  5  end procedure_a1;

  6  end package_a;

  7  /

 

Package body created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

 

SQL>

 

I created package_b containing procedure_b1. This procedure called package_a.procedure_a1. Then I ran package_b.procedure_b1 successfully:

 

SQL> create or replace package package_b is

  2  procedure procedure_b1;

  3  end package_b;

  4  /

 

Package created.

 

SQL> create or replace package body package_b is

  2  procedure procedure_b1 is

  3  begin

  4  package_a.procedure_a1;

  5  end procedure_b1;

  6  end package_b;

  7  /

 

Package body created.

 

SQL> exec package_b.procedure_b1;

 

PL/SQL procedure successfully completed.

 

SQL>

 

I added procedure_a2 to package_a but did not change its header. Then I described it but could only see procedure_a1:

 

SQL> create or replace package body package_a is

  2  procedure procedure_a1 is

  3  begin

  4  null;

  5  end procedure_a1;

  6  procedure procedure_a2 is

  7  begin

  8  null;

  9  end procedure_a2;

10  end package_a;

11  /

 

Package body created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

 

SQL>

 

Then I changed package_b to call package_a.procedure_a2 but it failed to compile:

 

SQL> create or replace package body package_b is

  2  procedure procedure_b1 is

  3  begin

  4  package_a.procedure_a1;

  5  package_a.procedure_a2;

  6  end procedure_b1;

  7  end package_b;

  8  /

 

Warning: Package Body created with compilation errors.

 

SQL> show errors

Errors for PACKAGE BODY PACKAGE_B:

 

LINE/COL

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

ERROR

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

5/1

PL/SQL: Statement ignored

 

5/11

PLS-00302: component 'PROCEDURE_A2' must be declared

 

SQL>

 

I changed the header for package_a. Then its description included procedure_a2:

 

SQL> create or replace package package_a is

  2  procedure procedure_a1;

  3  procedure procedure_a2;

  4  end package_a;

  5  /

 

Package created.

 

SQL> desc package_a

PROCEDURE PROCEDURE_A1

PROCEDURE PROCEDURE_A2

 

SQL>

 

This allowed me to compile package_b and run package_b.procedure_b1 again:

 

SQL> alter package package_b compile

  2  /

 

Package altered.

 

SQL> exec package_b.procedure_b1;

 

PL/SQL procedure successfully completed.

 

SQL>

Oracle 8 Bug

I don't suppose many of you are using Oracle 8 nowadays. Due to a bug in that version, if you tried to reference a synonym pointing to an object which did not exist, this produced a core dump:

  

SQL> create public synonym andrew for andrew;

 

Synonym created.

 

SQL> desc andrew

Stack overflow: pid 9058, proc sqlplus, addr 0x11fdfffd0, pc 0x3ff805900b8

Memory fault(coredump)

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>

Renaming Columns

Tested on an Oracle 9 database. You can rename columns with the alter table command:

SQL> create table with_columns_to_rename
  2  (old_name number)
  3  /

Table created.

SQL> desc with_columns_to_rename
Name                    Null?    Type
----------------------- -------- ----------------
OLD_NAME                        NUMBER

SQL> alter table with_columns_to_rename
  2  rename column old_name to new_name
  3  /

Table altered.

SQL> describe with_columns_to_rename
Name                    Null?    Type
----------------------- -------- ----------------
NEW_NAME                        NUMBER

SQL> 

But if the column to be renamed does not exist, you will get an ORA-00904:

SQL> alter table with_columns_to_rename
  2  rename column missing to found
  3  /
rename column missing to found
              *
ERROR at line 2:
ORA-00904: "MISSING": invalid identifier

SQL>

This has nothing to do with this post, but Oracle allows you to do desc, descr, descri, describ or describe to show the layout of a table:

SQL> descri with_columns_to_rename
Name                    Null?    Type
----------------------- -------- ----------------
NEW_NAME                        NUMBER

SQL>

Nulls First and Nulls Last

(Tested on an Oracle 9 database.)

When Oracle sorts data, nulls come out top in the sort order. So if you sort in ascending order, they appear last:

SQL> create table employees

  2  (name1 varchar2(10))

  3  /

Table created.

SQL> insert into employees values ('Andrew')

  2  /

1 row created.

SQL> insert into employees values ('Fred')

  2  /

1 row created.

SQL> insert into employees values (null)

  2  /

1 row created.

SQL> select nvl(name1,'Null') first_name

  2  from employees

  3  order by name1 asc

  4  /

FIRST_NAME

----------

Andrew

Fred

Null

SQL>

And if you sort in descending order, they are shown first:

SQL> select nvl(name1,'Null') first_name

  2  from employees

  3  order by name1 desc

  4  /

FIRST_NAME

----------

Null

Fred

Andrew

SQL>

If you specify nulls first in the order by statement, the null values will appear before the rest of the data. This applies for both ascending and descending sorts:

SQL> select nvl(name1,'Null') first_name

  2  from employees

  3  order by name1 asc nulls first

  4  /

FIRST_NAME

----------

Null

Andrew

Fred

SQL> select nvl(name1,'Null') first_name

  2  from employees

  3  order by name1 desc nulls first

  4  /

FIRST_NAME

----------

Null

Fred

Andrew

SQL>

Conversely, nulls last makes the null values appear after the other data. Again, this applies for both ascending and descending sorts:

SQL> select nvl(name1,'Null') first_name

  2  from employees

  3  order by name1 asc nulls last

  4  /

FIRST_NAME

----------

Andrew

Fred

Null

SQL> select nvl(name1,'Null') first_name

  2  from employees

  3  order by name1 desc nulls last

  4  /

FIRST_NAME

----------

Fred

Andrew

Null

SQL>

You Cannot Analyze a Table Through a Synonym

This was tested on an Oracle 11 database. First I created a table and analyzed it: 

SQL> create table andrew1 (one_col number)

  2  /

Table created.

SQL> analyze table andrew1 compute statistics

  2  /

Table analyzed.

SQL>

Then I created a synonym for the table:

SQL> create synonym andrew2 for andrew1

  2  /

Synonym created.

SQL>

I found that I was able to describe the table via the synonym:

SQL> desc andrew2

 Name                    Null?    Type

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

 ONE_COL                          NUMBER

SQL>

... but I could not analyze it:

SQL> analyze table andrew2 compute statistics

  2  /

analyze table andrew2 compute statistics

              *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>