管理数据库表空间
SQL>create user han identified by han default tablespace tank
SQL>grant connect,resource to han;
切换用户:han/han
SQL> create table t (id integer,name char(20));
SQL> insert into t values(0,'han');
SQL> select * from t;
SQL> commit;
SQL> insert into t values(1,'dfdf');
切换用户:system/system
SQL> alter tablespace tank read only; --> 修改表空间为:只读 (不能执行.)
切换用户:han/han
SQL> commit; ---提交完,修改表空间更为只读.
SQL> insert into t values(5, 'dfdf'); 不能插入数据:
SQL> delete from t where id=0; 不能删除数据
SQL> truncate table t; 不能清空表数据.
SQL> drop table t; 可以删除表:
更换用户:system/system
SQL> alter tablespace tank read write; 将表改为读写模式:
可以将大量的静态数据设置为只读表空间:
SQL> alter tablespace tank offline; 将表空间离线:
更换用户:tank/tank
SQL>select * from tt; 不查询数据库:
更换用户:system/system
alter tablespace tank online :挂上表空间.
查看表空间使用情况语句:
col tablespace_name format a10;
select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used",
round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total
from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) used
from dba_extents group by tablespace_name) u,
(select tablespace_name, round(sum(bytes/(1024*1024))) free
from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name;
向表空间里增加数据文件达到达到扩大表空间大少.
1 alter tablespace tank add datafile
2 '/u01/oradata/tank/tank2.dbf'size 20M
3* autoextend on next 10M maxsize 100M
SQL> /
创建表空间时指定大小为自动增长.
Create tablespace user_data
'/u01/oradata/tank/tank4.dbf'size 100M
autoextend on next 10M maxsize 500M;
查看数据文件的状态:
select file_name,tablespace_name,autoextensible from dba_data_files;
阅读(1467) | 评论(0) | 转发(0) |