Oracle (data files)

--Create a data fileCreateTablespace--Create a table space and create a data file at the same timeCreate TemporaryTablespace--Create a temporary data file while creating a temporary table spaceAlterTablespace ...AddDataFile--to add a data file to a table spaceAlterTablespace ...AddTempfile--to add a data file to a temporary table spaceCreate Database --Create a data file when creating a databaseAlter Database...CreateDatefile--data felt restore operation, create a new data file to replace the failed data file--ExampleAlterTablespace usersAddDataFile'd:\app\administrator\oradata\orcl\users02.dbf'size 10m;--Modify data File sizeSelect file_name, autoextensible fromDba_data_files;--View how all data files in the current database are expanded. --ExampleAlter DatabaseDataFile'd:\app\administrator\oradata\orcl\users04.dbf'Size 10m autoextend on Next512k maxsize 100m;--change the availability of data files--to take an offline backup of a data file, you need to take the data file offline first. --When you need to rename a data file or change the location of a data file, you need to take the data file offline first. --If an error occurs when Oracle writes to a data file, the data file is automatically set to an offline state and recorded in an alert file. After troubleshooting, you need to manually re-restore the data file to an online state. --data files are missing or corrupted, you need to take the data files offline before starting the database. --In the archive mode:Alter DatabaseDatafile...online|Offline--set up offline and online persistent data files. Alter DatabaseTempfile...online|Offline--sets the temporary data file offline and online. --set the ORCL database to archive mode. shutdownImmediate--database has been closed, has been unloaded database, example Cheng closedStartup Mount--Example Cheng start--ExampleAlter Databasedatafile'd:\app\administrator\oradata\orcl\users01.dbf'Offline--OfflineRecover DataFile'd:\app\administrator\oradata\orcl\users01.dbf';--Complete Media recovery. Alter Databasedatafile'd:\app\administrator\oradata\orcl\users01.dbf'Online--Online--offline data files in non-archive mode--because the database is in non-archive mode, when the data file goes offline, it causes the information to be lost, so that the data file can no longer be brought online.--that's not working. Therefore, in non-archive mode, it is often not possible toshutdownImmediatestartup MountAlter DatabaseNoarchivelog;Alter Database Open;Alter DatabaseDataFile'd:\app\administrator\product\11.2.0\dbhome_1\database\users3.dbf'Offline for Drop;Select file_name, fromDba_data_fileswhereTablespace_name= 'Users';--Note Using this statement simply takes the data file offline, the data file is not available, the data file information is not removed from the data dictionary and the control file, and the data file is not deleted from the disk. --the file can then be deleted with the deletion of the table space to which it belongs. Altertablespace users datafile offline;SelectName,status fromV$datafilewherets#=(Selectts# fromV$tablespacewhereName='Users');--View the status of individual data files in the Users table spacerecover TABLESPCE users;--change the name and location of the data fileAlterTablespacle...rename datafile ... to--change the name and location of the data file in the same tablespace. Alter DatabaseRenamefile... to--change the name and location of data files in multiple table spaces--ExampleSelect file_name, bytes fromDba_data_fileswhereTablespace_name= 'Users';Altertablespace users datafile offline;host copy d:\app\administrator\oradata\orcl\users02.dbf d:\disk1\ Users02.dbfhost copy d:\app\administrator\oradata\orcl\users04.dbf d:\disk1\users04.dbfAltertablespace users Rename datafile'd:\app\administrator\oradata\orcl\users02.dbf','d:\app\administrator\oradata\orcl\users04.dbf'  to'd:\disk1\users02.dbf','d:\disk1\users02.dbf'Altertablespace users online;--to modify the name and location of data files in multiple table spacesSelect file_name, bytes fromDba_data_fileswhereTablespace_nameinch('Users','Example');shutdownimmediatestartup mounthost copy d:\app\administrator\oradata\orcl\users01.dbf d:\disk2\users01.dbfhost copy d:\ APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF d:\disk2\example01.dbfAlter DatabaseRenamefile_nameAlter Database Open;--Delete a data fileAlterTablespace ...DropDataFile--deletes the empty data file specified in the persistent tablespace. AlterTablespace ...DropTempfile--deletes the empty temporary data file specified in the staging tablespace. --ExampleAlterTablespace usersDropdatafile'd:\app\administrator\product\11.2.0\dbhome_1\database\users03.dbf';AlterTablespaceTemp DropTempfile'd:\app\administrator\oradata\orcl\temp02.dbf';Alter DatabaseTempfile'd:\app\administrator\oradata\orcl\temp02.dbf'Drop;--Querying data FilesDba_data_files--contains information about all data files of the database, including the table space to which the data file belongs, data file number, etc.Dba_temp_files--contains information about all temporary data files for a databaseDba_extents--contains descriptive information for all allocated extents in all table spacesUser_extents--contains descriptive information about the areas that the current user owns that are allocated in all tablespacesDba_free_space--contains descriptive information about the idle area in the tablespaceUser_free_space--contains descriptive information about the idle area in the table space that the current user can accessV$datafile--contains data file information obtained from the control fileV$datafile_header--contains information obtained from the head of the data fileV$tempfile--contains basic information for all temporary data files

Oracle (data files)