Add data files

Add and modify data files in oracle

---------- Add a data file ---------

Altertablespacetablespace_nameadddatafile '+ ASMDISK1/mydbfile. dbf' size10240M;

------- Manually change the data file size ------

Alterdatabasedatafile '+ ASMDISK1/mydbfile. dbf' resize2048M;

------- Allow automatic expansion of data files ------

Alterdatabasedatafile '+ ASMDISK1/mydbfile. dbf' autoextendonnext10Mmaxsize30G;

---- You can also automatically expand the data file ----

Altertablespacetablespace_nameadddatafile '+ ASMDISK1/mydbfile. dbf' size5Mautoextendonnext5Mmaxsize50G;

------- Add a rollback segment for the tablespace -------

Createrollbacksegmentrollback_nametablespacetablespace_namestorage (initial3knext3koptimal3kminextents3maxextents3)
Alterrollbacksegmentrollback_nameonline;
Note that tablespace_name is modified based on actual conditions. Because my database uses ASM for management, '+ ASMDISK1/mydbfile. dbf' should also be modified based on actual conditions.

Resizing data files

Scale up a tablespace
1. The added bare device must be on both servers. You can see that the user owner is Oraclechownoracle: name.

2. It is best to add this option through an SQL script to check whether the file name is incorrect. 2. You can execute it in the background.

-- Query space size --

Selecttablespace_name, sum (bytes/1024/1024) fromdba_free_spacegroupbytablespace_name;

3. Solve the Problem of incorrect data file creation in the array library.

If you confirm that a file has been added in the check, do not directly delete the file on the operating system using the rm command. This will cause devastating damage to the database. to delete the data file that was mistakenly created, follow these steps: to operate

(1) query the file_id of the file, find the file_name that is equal to the file_id of the problem file, and write down

Selectfile_id, file_namefromdba_data_files;

② Confirm the database objects contained in the problem File

Selectdistinctsegment_name, segment_typefromdba_extents;

(1) For segment_type = TABLE, export each TABLE one by one as exp and drop the TABLE.

(2) For segment_type = LOBSEGMENT, you also need to confirm that the table where the large field is located is as follows:

Selecttable_namefromdba_lobswheresegment_name = '(2) the detected segment_name'; then, repeat step (1) to delete the corresponding Table.

③ Execute the query in step ① again to confirm that no object exists in the file.

4. Shut down the database and restart it in the mount mode to delete the problem in the database.

SQL> shutdownimmediate
SQL> startupmount
SQL> alterdatabasedatafile 'problematic file name' offlinedrop;

5. Open the database alterdatabaseopen;

6. Use imp to re-import all deleted tables.

This article is from the "welove" blog and will not be reposted!