3. oracle 表空间管理

表空间管理

创建表空间以及用户

创建临时表空间(可选)(添加临时表空间文件之后不会立即占用系统空间,用到的时候才会使用)

创建用户之前要创建“临时表空间”,若不创建则默认的表空间为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  tablespacealter 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;
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值