Oracle Data File Operations

Oracle Data File Operations 1. view data files and tablespaces SQL> SELECT * from v $ DATAFILE; SQL> SELECT * FROM DBA_DATA_FILES; SQL> SELECT * FROM V $ TABLESPACES; the distribution of data files and the name of the tablespace can be obtained. 2. move a data file (non-system TABLESPACE) (1) before moving a data file, first move it to the offline state SQL> ALTER TABLESPACE TABLESPACE_NAME OFFILNE; (TABLESPACE_NAME is the name of the TABLESPACE you want to move) (2) check whether the status of the tablespace has been modified successfully. SQL> SELECT TABLESPACE_NAME, ststus from DBA_TABLESPACES; (3) COPY data file SQL> HOST COPY D: \ ORACLE \ ORADATA \ DATA \ TABLESPACE_NAME.DBF D: \ DISK1 \ DATA; after running, copy the tablespace file to D: \ DISK1 \ DATA (4) RENAME the DATA file name corresponding TO the tablespace SQL> ALTER TABLESPACE TABLESPACE_NAME RENAME 2 DATAFILE 'd: \ ORACLE \ ORADATA \ DATA \ TABLESPACE_NAME.DBF '3 TO 'd: \ DISK1 \ DATA \ TABLESPACE_NAME.DBF '; (5) then change the TABLESPACE to the online status SQL> ALTER TABLESPACE TABLESPSCE_NAME ONLINE; view DBA_DATA_FILES, V $ TABLESPACES check whether the status is changed and whether the address points to the new tablespace (6) Delete useless data files. 3. move the data file (system tablespace) (1) the system tablespace must be moved under mount. First, log on to the database as a dba, and then close the database SQL> SHUTDOWN IMMEDIATE; (2) when the database is closed, start the instance and make the database load SQL> startup mount; (3) COPY the DATA file SQL> HOST COPY D: \ ORACLE \ ORADATA \ DATA \ SYSTEM01.DBF D: \ DISK1 \ DATA; (4) RENAME the DATA file name corresponding TO the tablespace SQL> ALTER TABLESPACE TABLESPACE_NAME RENAME 2 DATAFILE 'd: \ ORACLE \ ORADATA \ DATA \ TABLESPACE_NAME.DBF '3 TO 'd: \ DISK1 \ DATA \ TABLESPACE_NAME.DBF '; (5) open database SQL> ALTER DATABASE OPEN; 4. ADD a DATA file (1) ADD a DATA file SQL> ALTER TABLESPACE TABLESPACE_NAME ADD DATAFILE 'd: \ ORADATA \ DATA \ tablespace_name.dbf' SIZE 2 100 M; (2) re-RESIZE the DATA file SQL> ALTER DATABASE DATAFILE 'd: \ ORADATA \ DATA \ tablespace_name.dbf' RESIZE 120 M; auto scaling of oracle Data settings file (autoextend method and Advantages and Disadvantages Analysis) 1. benefits of automatic expansion of data files 1) there is no available space to use and data cannot be written 2) reduce human maintenance as much as possible 3) it can be used in databases with low importance levels, such as test database 2. disadvantages of automatic expansion of data files 1) If you enable it to expand, it will lead to an abnormal large data file during the process of increasing data volume 2) No one manages the database is very dangerous 3. query whether all data files in the current database are automatically extended> select tablespace_name, file_name, autoextensible from dba_data_files where tablespace_name = 'sec _ d '; TABLESPACE_NAME FILE_NAME AUT ----------------- ----------------------------------------- --- SEC_D/data/oracle/dbfile. dbf NO4. modify the data file of SEC_D to automatically expand the table space to achieve automatic expansion.> alter database datafile '/data/oracle/dbfile. dbf 'autoextend on; Database altered.5. check whether the modification is successful> select tablespace_name, file_name, autoextensible from dba_data_files where tablespace_name = 'sec _ d '; TABLESPACE_NAME FILE_NAME AUT ----------------- ----------------------------------------- --- SEC_D/data/oracle/dbfile. dbf YES6. summary modify statement syntax enable auto-scaling Syntax: alter database datafile's data file path information 'autoextend on; Disable auto-scaling Syntax: alter database datafile 'specifies The data file path information' autoextend off; -- The End --