管理表空间和数据文件
(1).以sys用户登录，创建一个表空间
SQL> create tablespace Aric
2 datafile '/home/oracle/oracle/product/10.2.0/oradata/orcl/aric.dbf' size 20M
3 extent management local uniform size 128k;
Tablespace created.
(2).创建一个用户，指定它的表空间为Aric，或者修改已存在的用户的默认表空间
SQL> create user aric identified by aric default tablespace aric;
User created.
SQL> grant connect,resource to aric;
Grant succeeded.
(3).以aric用户登录，创建测试表
SQL> create table t(id integer,name char(10));
Table created.
SQL> insert into t values(0,'aric');
1 row created.
SQL> commit;
Commit complete.
SQL> select *from t;
ID NAME
---------- ----------
0 aric
此时在sys用户下将表空间Aric改为read only状态，这个时候用户aric就只能读不能写，测试如下：
SQL> alter tablespace aric read write; --SYS用户操作
Tablespace altered.
SQL> insert into t values(1,'tt'); --aric用户操作
insert into t values(1,'tt')
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6:
'/home/oracle/oracle/product/10.2.0/oradata/orcl/aric.dbf'
就像上面所示，此时是不可做写操作的，truncate和delete均不可以：
SQL> delete from t where id=0;
delete from t where id=0
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6:
'/home/oracle/oracle/product/10.2.0/oradata/orcl/aric.dbf'
SQL> truncate table t;
truncate table t
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6:
'/home/oracle/oracle/product/10.2.0/oradata/orcl/aric.dbf'
虽然在read only状态下不可写，但是可以Drop该对象，因为保存对象的信息是在数据字典表空间里的：
SQL> drop table t;
Table dropped.
2.表空间的Online和Offline
(1).将表空间Aric置为离线状态
SQL> alter tablespace aric offline;
Tablespace altered.
此时表空间Aric是不可操作的
SQL> create table t1 (id integer,name varchar(20));
create table t1 (id integer,name varchar(20))
*
ERROR at line 1:
ORA-01542: tablespace 'ARIC' is offline, cannot allocate space in it
SQL> select *from tt;
select *from tt
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6:
'/home/oracle/oracle/product/10.2.0/oradata/orcl/aric.dbf'
SQL> alter tablespace aric online; --重新置为在线Online，就又可以操作了
Tablespace altered.
3.改变表空间的容量
--查看表空间使用情况的SQL语句
SELECT UPPER(F.TABLESPACE_NAME) "名",
D.TOT_GROOTTE_MB "大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已用(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
'990.99') "使用比",
F.TOTAL_BYTES "空闲(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
(1).向表空间添加新的数据文件达到扩容的目的
SQL> alter tablespace aric add datafile
2 '/home/oracle/oracle/product/10.2.0/oradata/orcl/aric2.dbf' size 20M
3 autoextend on next 10M maxsize 100M;
Tablespace altered.
此时查看数据字典dba_data_files，获取数据文件的相关信息：
SQL> desc dba_data_files;
Name Null? 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> col file_name format a20
SQL> select file_name,tablespace_name,autoextensible from dba_data_files;
FILE_NAME TABLESPACE_NAME AUT
-------------------- ------------------------------ ---
/home/oracle/oracle/ ARIC NO
product/10.2.0/orada
ta/orcl/aric.dbf
/home/oracle/oracle/ ARIC YES
product/10.2.0/orada
ta/orcl/aric2.dbf
现在，Aric表空间有两个数据文件，其中一个可以自动增长
(2).使得已经存在的数据文件自动增长
SQL> alter database datafile '/home/oracle/oracle/product/10.2.0/oradata/orcl/aric.dbf'
autoextend on next 10M maxsize 100M;
Database altered.
SQL> col file_name format a20
SQL> select file_name,tablespace_name,autoextensible from dba_data_files;
FILE_NAME TABLESPACE_NAME AUT
-------------------- ------------------------------ ---
/home/oracle/oracle/ ARIC YES
product/10.2.0/orada
ta/orcl/aric.dbf
/home/oracle/oracle/ ARIC YES
product/10.2.0/orada
ta/orcl/aric2.dbf
再次查看数据字典dba_data_files可以看到相应的数据文件的信息已被修改
(3).更改已有数据文件的大小
SQL> alter database
datafile '/home/oracle/oracle/product/10.2.0/oradata/orcl/aric.dbf'
resize 40M;
Database altered.
4.移动数据文件的方法(两种方法)
(1).修改表空间
--先将表空间aric离线
SQL> alter tablespace aric offline;
Tablespace altered.
--移动数据文件到指定磁盘
[oracle@localhost orcl]$ ll
总计 2264648
-rw-r----- 1 oracle oinstall 20979712 10-02 17:24 aric2.dbf
-rw-r----- 1 oracle oinstall 41951232 10-02 17:24 aric.dbf
[oracle@localhost orcl]$ mv aric.dbf aric1.dbf
[oracle@localhost orcl]$ ll
总计 2264648
-rw-r----- 1 oracle oinstall 41951232 10-02 17:24 aric1.dbf
-rw-r----- 1 oracle oinstall 20979712 10-02 17:24 aric2.dbf
--执行修改命令
SQL> alter tablespace aric rename
datafile '/home/oracle/oracle/product/10.2.0/oradata/orcl/aric.dbf'
to '/home/oracle/oracle/product/10.2.0/oradata/orcl/aric1.dbf';Tablespace altered.
--此时数据文件就被成功移动到指定路径下了，再将其online就可以访问了
SQL> alter tablespace aric online;
Tablespace altered.
(2).修改数据库
--将数据文件aric1.dbf移到当前目录的上层目录，这一步在数据库shutdown后操作
[oracle@localhost orcl]$ mv aric1.dbf ..
--将数据库置为mount状态
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 62916176 bytes
Database Buffers 218103808 bytes
Redo Buffers 2973696 bytes
Database mounted.
--执行修改数据库的命令
SQL> alter database rename file '/home/oracle/oracle/product/10.2.0/oradata/orcl/aric1.dbf'
to '/home/oracle/oracle/product/10.2.0/oradata/aric1.dbf';
Database altered.
--将数据库open就可正常使用了
SQL> alter database open;
Database altered.
5.删除表空间
--删除表空间及其对应的数据文件
SQL> drop tablespace aric including contents and datafiles;
Tablespace dropped.
注意：之前的用户aric的默认表空间是aric，现在把表空间aric删除了，那么用户aric的默认表空间是什么？
用Toad查看了一下，似乎没有变化(此时登进去，创建表报错，表空间aric不存在)
扫一扫
5997
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
