HOW TO RENAME A TABLESPACE WITH ORACLE 9 USING BBED
The option to rename a tablespace came only with 10g, but you still can do this change in an oracle version below 10.
This method is not supported by Oracle only if they do it, so I don’t recommend anyone to use in a production environment, only if you really understand what you are doing and will take the responsibility if it doesn’t work, because Oracle will not take it.:)
So use it more as a study experience.
To fully change a tablespace name 3 steps are necessary:
1 - The first one change the dictionary table TS$.
2 – Edit the datafile header using BBED
3 – Recreate the controlfile.
Basically with the first change you will have what you are expecting, what I mean is after that all your segments and views like USER_, ALL_, DBA_, will see the new name, but the views related to datafile header and controlfile will keep showing the old name, and to avoid strange behaviour the correct way is execute all the steps.
The first step:
This change is pretty simple, we just need to use an UPDATE to change the name stored on the base table TS$.
After that you flush the shared pool, forcing the library cache to be updated with the new value.
SQL> select ts#,name from ts$;
TS# NAME
---------- ------------------------------
0 SYSTEM
1 UNDOTBS1
3 TEMP
4 USERS
5 TEST_BLOCK
6 TEST_BLOCK2
SQL> update ts$
2 set name='TESTROD'
3 WHERE TS#=5;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
USERS
TESTROD
TEST_BLOCK2
After that you can check all views USER_,ALL_,DBA_ that show the tablespace name and you will see the new one.
But in views like V$DATAFILE_HEADER or V$TABLESPACE the name is the old one.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
USERS
TEST_BLOCK
TEST_BLOCK2
TEMP
SQL> select tablespace_name from v$datafile_header;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
USERS
TEST_BLOCK
TEST_BLOCK2
Now let’s work on the second step, you can perform everything with the database online, but if someone try to use the datafile you are editing it is going to get an error, so I recommend you shutdown:
In my blog there is another doc talking briefly about BBED, so for more reference take a look there.
In first place we need to generate a datafile file list, that is used with bbed, here is the select:
spool filelist.log
SELECT FILE#||' '||NAME||' '||BYTES FROM V$datafile;
spool off
You should have a file like this, edit if necessary :
1 /oracle/oradata/ims_test/system01.dbf 492830720
2 /oracle/oradata/ims_test/undotbs01.dbf 209715200
4 /oracle/oradata/ims_test/users01.dbf 5242880
5 /oracle/oradata/ims_test/test_block01.dbf 131072
6 /oracle/oradata/ims_test/test_block1.dbf 106496
To enter BBED(remember it is not compiled by default you need to do it manually).
$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Mon Mar 17 13:22:48 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
-- 1 – specify the blocksize our datafile has
BBED> set blocksize 8192
BLOCKSIZE 8192
-- 2 – specify the file list generated before
BBED> set listfile '/oracle/oradata/ims_test/filelist.log'
LISTFILE /oracle/oradata/ims_test/filelist.log
-- 3 – specify the datafile we are going to edit
BBED> set filename '/oracle/oradata/ims_test/test_block01.dbf'
FILENAME /oracle/oradata/ims_test/test_block01.dbf
-- 4 – change to edit mode, then it allow us to change the blocks
BBED> set mode edit
MODE Edit
-- 5 – We need to find the exact offset where the tablespace name is stored within the datafile header, since the datafile header start on the first block we set it to start our search.
The set dba command you specify first the file_id then the block:
BBED> set dba 5,1
DBA 0x01400001 (20971521 5,1)
-- 6 – Use the command find /c to search for a string inside the block, then it will show which is the offset position where the string is stored, in this case 338, the position may change depending on oracle versions and other configuration, that is why we need to perform a search to have sure we are working with the correct data.
If you use the sql:
select dump('TEST_BLOCK',1016) from dual;
You can check if the hexa values are the same:
54,45,53,54,5f,42,4c,4f,43,4b
As you will see they are, that means we got the tablespace name position on the datafile header.
BBED> find /c TEST_BLOCK
File: /oracle/oradata/ims_test/test_block01.dbf (5)
Block: 1 Offsets: 338 to 849 Dba:0x01400001
------------------------------------------------------------------------
54455354 5f424c4f 434b0000 00000000 00000000 00000000 00000000 00000500
-- 7 – To modify the string we use the MODIFY /C command, using the offset information we’ve found. You probably will see that there is a problem with this change, TEST_BLOCK has 10 characters and TESTROD just 7, that means the change we are going to perform with the modify command will let the other 3 bytes, so the string will be TESTRODOCK, so we need to use another Modify command to put 00 values on the 3 bytes that we want to remove..
BBED> modify /c TESTROD dba 5,1 offset 338
File: /oracle/oradata/ims_test/test_block01.dbf (5)
Block: 1 Offsets: 338 to 849 Dba:0x01400001
------------------------------------------------------------------------
54455354 524f444f 434b0000 00000000 00000000 00000000 00000000 00000500
BBED> modify /x 000000 dba 5,1 offset 345
File: /oracle/oradata/ims_test/test_block01.dbf (5)
Block: 1 Offsets: 345 to 856 Dba:0x01400001
------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00000005 00000000 00000000
-- 8 - Another important change is to specify the size of the string, that is used by oracle kernel when reading the header, this byte is 2 bytes behind the initial string byte, so if our string started at 338 the byte saying it’s size it is at 336, as you can see with the dump command starting at offset 336 the current value is 0A which means 10 in decimal that is exactly the size of TEST_BLOCK, the we use the command MODIFY /X (/x because we are doing a hexa decimal change) to modify that byte.
BBED> set offset 336
OFFSET 336
BBED> dump
File: /oracle/oradata/ims_test/test_block01.dbf (5)
Block: 1 Offsets: 336 to 847 Dba:0x01400001
------------------------------------------------------------------------
0a005445 5354524f 44000000 00000000 00000000 00000000 00000000 00000000
BBED> modify /x 07 dba 5,1 offset 336
File: /oracle/oradata/ims_test/test_block01.dbf (5)
Block: 1 Offsets: 336 to 847 Dba:0x01400001
------------------------------------------------------------------------
07005445 5354524f 44000000 00000000 00000000 00000000 00000000 00000000
-- 9 - To confirm the change is ok, set the offset to 0 and use the FIND command again.
BBED> set offset 0
OFFSET 0
BBED> find /c TESTROD
File: /oracle/oradata/ims_test/test_block01.dbf (5)
Block: 1 Offsets: 338 to 849 Dba:0x01400001
------------------------------------------------------------------------
54455354 524f4400 00000000 00000000 00000000 00000000 00000000 00000500
-- 10 – To save the change run the SUM APPLY command that will execute a checksum and correct the tail of the block.
BBED> sum dba 5,1
Check value for File 5, Block 1:
current = 0xa686, required = 0xafcd
BBED> sum dba 5,1 apply
Check value for File 5, Block 1:
current = 0xafcd, required = 0xafcd
After that, the step 2 is completed you can verify the view V$DATAFILE_HEADER and you will see the new tablespace name.
SQL> select tablespace_name from v$datafile_header;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
USERS
TESTROD
TEST_BLOCK2
But that is not all, because the controlfile still has the old tablespace name, take a look:
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
USERS
TEST_BLOCK
TEST_BLOCK2
TEMP
But that is the easiest part, to change the controlfile we recreate it from a controlfile trace backup, when the controlfile is recreated it is going to check the tablespace name from the datafile header.
So create a controlfile trace backup:
SQL> alter database backup controlfile to trace;
Shutdown your database using immediate option, this is very important
SQL> shutdown immediate
And recreate your controlfile using the trace, after that everything should be correct:
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
USERS
TESTROD
TEST_BLOCK2
TEMP
Remember this is not authorized by Oracle it is your responsibility if you use it, so if you think in use this option for real ensure you take a good backup from your database before.