oracle表空间和数据文件的管理！

--查看表空间信息：

SQL> select TABLESPACE_NAME,BLOCK_SIZE,STATUS,CONTENTS,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;

TABLESPACE_NAME                BLOCK_SIZE STATUS    CONTENTS  EXTENT_MAN SEGMEN
------------------------------ ---------- --------- --------- ---------- ------
SYSTEM                               8192 ONLINE    PERMANENT LOCAL      MANUAL
UNDOTBS1                             8192 ONLINE    UNDO      LOCAL      MANUAL
SYSAUX                               8192 ONLINE    PERMANENT LOCAL      AUTO
TEMP                                 8192 ONLINE    TEMPORARY LOCAL      MANUAL
USERS                                8192 ONLINE    PERMANENT LOCAL      AUTO
TEST01                               8192 ONLINE    PERMANENT LOCAL      AUTO
PERFSTAT                             8192 ONLINE    PERMANENT LOCAL      AUTO

已选择7行。

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 UNDOTBS1                       YES NO  YES
         2 SYSAUX                         YES NO  YES
         3 TEMP                           NO  NO  YES
         4 USERS                          YES NO  YES
         5 TEST01                         YES NO  YES
         6 PERFSTAT                       YES NO  YES

已选择7行。

--查看数据文件信息：

SQL> select FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024,AUTOEXTENSIBLE,USER_BYTES/1024/1024,ONLINE_STATUS from dba_data_files;

   FILE_ID FILE_NAME                                     TABLESPACE_NAME                BYTES/1024/1024 AUT USER_BYTES/1024/1024 ONLINE_
---------- --------------------------------------------- ------------------------------ --------------- --- -------------------- -------
         1 /u01/app/oracle/oradata/orcl/system01.dbf     SYSTEM                                     490 YES             489.9375 SYSTEM
         2 /u01/app/oracle/oradata/orcl/undotbs01.dbf    UNDOTBS1                                   310 YES             309.9375 ONLINE
         3 /u01/app/oracle/oradata/orcl/sysaux01.dbf     SYSAUX                                     270 YES             269.9375 ONLINE
         4 /u01/app/oracle/oradata/orcl/users01.dbf      USERS                                        5 YES               4.9375 ONLINE
         5 /u01/app/oracle/oradata/orcl/test01.dbf       TEST01                                       1 NO                 .9375 ONLINE
         6 /u01/app/oracle/oradata/orcl/perfstat01.dba   PERFSTAT                                   512 NO              511.9375 ONLINE

已选择6行。

SQL> select FILE#,STATUS,ENABLED,NAME,BYTES/1024/1024 from v$datafile;

     FILE# STATUS  ENABLED    NAME                                          BYTES/1024/1024
---------- ------- ---------- --------------------------------------------- ---------------
         1 SYSTEM  READ WRITE /u01/app/oracle/oradata/orcl/system01.dbf                 490
         2 ONLINE  READ WRITE /u01/app/oracle/oradata/orcl/undotbs01.dbf                310
         3 ONLINE  READ WRITE /u01/app/oracle/oradata/orcl/sysaux01.dbf                 270
         4 ONLINE  READ WRITE /u01/app/oracle/oradata/orcl/users01.dbf                    5
         5 ONLINE  READ WRITE /u01/app/oracle/oradata/orcl/test01.dbf                     1
         6 ONLINE  READ WRITE /u01/app/oracle/oradata/orcl/perfstat01.dba               512

已选择6行。

--创建表空间：

SQL> create tablespace test datafile '/u01/app/oracle/test01.dbf'
  2  size 1m autoextend on next 1m maxsize 10m
  3  extent management local autoallocate
  4  segment space management auto;

表空间已创建。

SQL> select d.file_id,d.file_name,d.bytes/1024/1024,d.MAXBYTES/1024/1024,d.AUTOEXTENSIBLE,t.EXTENT_MANAGEMENT,t.SEGMENT_SPACE_MANAGEMENT
  2  from dba_tablespaces t,dba_data_files d where t.TABLESPACE_NAME = d.TABLESPACE_NAME and t.TABLESPACE_NAME = 'TEST';

   FILE_ID FILE_NAME                                     D.BYTES/1024/1024 D.MAXBYTES/1024/1024 AUT EXTENT_MAN SEGMEN
---------- --------------------------------------------- ----------------- -------------------- --- ---------- ------
         7 /u01/app/oracle/test01.dbf                                    1                   10 YES LOCAL      AUTO

--调整表空间数据文件大小：

SQL> alter database datafile '/u01/app/oracle/test01.dbf' resize 2m;

数据库已更改。

SQL> select d.file_id,d.file_name,d.bytes/1024/1024,d.MAXBYTES/1024/1024,d.AUTOEXTENSIBLE,t.EXTENT_MANAGEMENT,t.SEGMENT_SPACE_MANAGEMENT
  2  from dba_tablespaces t,dba_data_files d where t.TABLESPACE_NAME = d.TABLESPACE_NAME and t.TABLESPACE_NAME = 'TEST';

   FILE_ID FILE_NAME                                     D.BYTES/1024/1024 D.MAXBYTES/1024/1024 AUT EXTENT_MAN SEGMEN
---------- --------------------------------------------- ----------------- -------------------- --- ---------- ------
         7 /u01/app/oracle/test01.dbf                                    2                   10 YES LOCAL      AUTO

--更改表空间离线：

SQL> alter tablespace test offline;

表空间已更改。

SQL> select d.file_id,d.file_name,d.bytes/1024/1024,d.MAXBYTES/1024/1024,d.AUTOEXTENSIBLE,t.EXTENT_MANAGEMENT,t.SEGMENT_SPACE_MANAGEMENT,ONLINE_STATUS
  2  from dba_tablespaces t,dba_data_files d where t.TABLESPACE_NAME = d.TABLESPACE_NAME and t.TABLESPACE_NAME = 'TEST';

   FILE_ID FILE_NAME                                     D.BYTES/1024/1024 D.MAXBYTES/1024/1024 AUT EXTENT_MAN SEGMEN ONLINE_
---------- --------------------------------------------- ----------------- -------------------- --- ---------- ------ -------
         7 /u01/app/oracle/test01.dbf                                                               LOCAL      AUTO   OFFLINE

--更改表空间在线：

SQL> alter tablespace test online;

表空间已更改。

SQL> select d.file_id,d.file_name,d.bytes/1024/1024,d.MAXBYTES/1024/1024,d.AUTOEXTENSIBLE,t.EXTENT_MANAGEMENT,t.SEGMENT_SPACE_MANAGEMENT,ONLINE_STATUS
  2  from dba_tablespaces t,dba_data_files d where t.TABLESPACE_NAME = d.TABLESPACE_NAME and t.TABLESPACE_NAME = 'TEST';

   FILE_ID FILE_NAME                                     D.BYTES/1024/1024 D.MAXBYTES/1024/1024 AUT EXTENT_MAN SEGMEN ONLINE_
---------- --------------------------------------------- ----------------- -------------------- --- ---------- ------ -------
         7 /u01/app/oracle/test01.dbf                                    2                   10 YES LOCAL      AUTO   ONLINE

--移动表空间数据文件位置：

SQL> alter tablespace test offline;

表空间已更改。

SQL> ! mv /u01/app/oracle/test01.dbf /u01/app/oracle/oradata/orcl/test001.dbf

SQL> alter tablespace test rename datafile '/u01/app/oracle/test01.dbf' to '/u01/app/oracle/oradata/orcl/test001.dbf';

表空间已更改。

SQL> alter tablespace test online;

表空间已更改。

SQL> select d.file_id,d.file_name,d.bytes/1024/1024,d.MAXBYTES/1024/1024,d.AUTOEXTENSIBLE,t.EXTENT_MANAGEMENT,t.SEGMENT_SPACE_MANAGEMENT,ONLINE_STATUS
  2  from dba_tablespaces t,dba_data_files d where t.TABLESPACE_NAME = d.TABLESPACE_NAME and t.TABLESPACE_NAME = 'TEST';

   FILE_ID FILE_NAME                                     D.BYTES/1024/1024 D.MAXBYTES/1024/1024 AUT EXTENT_MAN SEGMEN ONLINE_
---------- --------------------------------------------- ----------------- -------------------- --- ---------- ------ -------
         7 /u01/app/oracle/oradata/orcl/test001.dbf                      2                   10 YES LOCAL      AUTO   ONLINE

--向表空间添加数据文件：

SQL> alter tablespace test add datafile '/u01/app/oracle/oradata/orcl/test002.dbf' size 1m;

表空间已更改。

SQL> select d.file_id,d.file_name,d.bytes/1024/1024,d.MAXBYTES/1024/1024,d.AUTOEXTENSIBLE,t.EXTENT_MANAGEMENT,t.SEGMENT_SPACE_MANAGEMENT,ONLINE_STATUS
  2  from dba_tablespaces t,dba_data_files d where t.TABLESPACE_NAME = d.TABLESPACE_NAME and t.TABLESPACE_NAME = 'TEST';

   FILE_ID FILE_NAME                                     D.BYTES/1024/1024 D.MAXBYTES/1024/1024 AUT EXTENT_MAN SEGMEN ONLINE_
---------- --------------------------------------------- ----------------- -------------------- --- ---------- ------ -------
         7 /u01/app/oracle/oradata/orcl/test001.dbf                      2                   10 YES LOCAL      AUTO   ONLINE
         8 /u01/app/oracle/oradata/orcl/test002.dbf                      1                    0 NO  LOCAL      AUTO   ONLINE

--删除表中间中数据文件：

alter tablespace TEMP drop tempfile 'F:\ORADATA\ACSYSTEST_TMP04.DBF';

--更改表空间为只读：

SQL> alter tablespace test read only;

表空间已更改。

SQL> select d.file_id,d.file_name,d.bytes/1024/1024,d.MAXBYTES/1024/1024,d.AUTOEXTENSIBLE,t.EXTENT_MANAGEMENT,t.SEGMENT_SPACE_MANAGEMENT,d.ONLINE_STATUS,t.status
  2  from dba_tablespaces t,dba_data_files d where t.TABLESPACE_NAME = d.TABLESPACE_NAME and t.TABLESPACE_NAME = 'TEST';

   FILE_ID FILE_NAME                                     D.BYTES/1024/1024 D.MAXBYTES/1024/1024 AUT EXTENT_MAN SEGMEN ONLINE_ STATUS
---------- --------------------------------------------- ----------------- -------------------- --- ---------- ------ ------- ---------
         7 /u01/app/oracle/oradata/orcl/test001.dbf                      2                   10 YES LOCAL      AUTO   ONLINE  READ ONLY
         8 /u01/app/oracle/oradata/orcl/test002.dbf                      1                    0 NO  LOCAL      AUTO   ONLINE  READ ONLY

--更改表空间为读写：

SQL> alter tablespace test read write;

表空间已更改。

SQL>  select d.file_id,d.file_name,d.bytes/1024/1024,d.MAXBYTES/1024/1024,d.AUTOEXTENSIBLE,t.EXTENT_MANAGEMENT,t.SEGMENT_SPACE_MANAGEMENT,d.ONLINE_STATUS,t.status
  2   from dba_tablespaces t,dba_data_files d where t.TABLESPACE_NAME = d.TABLESPACE_NAME and t.TABLESPACE_NAME = 'TEST';

   FILE_ID FILE_NAME                                     D.BYTES/1024/1024 D.MAXBYTES/1024/1024 AUT EXTENT_MAN SEGMEN ONLINE_ STATUS
---------- --------------------------------------------- ----------------- -------------------- --- ---------- ------ ------- ---------
         7 /u01/app/oracle/oradata/orcl/test001.dbf                      2                   10 YES LOCAL      AUTO   ONLINE  ONLINE
         8 /u01/app/oracle/oradata/orcl/test002.dbf                      1                    0 NO  LOCAL      AUTO   ONLINE  ONLINE

--更改数据库全局缺省表空间：

SQL> select * from DATABASE_PROPERTIES where PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ ----------------------------------------
DEFAULT_PERMANENT_TABLESPACE   USERS                          Name of default permanent tablespace

SQL> alter database default tablespace test01;

数据库已更改。

SQL> select * from DATABASE_PROPERTIES where PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ ----------------------------------------
DEFAULT_PERMANENT_TABLESPACE   TEST01                         Name of default permanent tablespace

SQL> alter database default tablespace users;

数据库已更改。

SQL> select * from DATABASE_PROPERTIES where PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ ----------------------------------------
DEFAULT_PERMANENT_TABLESPACE   USERS                          Name of default permanent tablespace

--删除表空间：

SQL> drop tablespace test including contents and datafiles;

表空间已删除。

oracle表空间和数据文件的管理！

继续阅读

转换字符串成ＩＮＴ６４

c写文件

处理PCX文件

基于XOR的加密程序

不用iconv函数实现UTF-8编码转换GB2312的PHP函数

SQL优化SQL语句优化的目的

JAVA高效编程指南

坐标系统和投影变换在桌面产品中的应用

windows不能在本地计算机上运行oracleDbConsoleorcl

关于SQL语言

SQL语言基础：常用的数据查询语句

testlink 切换数据库

无组件上传图片到数据库中，最完整解决方案

neo4j之cypher使用文档

sqlServer根据经纬查距离

Oracle 批量查询传入List 返回List