逻辑结构操作(表空间，数据文件，段，块，区)

1.查询表空间信息

SQL> select * from v$tablespace;

2.查询表空间及数据文件的信息

SQL> select file_name,tablespace_name from dba_data_files;

3.创建表空间，并设置数据文件为自动扩展

SQL> create tablespace mytbs1 datafile '/oracle/app/oracle/oradata/orcl/mytbs01.dbf' size 5m autoextend on next 1m maxsize 50m;

验证:

SQL> select file_name,autoextensible from dba_data_files where tablespace_name='MYTBS1';


FILE_NAME
--------------------------------------------------------------------------------
AUT
---
/oracle/app/oracle/oradata/orcl/mytbs01.dbf
YES

4.修改数据文件为自动扩展

SQL> alter database datafile '/oracle/app/oracle/oradata/orcl/tbs01.dbf' autoextend on next 1m maxsize unlimited
  2  ;

验证:

SQL> select file_name,autoextensible from dba_data_files; 

5.禁用文件的自动扩展

SQL> alter database datafile '/oracle/app/oracle/oradata/orcl/tbs01.dbf' autoextend off;


Database altered.

6.为表空间增加数据文件

SQL> alter tablespace tbs1 add datafile '/oracle/app/oracle/oradata/orcl/tbs01b.dbf' size 5m;


Tablespace altered.

7.更改表空间中数据文件的大小

SQL> alter database datafile '/oracle/app/oracle/oradata/orcl/tbs01b.dbf' resize 10m;


Database altered.

8.表空间只读，该表空间中的表不能插入，但能够drop

SQL> alter tablespace tbs1 read only;


Tablespace altered.

SQL> insert into t values(1);
insert into t values(1)
            *
ERROR at line 1:
ORA-00372: file 7 cannot be modified at this time
ORA-01110: data file 7: '/oracle/app/oracle/oradata/orcl/tbs01.dbf'

SQL> delete from t;
delete from t
            *
ERROR at line 1:
ORA-00372: file 7 cannot be modified at this time
ORA-01110: data file 7: '/oracle/app/oracle/oradata/orcl/tbs01.dbf'

SQL> update t set id=2;
update t set id=2
       *
ERROR at line 1:
ORA-00372: file 7 cannot be modified at this time
ORA-01110: data file 7: '/oracle/app/oracle/oradata/orcl/tbs01.dbf'

SQL> drop table t;


Table dropped.

9.脱机状态下，同样不能做insert操作，但可以drop

SQL> alter tablespace tbs1 offline;


Tablespace altered.


SQL> insert into t values(1);
insert into t values(1)
            *
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/oracle/app/oracle/oradata/orcl/tbs01.dbf'




SQL> drop table t;


Table dropped.

10.将数据文件改为脱机状态，过一段时间再改为联机状态

SQL> alter database datafile '/oracle/app/oracle/oradata/orcl/tbs01.dbf' offline;


Database altered.

SQL> alter database datafile '/oracle/app/oracle/oradata/orcl/tbs01.dbf' online;


Database altered.

11.移动表空间的数据文件

SQL> alter tablespace tbs1 offline;


Tablespace altered.


SQL> ho mv /oracle/app/oracle/oradata/orcl/tbs01b.dbf /oracle/app/oracle/oradata/tbs01b.dbf


SQL> alter tablespace tbs1 rename datafile '/oracle/app/oracle/oradata/orcl/tbs01b.dbf' to '/oracle/app/oracle/oradata/tbs01b.dbf';


Tablespace altered.

SQL> alter tablespace tbs1 online;


Tablespace altered.

12.移动system表空间

SQL> shutdown immediate;

SQL> ho mv /oracle/app/oracle/oradata/system01.dbf /oracle/app/oracle/oradata/orcl/system01.dbf

SQL> startup mount;

SQL> alter database rename file '/oracle/app/oracle/oradata/system01.dbf' to '/oracle/app/oracle/oradata/orcl/system01.dbf';


Database altered.

SQL> alter database open;


Database altered.


SQL> select file_name,tablespace_name from dba_data_files;

13.删除表空间

SQL> drop tablespace tbs1 including contents and datafiles;


Tablespace dropped.

contents 代表表段，dtaafiles表示连带删除表空间下的数据文件

14.查看数据文件的基本信息

SQL> select name,file#,bytes,checkpoint_change# last_scn from v$datafile;

bytes/1024即为数据问价的大小(m)

15.查看段类型和个数

SQL> select segment_type,count(*) seg_count from dba_segments where owner='SCOTT' group by segment_type;


SEGMENT_TYPE        SEG_COUNT
------------------ ----------
INDEX                       2
TABLE                       3

16.创建一个2k块大小的表空间

SQL> ALTER SYSTEM SET DB_2k_CACHE_SIZE=16M SCOPE=SPFILE;


System altered.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.


Total System Global Area  839282688 bytes
Fixed Size                  2233000 bytes
Variable Size             494931288 bytes
Database Buffers          339738624 bytes
Redo Buffers                2379776 bytes
Database mounted.
Database opened.
SQL> create tablespace tbs2 datafile '/oracle/app/oracle/oradata/orcl/tbs02.dbf' size 5m blocksize 2k;


Tablespace created.

SQL> select tablespace_name,block_size from dba_tablespaces;    


TABLESPACE_NAME                BLOCK_SIZE
------------------------------ ----------
SYSTEM                               8192
SYSAUX                               8192
UNDOTBS1                             8192
TEMP                                 8192
USERS                                8192
MYTBS1                               8192
MYTBS2                               8192
TBS2                                 2048

17.创建表空间设置自动段管理

SQL> create tablespace tbs3 datafile '/oracle/app/oracle/oradata/orcl/tbs03.dbf' size 5m extent management local uniform size 64k segment space management auto;


Tablespace created.

SQL> select tablespace_name,segment_space_management from dba_tablespaces;

18.查看段的区和块的数目

SQL> SELECT segment_name,extents,blocks,bytes FROM dba_segments WHERE owner =
  2  'SCOTT' AND segment_name='EMP';


SEGMENT_NAME
--------------------------------------------------------------------------------
   EXTENTS     BLOCKS      BYTES
---------- ---------- ----------
EMP
         1          8      65536

19.查看给定段的区的信息

SELECT extent_id,file_id,block_id,blocks FROM dba_extents WHERE owner =
'SCOTT' AND segment_name='EMP';

20.查看DBA_FREE_SPACE视图

SQL> SELECT tablespace_name, count(*),max(blocks), sum(blocks) FROM dba_free_space
  2  GROUP BY tablespace_name;


TABLESPACE_NAME                  COUNT(*) MAX(BLOCKS) SUM(BLOCKS)
------------------------------ ---------- ----------- -----------
TBS2                                    1        2048        2048
SYSAUX                                  1        3768        3768
UNDOTBS1                                5         768         808
MYTBS2                                  1         512         512
USERS                                   1         472         472
SYSTEM                                  1         264         264
TBS3                                    1         512         512
MYTBS1                                  1         512         512


8 rows selected.