Oracle Basic Statement (chapter fifth, database logical storage structure management)

1. Use the SYS user to log in to SQL Plus as SYSDBA and use view V$tablespace to view tablespace information

SELECT * from V$tablespace;

2. View View Dba_tablespaces View the properties and online status of all tablespaces

SELECT tablespace_name, CONTENTS, STATUS from dba_tablespaces;--table space name, table space content, tablespace state

3. Other (slightly)

4. CREATE TABLE Space

CREATE tablespace < tablespace name > datafile < data file name > Size < data file size > EXTENT MANAGEMENT LOCAL autoallocate;

--Local management table space,autoallocate specifies the table space is managed by the system, the minimum interval 64KB; corresponds to autoallocate uniform

CREATE tablespace < tablespace name > datafile < data file name > Size < data file size > EXTENT MANAGEMENT LOCAL UNIFORM size 12 8K;

-- Local management table space, uniform Specify a minimum interval of 128KB

CREATE bigfile tablespace < tablespace name > datafile < data file name > Size < data file size >;

--bigfile keyword to create a large file tablespace

CREATE Temporary tablespace < temp tablespace name > tempfile < temporary file name > size < temp files Initial size > Reuse EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

--temporary tablespace, temporary tablespace is not allowed to use autoallocate, must use UNIFORM,Reuse Description Temporary file can be reused.

5. Segment Management in table space

CREATE tablespace < tablespace name > datafile < data file name > Size < data file size > EXTENT MANAGEMENT LOCAL Autoallocate SEGMENT space MANAGEMENT auto;--Table spaces for automatic segment management

CREATE tablespace < tablespace name > datafile < data file name > Size < data file size > EXTENT MANAGEMENT LOCAL Autoallocate SEGMENT space MANAGEMENT MANUAL;--Table spaces for manual segment management

6. Data block management in table space

CREATE tablespace < tablespace name > datafile < data file name > Size < data file size > EXTENT MANAGEMENT LOCAL Autoallocate BLOCKSIZE 8K;--table space for automatic segment management

7. Undo Table Space

CREATE undo tablespace < tablespace name > datafile < data file name > Size < data file size > reuse;--undo Table Space

DROP Tablespace < table space name >;--Delete table space

Drop tablespace < tablespace name > including contents;--Delete table space while deleting the segment

DROP tablespace < tablespace name > including CONTENTS and datafiles; --Delete the table space while deleting the segments and data files

8. Modify Table Space

ALTER tablespace < tablespace name > RENAME to < new name >;--rename

ALTER tablespace < tablespace name > add datafile < data file name > Size < data file size >;--Add data file

ALTER tablespace < temp tablespace name > add tempfile < temporary file name > size < temporary files Initial size >;--add temporary file to temporary tablespace

Alter Tablespace < tablespace name > RESIZE < data file size >;--Modify large file tablespace size

ALTER Talbespace < tablespace name > OFFLINE;--set tablespace to offline state

ALTER Talbespace < tablespace name > online ;--set table space to online status

ALTER Talbespace < table space name >Read only;--read-only table space

ALTER Talbespace < tablespace name > Read Write;--Readable and writable

9. Other query statements and table space groups (slightly)

10. Assigning temporary table space groups to users, etc. (slightly)