Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to use the RENAME function of the ALTER TABLESPACE command in Oracle 10g to rename the tablespace quickly, which has a certain reference value, and interested friends can refer to it. I hope you can learn a lot after reading this article.

Let's try this convenient and quick way to quickly rename tablespaces by using "RENAME TO" with the "ALTER TABLESPACE" command in the Oracle 10g environment.

1. Query database version information

Sys@ora10g > select * from v$version

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod

PL/SQL Release 10.2.0.1.0-Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0-Production

NLSRTL Version 10.2.0.1.0-Production

two。 Create tablespace TBS_SECOOLER

Sys@ora10g > create tablespace tbs_secooler datafile'/ oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbf' size 10m

Tablespace created.

3. Query the correspondence between tablespaces and data files

Sys@ora10g > col tablespace_name for A20

Sys@ora10g > col file_name for A51

Sys@ora10g > select tablespace_name,file_name from dba_data_files where file_name like'% secooler%'

TABLESPACE_NAME FILE_NAME

TBS_SECOOLER / oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbf

The information here is used for later comparison.

4. Create a user SECOOLER on the TBS_SECOOLER tablespace and authorize

Sys@ora10g > create user secooler identified by secooler default tablespace tbs_secooler

User created.

Sys@ora10g > grant connect,resource to secooler

Grant succeeded.

5. Query the corresponding relationship between users and tablespaces

Sys@ora10g > select username,default_tablespace from dba_users where username = 'SECOOLER'

USERNAME DEFAULT_TABLESPACE

SECOOLER TBS_SECOOLER

The information here is used for subsequent comparisons.

6. Create a test table in the SECOOLER user

Sys@ora10g > conn secooler/secooler

Connected.

Secooler@ora10g > create table t_secooler as select * from all_objects

Table created.

7. Query the corresponding relationship between tables and tablespaces

Secooler@ora10g > conn / as sysdba

Connected.

Sys@ora10g > select segment_name,tablespace_name from dba_segments where segment_name='T_SECOOLER'

SEGMENT_NAME TABLESPACE_NAME

T_SECOOLER TBS_SECOOLER

The information here is used for subsequent comparisons.

8. Adjust tablespace names

We renamed the tablespace TBS_SECOOLER to TBS_ANDY.

Sys@ora10g > alter tablespace TBS_SECOOLER rename to TBS_ANDY

Tablespace altered.

9. Query confirmation after tablespace adjustment

1) query the corresponding relationship between tablespaces and data files

Sys@ora10g > select tablespace_name,file_name from dba_data_files where file_name like'% secooler%'

TABLESPACE_NAME FILE_NAME

TBS_ANDY / oracle/ora10gR2/oradata/ora10g/tbs_secooler_01.dbf

It can be seen that the name of the table space has changed, but the corresponding data file has not changed.

2) query the correspondence between SECOOLER users and tablespaces

Sys@ora10g > select username,default_tablespace from dba_users where username = 'SECOOLER'

USERNAME DEFAULT_TABLESPACE

SECOOLER TBS_ANDY

The default tablespace for SECOOLER users has been adjusted from the original TBS_SECOOLER to the current TBS_ANDY.

3) query the corresponding relationship between table and table space

Sys@ora10g > select segment_name,tablespace_name from dba_segments where segment_name='T_SECOOLER'

SEGMENT_NAME TABLESPACE_NAME

T_SECOOLER TBS_ANDY

The storage tablespace of the table T_SECOOLER has been adjusted from the original TBS_SECOOLER to the current TBS_ANDY.

Through the above tests, it can be seen that using the "RENAME TO" function of the "ALTER TABLESPACE" command can perfectly achieve the goal of quickly renaming the tablespace.

Thank you for reading this article carefully. I hope the article "how to use the RENAME function of ALTER TABLESPACE command to quickly rename tablespaces in Oracle 10g" is helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.