oracle-表空间相关笔记

发表于 更新于 分类于

oracle 表空间相关的笔记

官方例子

参考https://docs.oracle.com/html/E25494_01/omf003.htm#sthref1838

Example 1

以下示例将数据文件创建的缺省位置设置为 /u01/oradata，然后在该位置创建一个包含数据文件的表空间tbs_1。数据文件为100 MB，可自动扩展，最大大小不受限制。

1
2
SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/oradata';
SQL> CREATE TABLESPACE tbs_1;

Example 2

此示例在目录 / u01/oradata 中创建一个名为 tbs_2 的表空间，其中包含数据文件。数据文件初始大小为400 MB，并且由于指定了SIZE子句，因此数据文件不可自动扩展。

The following parameter setting is included in the initialization parameter file:

DB_CREATE_FILE_DEST = '/u01/oradata'

The following statement is issued at the SQL prompt:

1
SQL> CREATE TABLESPACE tbs_2 DATAFILE SIZE 400M;

表空间的一些笔记

查询用户默认表空间

1
select username,default_tablespace,TEMPORARY_TABLESPACE from user_users;

查询默认表空间路径

该路径是在创建表空间时未指定 datafile 时默认保存的路径

1
show parameter DB_CREATE_FILE_DEST

修改默认表空间路径

1
ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/oradata';

建表空间语句

这里文件路径可用 ./GZ_FUYI.dbf

这里创建一个初始大小为100m，自动扩展50m，无限制扩展的表空间 gz_fuyi

1
2
3
4
create tablespace gz_fuyi datafile 'D:\oradata\orcl\yibo\gz_fuyi.dbf'  size 100m
autoextend on next 50m maxsize unlimited
extent management local autoallocate
segment space management auto;

查询当前数据库默认的表空间类型

1
select * from database_properties where property_name = 'DEFAULT_TBS_TYPE';

v$datafile查当前所有表空间

1
select name from v$datafile

查询当前所有表空间名及路径

1
select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts# = t2.ts#;

dba_data_files查询某个表空间的信息

1
select file_name,tablespace_name,online_status from dba_data_files where tablespace_name = 'FUYI_ZHUANKEPINGTAI';

查询所有表空间容量情况

1
2
3
4
5
6
7
8
9
10
11
12
SELECT Total.name "Tablespace Name",
Free_space,
(total_space - Free_space) Used_space,
total_space
FROM (select tablespace_name, sum(bytes / 1024 / 1024) Free_Space
from sys.dba_free_space
group by tablespace_name) Free,
(select b.name, sum(bytes / 1024 / 1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name) Total
WHERE Free.Tablespace_name = Total.name ORDER BY Free.Tablespace_name;

修改数据库默认使用bigfile表空间

1
alter database set default bigfile tablespace;

建bigfile表空间

可以将表空间默认类型改为bigfile，这样用上面的语句创建出来的就是bigfile表空间了

官方文档

1
2
3
4
create bigfile tablespace bigtbs_test
datafile 'D:\oradata\orcl\bigfile_test\bigtbs_test.dbf'
size 10g
autoextend on;

建临时表空间

1
2
3
4
5
6
--temporary tablespace
create temporary tablespace GZ_FUYI_TEMP tempfile 'D:\oradata\orcl\yibo\GZ_FUYI_TEMP.dbf'  
size 100m
autoextend on
next 50m maxsize 20G
extent management local;

分配表空间给指定用户

给用户user_test 分配表空间TS_TEST,配额无限制

1
alter user user_test quota unlimited on TS_TEST;

表空间修改为自动扩展

1
alter tablespace BIGTBS_TEST autoextend on;

查询表空间信息的语句

1
2
3
4
5
6
7
select file_name,
       tablespace_name,
       blocks,
       user_bytes / 1024 / 1024 as userful_m,
       autoextensible
  from dba_data_files
 where tablespace_name = upper('bigtbs_test');

示例

一些说明

USER_BYTES is ‘Size of the useful portion of file in bytes’;
USER_BLOCKS is ‘Size of the useful portion of file in ORACLE blocks’;

修改用户默认表空间

1
alter user yibo default tablespace gz_yibo_bigtbs;

SQL> alter user yibo default tablespace gz_yibo_bigtbs;
User altered

1
2
3
alter user kcgl default tablespace kcgl temporary tablespace temp;
--针对某个用户的:
alter user user_name default tablespace tbs_name;

修改用户临时表空间

1
alter user yibo temporary tablespace YIBO_TEMP1;

修改数据库的默认临时表空间

1
alter database default temporary tablespace temp_tbs_name;

删除表空间及其数据文件

1
2
3
drop tablespace TBS_01
including contents
cascade constraints;

表空间数量超出上限解决

1
2
3
4
5
6
7
show parameter db_files;

alter system set db_files=更大的值 scope=spfile;

shutdown immediate;

startup;

修改表空间最大值

UNDO表空间

查看当前undo表空间

1
show parameter undo_tablespace

创建

语法

1
2
3
4
5
6
--使用create undo tablespace
CREATE [BIGFILE | SMALLFILE] UNDO TABLESPACE tbs_name
DATAFILE 'path/filename' SIZE integer [K | M] [REUSE]
[AUTOEXTEND] [OFF | ON] NEXT integer [K | M] MAXSIZE [UNLIMITED | integer [K | M] ]
[EXTENT MANAGEMENT LOCAL] [AUTOALLOCATE]
[RETENTION GUARANTEE | NOGUARANTEE]

实例

1
create bigfile undo tablespace yibo_undo_bigtbs datafile 'D:\YIBODB\yibo_undo_bigtbs.dbf' size 100m  autoextend on next 50m maxsize 50g;

切换undo表空间

1
2

ALTER SYSTEM SET UNDO_TABLESPACE = yibo_undo_bigtbs

不用重启实例

查询undo表空间

数据字典 解释
v$undostat 包含所有undo表空间的统计信息，用于对undo表空间进行监控和调整。通过该视图，可以估计当前undo表空间的大小，Oracle利用该视图完成对回退信息的自动管理，该视图数据是有最近4天内，每10分钟产生一条统计记录构成的。
v$rollstat 包含undo表空间中回退段的性能统计信息
v$transaction 包含事务所使用的回退段信息
dba_undo_extents 包含undo表空间中区的大小与状态信息
dba_hist_undostat 包含v$undostat的快照，主要是4天前的统计信息

查询undo表空间中回退信息的当前状态

1
select tablespace_name,segment_name,extent_id,status from dba_undo_extents;

undo表空间中区的状态一共有3种：EXPIRED、UNEXPIRED、ACTIVE。

–EXPIRED：表示该回退信息对应的事务已经提交，保存时间超过保留区；

–UNEXPIRED：表示该回退信息对应的事务已经提交，保存时间没有超过保留区；

–ACTIVE：表示回退信息对应的事务还没有提交，该区还在使用；

0%
隐藏