表空间管理
创建表空间以及用户
创建临时表空间(可选)(添加临时表空间文件之后不会立即占用系统空间,用到的时候才会使用)
创建用户之前要创建“临时表空间”,若不创建则默认的表空间为temp
CREATE TEMPORARY TABLESPACE TRAFFIC_TEMP
TEMPFILE '/home/oracle/tablespace/TRAFFIC_TEMP.DBF'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- 增加临时表空间扩容
ALTER TABLESPACE TEMP add tempfile '/oracle/PUB/pubdata/temp02.dbf' size 10G;
-- 在 linux 上创建临时数据文件不会立马占用系统空间
-- 在 windows 上创建会立即占用系统空间
创建数据表空间
创建用户之前要先创建数据表空间,若没有创建则默认永久性表空间为system。
CREATE TABLESPACE TRAFFIC
LOGGING
DATAFILE '/home/oracle/tablespace/TRAFFIC.DBF'
SIZE 32M
AUTOEXTEND ON /* 自动扩展表空间,开启 */
NEXT 32M MAXSIZE UNLIMITED /* 每次以32M增长表空间 */
EXTENT MANAGEMENT LOCAL;
查看数据文件创建时间
select creation_date from v$datafile
创建用户并指定表空间
建好了名为‘TRAFFIC’的表空间,下面就可以创建用户了。
CREATE USER tbb IDENTIFIED BY "123456"
ACCOUNT UNLOCK
DEFAULT TABLESPACE TRAFFIC
TEMPORARY TABLESPACE TRAFFIC_TEMP;(如果没有创建临时表空间,则不需要这句话)
给用户授予权限
接着授权给新建的用户
GRANT CONNECT,RESOURCE TO tbb; --表示把 connect,resource权限授予tbb用户
grant unlimit tablespace to user; -- 如果出现不能添加数据的情况则在 sys 下执行。
GRANT DBA TO NEWUSER; --表示把 dba权限授予给tbb用户(可选)
查看表空间名称以及表空间大小
select t.tablespace_name, round(SUM(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t,dba_data_files d
where t.tablespace_name=d.tablespace_name
group by t.tablespace_name;
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name;
查看数据文件存放位置
select file_name from dba_data_files;
扩展表空间
查看数据文件情况
select
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;
查看表空间名字和所属文件
select tablespace_name,file_id,file_name,round(bytes/(1024*1024),0)total_space from dba_data_files order by tablespace_name;
增加表空间数据文件
alter tablespace game add datafile '/oracle/oradata/db/GAME02.dbf' size 1000M;
--RAC数据库扩容
--直接在路径中写'+DATA'就可以,不需要加路径和文件名
alter tablespace game add datafile '+DATA' size 1000M;
手动增加数据文件尺寸
alter database datafile '/oradata/db/GAME.dbf' resize 4000M;
设定数据文件自动扩展
alter database datafile '/oracle/oradata/db/BAME.dbf'
autoextend on next 100M
maxsize 10000M;
检查数据文件是否是自动扩展
select file_name,autoextensible,increment_by from dba_data_files where tablespace_name = 'TABLESPACE_NAME';
设置数据文件为自动扩展
alter database datafile '/u01/app/oracle/oradata/XXX/XXXX01.dbf' autoextend on;
或
alter database datafile '/u01/app/oracle/oradata/XXX/XXXX01.dbf' autoextend on next xxx maxsize xxxx;
关闭自动扩展
alter database datafile '/u01/app/oracle/oradata/XXX/XXXX01.dbf' autoextend off;
设定后查看表空间
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE;
更改表空间状态
使表空间脱机
alter tablespace 表空间名 offline;
如果意外删除了数据文件,则必须带有 RECOVER 选项
alter tablespace 表空间名 offline for recover;
使表空间联机
alter tablespace 表空间名 online;
使数据文件脱机
alter database datafile 'file_name' offline;
使数据文件联机
alter database datafile 'file_name' online;
使表空间只读
alter tablespace 表空间名 read only;
使表空间可读写
alter tablespace 表空间名 read write;
更改临时表空间数据文件状态
不能直接offline临时表空间
可以先建立另一个临时表空间:
create temporary tablespace…
然后指定新建的临时表空间为默认:
alter database default temporary tablespace……
最后:alter tablespace 原临时表空间 offline
create temporary tablespace… alter database default temporary tablespace…… alter tablespace 原临时表空间 offline
更改数据文件使离线
ALTER DATABASE TEMPFILE 'd:/TEMP01.DBF' OFFLINE;
用户在表空间上的配额
改变用户在表空间上的配额
alter user username quota 0 on tablespace_name;
alter user scott username unlimited on tablespace_name;
alter user username quota 30m on tablespace_name;
查看用户表空间配额(dba_ts_quotas)
select username ,tablespace_name ,max_bytes/1024/1024 "Max MB" from dba_ts_quotas where username='scott';
如果创建的用户不能在表空间中创建表,则可能没有权限
grant unlimited tablespace to user;
建立 UNDO 表空间
CREATE UNDO TABLESPACE UNDOTBS02;
DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M
-- 注意:在OPEN状态下某些时刻只能用一个UNDO表空间,如果要用新建的表空间,必须切换到该表空间:
ALTER SYSTEM SET undo_tablespace=UNDOTBS02;
FSFI 表空间碎片
整理表空间碎片
alter tablespace 表空间名 coalesce
删除表空间以及用户
删除表空间
drop tablespace GLOBE_NEW including contents and datafiles;
查看用户连接情况
select username ,sid ,serial# from v$session where username='连接的用户名';
杀掉进程资源
alter system kill session 'SID,SERIAL#';
重新执行删除用户命令
drop user 用户名 cascade;
删除表空间遇到的问题
drop tablespace TABLESPACE_NAME including contents and datafiles;
SQL> drop tablespace TABLESPACE_NAME including contents and datafiles;
drop tablespace TABLESPACE_NAME including contents and datafiles
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
select 'alter table '||owner||'.'||table_name||' drop constraint '||constraint_name||' ;'
from dba_constraints
where constraint_type in ('U', 'P')
and (index_owner, index_name) in
(select owner, segment_name
from dba_segments
where tablespace_name = 'TABLESPACE_NAME');
ORA-23515: 实体化视图和/或它们的索引存在于表空间中
错误的提示已经很明晰了,你先把这个表空间上的mview drop掉再删除就可以了
查这个表空间有哪些mview的语句:
select table_name, tablespace_name from dba_tables
where tablespace_name='DW'
and table_name in (select mview_name from dba_mviews);
drop掉相应的mview
DROP MATERIALIZED VIEW mv_pk;
drop materialized view scott.mv_test_INFO PRESERVE TABLE;
删除数据文件
alter tablespace 表空间名 drop datafile '数据文件路径';
如果要删除临时文件,可以使用如下命令
alter tablespace 临时表空间名 drop tempfile '临时文件位置';
也可以使用如下命令删除临时文件
alter database tempfile '临时文件路径' drop including datafiles;