[Oracle Study Notes] create, modify, and delete basic tablespaces

1. Create Table space syntax:

Create [temporary | undo] tablespace tablespace_name

[Datafile | tempfile file_name size k | M [reuse] [autoextent off | on [next number k | M maxsize unlimited | number k | M]

[Blocksize number K]

[Online | offline]

[Logging | nologging]

[Force logging]

[Default Storage]

[Compress | nocompress]

[Permanent | temporary]

[Extent management dictionary | Local [autoallocate | uniform size number k | M]

[Segment space management auto | manual]

 

1. Temporary | undo indicates the purpose of the tablespace. Temporary tablespace/undo tablespace. If not specified, the basic tablespace is identified.

2. If the reuse file exists, the file is cleared and re-created. If it is not used, an error is reported when the file exists.

3. blocksize: create a non-standard data block tablespace

4. Default storage is only applicable to tablespaces managed by data dictionaries.

5. extent management Disk Area Management Mode. Generally, local is used. The dictionary management mode is inefficient in storage, difficult to manage, and easy to cause disk fragmentation.

6. autoallocate, uniform size. This parameter is applicable when the disk area management mode is local. autoallocate indicates that the disk area size is automatically allocated by the system, and uniform indicates that the disk area size is the same for all disks.

7. segment space management segment management mode. Generally, auto is used. In this case, Oracle uses bitmap to manage used and idle blocks in segments. IF Manual is used, oracle uses a list of available blocks to manage used blocks and idle blocks in Segments

Example:

SQL> Create tablespace tls01
2 datafile 'f: \ tls01.dbf' size 20 m autoextend on next 10 m maxsize unlimited;

Query table space attributes through dba_tablespaces:

SQL> DESC dba_tablespaces;
Is the name empty? Type
----------------------------------------------------------------------------

Tablespace_name not null varchar2 (30)
Block_size not null number
Initial_extent number
Next_extent number
Min_extents not null number
Max_extents number
Pct_increase number
Min_extlen number
Status varchar2 (9)
Contents varchar2 (9)
Logging varchar2 (9)
Force_logging varchar2 (3)
Extent_management varchar2 (10)
Allocation_type varchar2 (9)
Plugged_in varchar2 (3)
Segment_space_management varchar2 (6)
Def_tab_compression varchar2 (8)
Retention varchar2 (11)
Bigfile varchar2 (3)

Example:

SQL> select tablespace_name, extent_management, segment_space_management
2 from dba_tablespaces;

Tablespace_name extent_man segmen
----------------------------------------------
System local manual
Undotbs1 local manual
Sysaux local auto
Temp local manual
Users local auto
Tls01 local auto

You have selected 6 rows.

 

2. Modify the tablespace

1. Add a data file

Alter tablespace tablespace_name

Add datafile datafile_specification

Example:

SQL> alter tablespace tls01
2 add datafile 'f: \ tls02.dbf' size 20 m;

 

2. Modify the data file size

First, query dba_data_files to learn the location and size of the data file.

SQL> DESC dba_data_files;
Is the name empty? Type
------------------------------------------------------------------------

File_name varchar2 (513)
File_id number
Tablespace_name varchar2 (30)
Bytes number
Blocks number
Status varchar2 (9)
Relative_fno number
Autoextensible varchar2 (3)
Maxbytes number
Maxblocks number
Increment_by number
User_bytes number
User_blocks number
Online_status varchar2 (7)

SQL> select file_name, file_id, bytes from dba_data_files
2 Where tablespace_name = 'tls01 ';

File_name file_id bytes
-------------------------------------------
F: \ tls01.dbf 5 20971520

F: \ tls02.dbf 6 20971520.

Note: The tablespace name in the data dictionary is in uppercase.

Modify the data file size:

Alter database datafile file_name resize size

Example:

SQL> alter database datafile 'f: \ tls01.dbf' resize 50 m;

3. Modify the automatic expansion of data files

SQL> alter database datafile 'f: \ tls01.dbf' autoextend on;

Also applies to alter tablespace add datafile

4. Modify the tablespace status

Tablespace status: offline, online, read only, read write

SQL> alter tablespace tls01 offline;

5. Move data files

If the disk space of the data file is insufficient, you need to move the data file to another disk:

1. Modify the tablespace Status Offline

2. copy the data file to another disk.

3. Use alter tablespace rename statement

4. Restore the tablespace Status Online

Example:

1) SQL> alter tablespace tls01 offline;

2) copy or cut the data file F: \ tls01.dbf to E: \ tls01.dbf through operating system commands

3) SQL> alter tablespace tls01
2 rename datafile 'f: \ tls01.dbf' to 'e: \ tls01.dbf ';

4) SQL> alter tablespace tls01 online;

Query dba_data_files again. The data file path has changed.

SQL> column file_name format A50

SQL> select file_id, file_name from dba_data_files
2 Where tablespace_name = 'tls01 ';

File_id file_name
------------------------------------------------------------
5 E: \ tls01.dbf
6 F: \ tls02.dbf

 

3. Delete tablespaces

Drop tablespace tablespace_name

| [Including contents]

| [Including contents and datafiles]

Example:

SQL> drop tablespace tls01
2 Including contents and datafiles;

The tablespace has been deleted.