How to use the create/offline/drop/rename method of datafile 02/22 Update SLTechnology News&Howtos

How to use the create/offline/drop/rename method of datafile

2025-02-22 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use the create/offline/drop/rename method of datafile". In the daily operation, I believe that many people have doubts about how to use the create/offline/drop/rename method of datafile. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use the create/offline/drop/rename method of datafile". Next, please follow the editor to study!

First, create a tablespace:

Create tablespace test1

Datafile'/ u01 size size 10m

Autoextend on next 1M maxsize 2G

Extent management local uniform size 1M

Segment space management auto

Tablespace created.

SQL >

Second, change the status of the table space:

SYS > alter tablespace test1 read only

Tablespace altered.

SQL > select tablespace_name,initial_extent/1024/1024 MANAGENTPROTENTGENMENTPROLOGICATIONTYPEN SEGMENTSPACEMANAGEN status from dba_tablespaces where tablespace_name='TEST1'

TABLESPACE_NAME M EXTENT_MAN ALLOCATIO SEGMEN STATUS

TEST1 1 LOCAL UNIFORM AUTO READ ONLY

SYS > alter tablespace test1 read write

Tablespace altered.

SQL > select tablespace_name,initial_extent/1024/1024 MANAGENTPROTENTGENMENTPROLOGICATIONTYPEN SEGMENTSPACEMANAGEN status from dba_tablespaces where tablespace_name='TEST1'

TABLESPACE_NAME M EXTENT_MAN ALLOCATIO SEGMEN STATUS

TEST1 1 LOCAL UNIFORM AUTO ONLINE

SQL >

3. Rename the tablespace: (modify the tablespace name online)

SYS > alter tablespace test1 rename to sales

Tablespace altered.

SQL > select tablespace_name,initial_extent/1024/1024 MANAGENTPROTENTGENMENTPROLOGICATIONTYPEN SEGMENTSPACEMANAGEN status from dba_tablespaces where tablespace_name='TEST1'

No rows selected

SQL > select tablespace_name,initial_extent/1024/1024 MANAGENTPROTENTGENMENTPROLOGICATIONTYPEN SEGMENTSPACEMANAGEN status from dba_tablespaces where tablespace_name='SALES'

TABLESPACE_NAME M EXTENT_MAN ALLOCATIO SEGMEN STATUS

SALES 1 LOCAL UNIFORM AUTO ONLINE

Fourth, query tablespace information:

SQL > select s.filewriting name, dbathing tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME. BytesUniverse 1024 size_m,d.status,d.contents from dba_data_files

FILE_NAME TABLESPACE_NAME SIZE_M STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/users01.dbf USERS 3466.25 ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/undotbs01.dbf UNDOTBS1 3330 ONLINE UNDO

/ u01/app/oracle/oradata/DBdb/sysaux01.dbf SYSAUX 740 ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/system01.dbf SYSTEM 2800 ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/example01.dbf EXAMPLE 338.75 ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/test101.dbf SALES 10 ONLINE PERMANENT

6 rows selected.

SQL >

SQL > select s.fileroomnamewritings.tablespaceroomnamewritings.bytesUniq1024and1024 size_m,d.status,d.contents from dba_data_files s dbathing tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACENAMERS

FILE_NAME TABLESPACE_NAME SIZE_M STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf SALES 10 ONLINE PERMANENT

5. There are three ways to change the size of the table space:

1. Alter tablespace sales add datafile'/ u01 size

2. Alter database datafile'/ u01 autoextend on maxsize

3 alter database datafile'/ u01 resize

VI. Offline

SYS > alter tablespace sales offline

Tablespace altered.

SQL > select tablespace_name,initial_extent/1024/1024 MANAGENTPROTENTGENMENTPROLOGICATIONTYPEN SEGMENTSPACEMANAGEN status from dba_tablespaces where tablespace_name='SALES'

TABLESPACE_NAME M EXTENT_MAN ALLOCATIO SEGMEN STATUS

SALES 1 LOCAL UNIFORM AUTO OFFLINE

FILE_NAME TABLESPACE_NAME SIZE_M STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf SALES OFFLINE PERMANENT

SQL >

Delete tablespaces

SYS > create table sales_1 (id number)

2 tablespace sales

Table created.

SYS > select table_name,tablespace_name from dba_tables where tablespace_name='SALES'

TABLE_NAME TABLESPACE_NAME

SALES_1 SALES

SYS > drop tablespace sales

Drop tablespace sales

ERROR at line 1:

ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

To delete an object, use the following statement:

Drop tablespace sales INCLUDING CONTENTS

Eight: OMF

SQL > show parameter db_create

NAME TYPE VALUE

Db_create_file_dest string

Db_create_online_log_dest_1 string

Db_create_online_log_dest_2 string

Db_create_online_log_dest_3 string

Db_create_online_log_dest_4 string

Db_create_online_log_dest_5 string

SQL >! mkdir-p / u01/app/oracle/oradata/omf

SQL > alter system set db_create_file_dest='/u01/app/oracle/oradata/omf'

System altered.

SQL > show parameter db_create

NAME TYPE VALUE

Db_create_file_dest string / u01/app/oracle/oradata/omf

Db_create_online_log_dest_1 string

Db_create_online_log_dest_2 string

Db_create_online_log_dest_3 string

Db_create_online_log_dest_4 string

Db_create_online_log_dest_5 string

SQL >

SQL > create tablespace test2

Tablespace created.

SQL > col file_name for a804

SQL > col file_name for A80

SQL > select tablespace_name,file_name from dba_data_files where tablespace_name='TEST2'

TABLESPACE_NAME FILE_NAME

TEST2 / u01/app/oracle/oradata/omf/DBDB/datafile/o1_mf_test2_f2x469nq_.dbf

SQL >! ls / u01/app/oracle/oradata/omf/DBDB/datafile

O1_mf_test2_f2x469nq_.dbf

SQL >

SQL >! ls / u01/app/oracle/oradata/omf/DBDB/datafile

O1_mf_test2_f2x469nq_.dbf

SQL >

SQL > drop tablespace test2

Tablespace dropped.

SQL >! ls / u01/app/oracle/oradata/omf/DBDB/datafile

SQL >

Delete the tablespace and then check that there is no data file for the tablespace under the physical path of the operating system.

SQL > show parameter db_create

NAME TYPE VALUE

Db_create_file_dest string / u01/app/oracle/oradata/omf

Db_create_online_log_dest_1 string

Db_create_online_log_dest_2 string

Db_create_online_log_dest_3 string

Db_create_online_log_dest_4 string

Db_create_online_log_dest_5 string

SQL >

SQL > create tablespace test2 datafile'/ u01 size

Tablespace created.

SQL > select tablespace_name,file_name from dba_data_files where tablespace_name='TEST2'

TABLESPACE_NAME FILE_NAME

TEST2 / u01/app/oracle/oradata/DBdb/test2.dbf

SQL >! ls-lrt / u01/app/oracle/oradata/omf/DBDB/datafile/

Total 0

SQL >! ls-lrt / u01/app/oracle/oradata/DBdb/test2.dbf

-rw-r- 1 oracle oinstall 5251072 Dec 11 22:12 / u01/app/oracle/oradata/DBdb/test2.dbf

SQL >

-- close OMF:

SQL > alter system set db_create_file_dest=''

System altered.

SQL > show parameter db_create

NAME TYPE VALUE

Db_create_file_dest string

Db_create_online_log_dest_1 string

Db_create_online_log_dest_2 string

Db_create_online_log_dest_3 string

Db_create_online_log_dest_4 string

Db_create_online_log_dest_5 string

SQL >

IX. Modify the availability of data files

The availability of the data file can be modified by performing online and offline operations of the data file, and the offline data file cannot be accessed by the database until it is restored online. Data files in read-only tablespaces can also be offline or online, and the online or offline status of data files in read-only tablespaces does not affect the state of the tablespace itself, in any case, before the tablespace is read and written, these files are not writable.

9.1 View the status of the tablespace that was previously renamed sales: (executing 0ffline)

SQL > select s.fileroomnamewritings.tablespaceroomnamewritings.bytesUniq1024and1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s dbathing tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACENAMERS

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf SALES OFFLINE OFFLINE PERMANENT

SQL >

-- modify the tablespace name:

SQL > alter tablespace SALES rename to test1

Alter tablespace SALES rename to test1

ERROR at line 1:

ORA-01135: file 6 accessed for DML/query is offline

ORA-01110: data file 6:'/ u01 *

SQL > alter tablespace sales online

Tablespace altered.

SQL > alter tablespace SALES rename to test1

Tablespace altered.

SQL > select s.fileroomnamewritings.tablespaceroomnamewritings.bytesUniverse 1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files s dbathing tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME and s.TABLESPACENAMENTNAMEST1'

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 10 ONLINE ONLINE PERMANENT

SQL >

9.2 offline data files in archive mode

SQL > alter tablespace TEST1 online

Tablespace altered.

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 488

Next log sequence to archive 490

Current log sequence 490

SQL >

SQL > alter database datafile'/ u01 offline

Database altered.

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT

SQL > alter database datafile'/ u01 online

Alter database datafile'/ u01 online

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6:'/ u01 *

Verify that when offline data files are re-online, recover data files are required, while offline tablespaces can execute online, such as recover and online data files

SQL > recover datafile 6

Media recovery complete.

SQL >

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 OFFLINE ONLINE PERMANENT

SQL >

SQL > alter database datafile'/ u01 online

Database altered.

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 10 ONLINE ONLINE PERMANENT

SQL >

9.2. Data files in non-archived mode are offline

Use alter database in non-archive mode. Offline for drop statement offline data file. The offline keyword marks the data file offline, regardless of whether it is corrupted or not, so the database can be opened; the for drop keyword marks that the data file is then deleted (just the tag, the physical file is still there), and the data file cannot be restored online again. (in fact, you can return to the online state before the online log group is switched over.)

9.2.1 first simulate the offline for drop when the online log is not switched

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL >

SQL > startup mount

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 549456976 bytes

Database Buffers 281018368 bytes

Redo Buffers 2371584 bytes

Database mounted.

SQL >

SQL > alter database noarchivelog

Database altered.

SQL > alter database open

Database altered.

SQL > archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 488

Current log sequence 490

SQL >

-- query the current log log group:

SQL > select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log

GROUP# SIZE_M MEMBERS ARC STATUS

1 50 1 NO CURRENT

2 50 1 YES INACTIVE

3 50 1 YES INACTIVE

-- perform offline for drop operation:

SQL > alter database datafile 6 offline for drop

Database altered.

SQL >! ls / u01/app/oracle/oradata/DBdb/test101.dbf

/ u01/app/oracle/oradata/DBdb/test101.dbf

SQL > select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log

GROUP# SIZE_M MEMBERS ARC STATUS

1 50 1 NO CURRENT

2 50 1 YES INACTIVE

3 50 1 YES INACTIVE

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT

-- querying online logs has not been switched, so recover can be performed.

SQL > recover datafile 6

Media recovery complete.

SQL >

SQL > alter database datafile 6 online

Database altered.

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 10 ONLINE ONLINE PERMANENT

SQL > select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log

GROUP# SIZE_M MEMBERS ARC STATUS

1 50 1 NO CURRENT

2 50 1 YES INACTIVE

3 50 1 YES INACTIVE

9.2.2 offline for drop is performed after the simulation online log group has been switched and the log has been overwritten

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 10 ONLINE ONLINE PERMANENT

SQL > select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log

GROUP# SIZE_M MEMBERS ARC STATUS

1 50 1 NO CURRENT

2 50 1 NO INACTIVE

3 50 1 NO INACTIVE

SQL > alter database datafile 6 offline for drop

Database altered.

SQL > alter system switch logfile

System altered.

SQL > /

System altered.

SQL > select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log

GROUP# SIZE_M MEMBERS ARC STATUS

1 50 1 NO ACTIVE

2 50 1 NO ACTIVE

3 50 1 NO CURRENT

SQL > alter system switch logfile

System altered.

SQL > select GROUP#,BYTES/1024/1024 size_m,MEMBERS,ARCHIVED,STATUS from v$log

GROUP# SIZE_M MEMBERS ARC STATUS

1 50 1 NO CURRENT

2 50 1 NO INACTIVE

3 50 1 NO INACTIVE

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT

SQL >! ls / u01/app/oracle/oradata/DBdb/test101.dbf

/ u01/app/oracle/oradata/DBdb/test101.dbf

SQL > alter database datafile 6 online

Alter database datafile 6 online

ERROR at line 1:

ORA-01113: file 6 needs media recovery

ORA-01110: data file 6:'/ u01 *

SQL > recover datafile 6

ORA-00279: change 5518174 generated at 12/12/2017 00:06:18 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_12/o1_mf_1_496_%u_.arc

ORA-00280: change 5518174 for thread 1 is in sequence # 496

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

ORA-00308: cannot open archived log'/ u01 ActionActionoracleActionfastfastened recoveryCharacareaDBBUBGUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBG

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log'/ u01 ActionActionoracleActionfastfastened recoveryCharacareaDBBUBGUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBUBG

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

There are no archived files available to recover datafile 6, so file 6 cannot be used.

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT

SQL > alter tablespace test1 add datafile'/ u01 size

Tablespace altered.

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/test102.dbf TEST1 10 ONLINE ONLINE PERMANENT

SQL >

10. Rename (modify storage location) data file

The steps are as follows:

Method 1

1. Take the tablespace that contains the data file or just take a data file offline.

2. Use operating system commands to modify the data file name.

3. Use alter database... The rename file'to'; statement changes the name of the data file in the database.

4. Back up the database.

Method 2:

1. Start the database shutdown under mount

two。 Use operating system commands to modify the data file name.

3. Use alter database... The rename file'to'; statement changes the name of the data file in the database.

4. Open the library and back up.

Method 1:

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/test102.dbf TEST1 10 ONLINE ONLINE PERMANENT

SQL >

SQL > alter tablespace TEST1 rename datafile'/ u01ActionApplicationCharacleCharacleoradataUnitedDBdbUnitest102.dbf'to'/ u01Lax oracle.oradataUniplicationoradataloradataandDBdbUnitest103.dbf'

Alter tablespace TEST1 rename datafile'/ u01 to to'/ u01 apprenticeship oradata

ERROR at line 1:

ORA-01525: error in renaming data files

ORA-01121: cannot rename database file 8-file is in use or recovery

ORA-01110: data file 8:'/ u01 *

[oracle@wang 2017 / 12 / 12] $oerr ora 01145

01145, 00000, "offline immediate disallowed unless media recovery enabled"

/ / * Cause: ALTER TABLESPACE... OFFLINE IMMEDIATE or ALTER DATABASE DATAFILE

/ /... OFFLINE is only allowed if database is in ARCHIVELOG mode.

/ / * Action:Take tablespace offline normally or shutdown abort. Reconsider your

/ / backup strategy. You could do this if you were archiving your logs.

[oracle@wang 2017 / 12 / 12] $

Open an archive:

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 549456976 bytes

Database Buffers 281018368 bytes

Redo Buffers 2371584 bytes

Database mounted.

SQL > alter database archivelog

Database altered.

SQL > alter database open

Database altered.

SQL >

Try offline:

SQL > alter database datafile'/ u01 offline

Database altered.

SQL >

Changing the name of the data file using rename

-- create the corresponding name of the physical disk first

[oracle@wang 2017 December 12] $cd / u01/app/oracle/oradata/DBdb/

[oracle@wang DBdb] $cp test102.dbf test1022222.dbf

[oracle@wang DBdb] $ls-lrt test*

-rw-r- 1 oracle oinstall 10493952 Dec 12 00:10 test101.dbf

-rw-r- 1 oracle oinstall 5251072 Dec 12 00:32 test2.dbf

-rw-r- 1 oracle oinstall 10493952 Dec 12 00:32 test102.dbf

-rw-r- 1 oracle oinstall 10493952 Dec 12 00:39 test1022222.dbf

[oracle@wang DBdb] $

-- formal operation:

SQL > alter database rename file'/ u01ActionApplicationCharacleCharacleoradata Universe DBdbUniverse test102.dbf'to'/ u01LigameApplicationoracleGreoradata GreatDBDB Grease Test1022222.dbf'

Database altered.

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/test1022222.dbf TEST1 RECOVER ONLINE PERMANENT

SQL >

Online new data file

SQL > alter database datafile'/ u01 online

Alter database datafile'/ u01 online

ERROR at line 1:

ORA-01113: file 8 needs media recovery

ORA-01110: data file 8:'/ u01 *

-- recovery

SQL > recover datafile 8

Media recovery complete.

SQL >

SQL > alter database datafile'/ u01 online

Database altered.

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/test1022222.dbf TEST1 10 ONLINE ONLINE PERMANENT

SQL >

Method 2:

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL >

SQL > startup mount

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 549456976 bytes

Database Buffers 281018368 bytes

Redo Buffers 2371584 bytes

Database mounted.

SQL >

Rename data file:

SQL > alter database rename file'/ u01ActionApplicationCharacleCharacleoradata Universe DBdbUniverse test1022222.dbf' to'/ u01Uniplicalappplicable oradataloradataGrease DBdbUniverse test3333.dbf'

Alter database rename file'/ u01qaphort oracle.oradata; DBdbUniverse test10222.dbf' to'/ u01; appplicableoradata; oradatal.oradata; DBdbbank; test3333.dbf'

ERROR at line 1:

ORA-01511: error in renaming log/data files

ORA-01141: error renaming data file 8-new file'/ u01 not found

ORA-01110: data file 8:'/ u01 *

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

/ u01/app/oracle/oradata/DBdb/test3333.dbf not found, create:

[oracle@wang DBdb] $pwd

/ u01/app/oracle/oradata/DBdb

[oracle@wang DBdb] $

[oracle@wang DBdb] $cp test1022222.dbf test3333.dbf

[oracle@wang DBdb] $

Execute again:

SQL > alter database rename file'/ u01ActionApplicationCharacleCharacleoradata Universe DBdbUniverse test1022222.dbf' to'/ u01Uniplicalappplicable oradataloradataGrease DBdbUniverse test3333.dbf'

Database altered.

SQL > alter database open

Database altered.

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/test3333.dbf TEST1 10 ONLINE ONLINE PERMANENT

SQL >

SQL > select s.filewriting name, dbathing tablespaces d where s.TABLESPACE_NAME=d.TABLESPACE_NAME. BytesUniverse 1024 size_m,s.ONLINE_STATUS,d.status,d.contents from dba_data_files

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/users01.dbf USERS 3466.25 ONLINE ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/undotbs01.dbf UNDOTBS1 3330 ONLINE ONLINE UNDO

/ u01/app/oracle/oradata/DBdb/sysaux01.dbf SYSAUX 740 ONLINE ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/system01.dbf SYSTEM 2800 SYSTEM ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/example01.dbf EXAMPLE 338.75 ONLINE ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/test2.dbf TEST2 5 ONLINE ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/test3333.dbf TEST1 10 ONLINE ONLINE PERMANENT

8 rows selected.

SQL >

Delete data files

Delete data files from tablespaces: syntax

Alter tablespace tablespace_name {ADD {DATAFILE | TEMPFILE}

[file_specification [, file_specification]... ]

| | DROP {DATAFILE | TEMPFILE} {'filename' | file_number} |

| | SHRINK TEMPFILE {'filename' | file_number} [KEEP size_clause] |

| RENAME DATAFILE 'filename' [,' filename'].

TO 'filename' [,' filename']...

| | {DATAFILE | TEMPFILE} {ONLINE | OFFLINE} |

}

SQL > alter tablespace test1 drop datafile'/ u01Accord oradataPlus DBdbUniverse test3333.dbf'

Tablespace altered.

Success!

SQL > alter tablespace test1 drop datafile'/ u01ActionApp Universe oradataPlus DBdbUniverse test101.dbf'

Alter tablespace test1 drop datafile'/ u01qapplash oracle.oradataDB dbUniver test101.dbf'

ERROR at line 1:

ORA-03261: the tablespace TEST1 has only one file

An error indicates that there is only one data file in the tablespace, which cannot be deleted.

SQL > alter tablespace test1 add datafile'/ u01 size size 5m

Tablespace altered.

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS

/ u01/app/oracle/oradata/DBdb/test101.dbf TEST1 RECOVER ONLINE PERMANENT

/ u01/app/oracle/oradata/DBdb/test101bak.dbf TEST1 5 ONLINE ONLINE PERMANENT

SQL > alter tablespace test1 drop datafile'/ u01ActionApp Universe oradataPlus DBdbUniverse test101.dbf'

Alter tablespace test1 drop datafile'/ u01qapplash oracle.oradataDB dbUniver test101.dbf'

ERROR at line 1:

ORA-03263: cannot drop the first file of tablespace TEST1

[oracle@wang DBdb] $oerr ora 03263

03263, 00000, "cannot drop the first file of tablespace's"

/ / * Cause: Trying to drop the first datafile with which ts is created

/ / * Action: Cannot drop the first datafile with which ts is created

[oracle@wang DBdb] $

An error indicates that the first data file in the tablespace cannot be deleted

Note:

1. Data files cannot be deleted when migrating from dictionary management to locally managed read-only tablespaces. In addition, other data files in the read-only tablespace can be deleted.

2. The data files in the system tablespace cannot be deleted.

3. If a locally managed table space is offline, the data files in it cannot be deleted.

SQL > alter tablespace test_tbs drop datafile'/ u01ActionAccording to oradataAccord, oradataCharpy, stdbUniverse test4.dbf'

Alter tablespace test_tbs drop datafile'/ u01qqappUniqr oracle.oradataAccording to stdbUnip test4.dbf'

ERROR at line 1:

ORA-03264: cannot drop offline datafile of locally managed tablespace

4. If there is only one data file in the tablespace, the data file cannot be deleted.

SQL > alter tablespace test_tbs drop datafile'/ u01qqappActionoradataUniplicateoracle.oradataUniple

Alter tablespace test_tbs drop datafile'/ u01qqappUniqr oracle.oradataAccording to stdbUnip test3.dbf'

ERROR at line 1:

ORA-03261: the tablespace TEST_TBS has only one file

5. If the data file is not empty, the data file cannot be deleted.

6. To delete a data file, you must ensure that the data block is open.

7. You cannot delete the first created data file under the tablespace:

SQL > alter tablespace test1 drop datafile'/ u01ActionApp Universe oradataPlus DBdbUniverse test101.dbf'

Alter tablespace test1 drop datafile'/ u01qapplash oracle.oradataDB dbUniver test101.dbf'

ERROR at line 1:

ORA-03263: cannot drop the first file of tablespace TEST1

At this point, the study on "how to use the create/offline/drop/rename method of datafile" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.