Oracle: Tablespace & amp; Datafile

Oracle: Tablespace & Datafile

-- CREATE a local TABLESPACE (LMT) create tablespace test datafile 'f:/temp/test1.dbf' SIZE 5 m autoextend on next 1 m maxsize 10 M, 'f: /temp/test2.dbf 'size 5 M; -- CREATE a dictionary-managed TABLESPACE (DMT) create tablespace test datafile 'd: \ APP \ ORADATA \ test01.DBF 'size 5 mminimum extent 50 k extent management dictionarydefault storage (INITIAL 50 k next 50 k maxextents 100 PCTINCREASE 0); create tablespace test datafile 'd: \ APP \ ORADATA \ test01.DBF 'size 5 m autoextend on next 1 m maxsize 20 M, 'd: \ APP \ ORADATA \ test02.dbf' SIZE 5 m autoextend offextent management localloggingonlineuniform size 1 M; -- CREATE an index tablespace create tablespace test_indexDATAFILE 'd: \ APP \ ORADATA \ test_index01.DBF 'size 5 mextent management localuniform size 1 M; -- CREATE an undo tablespace create undo tablespace test_undoDATAFILE 'd: \ APP \ ORADATA \ test_undo01.DBF 'size 5 M; -- delete the tablespace drop tablespace test including contents and datafiles; -- query the default tablespace of a database select * FROM database_properties WHERE property_name IN ('default _ TEMP_TABLESPACE ', 'default _ PERMANENT_TABLESPACE'); -- modify the default tablespace of a database alter database default tablespace users; alter database default temporary tablespace TEST_temp; -- it indicates that create tablespace is LOGGING by DEFAULT, and LOCAL MANAGEMENTsystem cannot create dictionary to manage TABLESPACE data dictionary if it is local. -- SELECT. ts #, B. * FROM v $ tablespace a, dba_tablespaces bWHERE. name = B. tablespace_name; -- SELECT * FROM dba_data_files; SELECT * FROM v $ datafile; -- the data file size can be changed by using the file name or file_IDALTER database datafile 'd: \ APP \ ORADATA \ TEST02.DBF 'resize 6 M; alter database datafile 8 RESIZE 8 M; -- ADD the data file alter tablespace test add datafile 'd in the TABLESPACE: \ APP \ ORADATA \ TEST03.DBF 'size 5 m autoextend off; -- create a temporary tablespace. The temporary tablespace can only be standard blocks. It is always nologgingCREATE temporary tablespace test_tempTEMPFILE 'd: \ APP \ ORADATA \ test_temp01.DBF 'size 5 mextent management localuniform size 1 M; SELECT * FROM dba_temp_files; SELECT * FROM v $ tempfile; -- tablespace offline/online alter tablespace TEST_undo OFFLINE; -- If the undo tablespace has a rollback segment, the offline alter tablespace test offline; alter tablespace test online; -- tablespace read-ONLY/READ-write alter tablespace test read only; alter tablespace test read write; /** two ways to move a tablespace **/---- 1. offline alter tablespace test offline; ---- 2. move the file host copy D: \ APP \ ORADATA \ TEST03.DBF D: \ APP \ ORADATA \ TEST13.DBF ---- 3. update the TABLESPACE data file alter tablespace test rename datafile 'd: \ APP \ ORADATA \ TEST03.DBF 'TO 'd: \ APP \ ORADATA \ TEST13.DBF'; ---- 4. online alter tablespace test online; ---- 5. delete the original data file host del D: \ APP \ ORADATA \ TEST03.DBF ---- 1. set the database to the mount status starup force mount; ---- 2. move the file host copy D: \ APP \ ORADATA \ TEST13.DBF D: \ APP \ ORADATA \ TEST03.DBF ---- 3. update the data file of the tablespace, database Name (v $ database. name) alter database demo rename file 'd: \ APP \ ORADATA \ TEST13.DBF 'TO 'd: \ APP \ ORADATA \ TEST03.DBF'; ---- 4. start database alter database open; ---- 5. delete the original data file host del D: \ APP \ ORADATA \ TEST13.DBF -- restore the tablespace test from the TABLESPACE and data file media; recover datafile 'd: \ APP \ ORADATA \ TEST03.DBF '; -- dictionary management --> Local Management EXEC dbms_space_admin.tablespace_migrate_to_local ('test'); -- local management --> dictionary management EXEC dmbs_space_admin.tablespace_migrate_from_local ('test '); -- OMF manages alter system set db_create_file_dest = 'd: \ app \ OraData \ OMF '; create tablespace testomf; alter tablespace testomf add datafile 'd: \ app \ OraData \ OMF \ DEMO \ DATAFILE \ testomf01.dbf 'size 5 M;