An Oracle blog – Amardeep Sidhu

Many times you need to move datafiles from one location to another. The simplest approach for this is to take the tablespace offline, copy the datafiles to new location, rename the files with alter database rename file (Except that you dont have to move the SYSTEM and UNDO tablespace, as you can’t take SYSTEM tablespace offline)

SYS@orcl AS SYSDBA> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary

SYS@orcl AS SYSDBA>

Well lets try moving USERS tablespace.

SYS@orcl AS SYSDBA> column file_name format a50

SYS@orcl AS SYSDBA> set lines 100
SYS@orcl AS SYSDBA> select file_name,tablespace_name from dba_data_files where tablespace_name='USERS';

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
C:\ORACLE\ORCL\USERS01.DBF                         USERS

SYS@orcl AS SYSDBA>

The current location of the datafile is C:\ORACLE\ORCL\. Suppose I have to move it to c:\oracle\oradata. So first lets take the tablespace offline

SYS@orcl AS SYSDBA> alter tablespace users offline;

Tablespace altered.

SYS@orcl AS SYSDBA>

Now copy the datafile to new location [Note the new directory should be already created]

C:\oracle\ORCL>copy USERS01.DBF c:\oracle\oradata
1 file(s) copied.

C:\oracle\ORCL>

Now make database aware of the new location of the datafile using alter database rename file:

SYS@orcl AS SYSDBA> alter database rename file 'c:\oracle\orcl\users01.dbf' to 'c:\oracle\oradata\users01.dbf';

Database altered.

SYS@orcl AS SYSDBA>

The last thing is to bring the tablespace online. If everything has gone rightly the message like this will appear and you can view the new location of the datafile.

SYS@orcl AS SYSDBA> alter tablespace users online;

Tablespace altered.

SYS@orcl AS SYSDBA> select file_name,tablespace_name from dba_data_files where tablespace_name='USERS';

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
C:\ORACLE\ORADATA\USERS01.DBF                      USERS

SYS@orcl AS SYSDBA>

In next post we will discuss moving datafiles, controlfiles and logfiles.

One may encounter this error while importing from a dmp file from older versions of Oracle. Genereally this error is caused by some statement like alter session set current_schema=scott; And the simple reason is that the user scott doesn’t exist. Yesterday I came across this error. And the reason was that user was not created. As in case of import we generally create tablespaces first (by creating the DDL using option show=Y) but creation of users is done by import itself. In older versions of Oracle, the temp tablespaces were no different from other tablespaces. But in newer versions temp tablespaces are different. So in dmp files from thoese older versions create user statements are written like create user t1 identified by t1 default tablespace temp temporary tablespace temp. This thing worked fine in older versions but in newer versions we cannot specify the TEMP tablespace as the default tablespace for a user. So the statement create user t2 identified by t2 default tablespace temp temporary tablespace temp throws ORA-12910: cannot specify temporary tablespace as default tablespace. In such cases the users (for which default statement is specified as TEMP) have to be created manually by specifying the appropriate tablespace as default tablespace and then the import should be run with ignore=Y.

Sidhu

On OTN someone asked a question that how to spool data from a table into a xls file. Spooling a single table I discussed in one of the previous posts. We can use the same approach to spool data from more than 1 table also. Well here I will do it through a shell script and assume that you have a text file having list of tables to be spooled (Even if you don’t have one, it can be easily made by spooling the names of tables into a simple text file) Here is the shell script that you can use to spool data to various xls files, table wise.

cat list.txt | while read a
do
echo "spooling $a"
sqlplus username/password@string <<EOF
set feed off markup html on spool on
spool /home/oracle/$a.xls
select * from $a;
spool off
set markup html off spool off
EOF
done

I didn’t see any work around for Windoze as SQLPLUS << EOF thing doesn’t seem to work in Windows. Will try to find some alternative. If you come across something, do let me know.

Sidhu

An interesing post by Laurent. Check out http://laurentschneider.com/wordpress/2007/06/to-divide-or-to-multiply.html

My findings on 10gR2 on Windoze XP

SQL> var z numberSQL> var y numberSQL> exec :z := power(2,102)*2e-31;

PL/SQL procedure successfully completed.

SQL> exec :y := 1e125;

PL/SQL procedure successfully completed.

SQL> set timi onSQL> exec while (:y>1e-125) loop :y:=:y/:z; end loop

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10SQL> set timi offSQL> print y

      Y----------9.988E-126

SQL> exec :z := power(2,-104)*2e31;

PL/SQL procedure successfully completed.

SQL> exec :y := 1e125;

PL/SQL procedure successfully completed.

SQL> set timi onSQL> exec while (:y>1e-125) loop :y:=:y*:z; end loop

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04SQL> set timi offSQL> print y

      Y----------9.988E-126

SQL>

Sidhu

A small tip, I read on OTN about spooling to a .xls (excel) file:

It goes like this

set feed off markup html on
spool onspool c:\salgrade.xls
select * from salgrade;
spool offset markup html off
spool off

And the xls it makes shows up like:

Sidhu