Basic usage of ORACLE tablespace creation, management, and deletion

Basic usage of ORACLE tablespace creation, management, and deletion

1. Create a Permanent Table

-- Create a permanent tablespace TEST01 with a size of 50 MB. do not automatically expand the data file create tablespace TEST01loggingdatafile 'f: \ app \ oraclezq \ oradata \ orcl \ test01.dbf' size 50 mreuse autoextend off; -- create permanent tablespace TEST02, which allows automatic expansion of data files. create tablespace TEST02loggingdatafile 'f: \ app \ oraclezq \ oradata \ orcl \ TEST02.dbf 'size 50 mreuse autoextend on next 10 m maxsize 200 Mexico tent management local; -- create permanent tablespace TEST03, which allows automatic expansion of data files, in local management mode, the partition allocation mode is automatically assigned create tablespace TEST03loggingdatafile 'f: \ app \ oraclezq \ oradata \ orcl \ TEST03.dbf 'size 50 mreuse autoextend on next 10 m maxsize 200 Mexico tent management local autoallocate; -- create permanent tablespace TEST04 and allow automatic expansion of data files, local management: create tablespace TEST04loggingdatafile 'f: \ app \ oraclezq \ oradata \ orcl \ TEST04.dbf 'size 50 mreuse autoextend on next 10 m maxsize 200 Mexico tent management local uniform size 10 m; -- create permanent tablespace TEST05, data files can be automatically expanded. Local Management is enabled. partitions are automatically allocated. segments are automatically managed. create tablespace test05loggingdatafile 'f: \ app \ oraclezq \ oradata \ orcl \ TEST05.dbf 'size 50 mreuse autoextend on next 10 m maxsize 200 Mextent management local autoallocatesegment space management auto; -- create permanent tablespace TEST06, data files can be automatically expanded. The local management mode is used. The partition allocation mode is customized. The segment management mode is to manually manage create tablespace test06loggingdatafile 'f: \ app \ oraclezq \ oradata \ orcl \ TEST06.dbf 'size 50 mreuse autoextend on next 10 m maxsize 200 mmexico tent management local uniform size 10 msegment space management manual;

2. Create a temporary table

-- Create a temporary tablespace test07. In order to avoid frequent allocation and recovery of temporary space, the temporary tablespace can only be automatically allocated in the create temporary tablespace test07tempfile 'f: \ app \ oraclezq \ oradata \ orcl \ TEST07.dbf 'size 20 M;

3. Create an undo table

-- Create an undo tablespace test08create undo tablespace test08datafile 'f: \ app \ oraclezq \ oradata \ orcl \ test08.dbf' size 20 M;

4. Manage tablespaces

-- Run the alter tablespace command to specify the location (or name) of the data file in the tablespace. First, set the tablespace to the offline state: alter tablespace TEST03 offline normal. -- create the folder TEST03 manually, copy TEST03.dbf to THE TEST03 folder and rename it to TEST03_1.dbfalter tablespace TEST03 rename datafile 'f: \ app \ oraclezq \ oradata \ orcl \ TEST03.dbf 'to 'f: \ app \ oraclezq \ oradata \ orcl \ TEST03 \ TEST03_1.dbf '; -- set the tablespace to online State alter tablespace TEST03 online;

5. Delete tablespaces

-- Delete table space test01 and its corresponding data file drop tablespace test01 including contents and datafiles; -- delete table space test02 and its corresponding data file drop tablespace test02 including contents and datafiles; -- delete table space test03 and its corresponding data file drop tablespace test03 including contents and datafiles; -- delete table space test04 and its corresponding data file drop tablespace test04 including contents and datafiles; -- delete table space test05 and its corresponding data files drop tablespace test05 including contents and datafiles; -- delete table space test06 and its corresponding data files drop tablespace test06 including contents and datafiles; -- delete table space test07 and its corresponding data file drop tablespace test07 including contents and datafiles; -- delete table space test08 and its corresponding data file drop tablespace test08 including contents and datafiles;

The result is as follows: Use the delete statement to delete the file.