oracle表空间+添加数据文件+日志文件

[size=small]oracle表空间+添加数据文件+日志文件

1.mount状态下查看数据文件，临时文件，日志文件
SQL> select file#,name,status,bytes/1024/1024 from v$datafile; --默认是4个数据文件
FILE# NAME STATUS BYTES/1024/1024
---------- ------- ------- ---------------
1 /u01/oradata/tinadb/system01.dbf SYSTEM 710
2 /u01/oradata/tinadb/sysaux01.dbf ONLINE 610
3 /u01/oradata/tinadb/undotbs01.dbf ONLINE 90
4 /u01/oradata/tinadb/users01.dbf ONLINE 5

SQL> select file#,name,status,bytes/1024/1024 from v$tempfile; --默认是1个临时文件
1 /u01/oradata/tinadb/temp01.dbf ONLINE 29

sql>select * from v$logfile; --默认是3个日志文件，大小是50m
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- -------
3 ONLINE /u01/oradata/tinadb/redo03.log NO
2 ONLINE /u01/oradata/tinadb/redo02.log NO
1 ONLINE /u01/oradata/tinadb/redo01.log NO

SQL> select group#,thread#,members,archived,status,bytes/1024/1024 from v$log; ---还有一个v$log视图（日志还有日志组，日志组成员member的概念，具体可以查一下百度）
GROUP# THREAD# MEMBERS ARCHIVED STATUS BYTES/1024/1024
---------- ---------- ---------- -------- ---------------- ---------------
1 1 1 YES INACTIVE 50
2 1 1 YES INACTIVE 50
3 1 1 NO CURRENT 50

SQL> select * from v$tablespace; --默认是5个表空间，每个表空间一个数据文件
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES


2.open状态下查看数据文件，临时文件，日志文件
SQL> select file_id,tablespace_name,bytes/1024/1024,status,autoextensible,increment_by from dba_data_files;
FILE_ID TABLESPACE_NAME BYTES/1024/1024 STATUS AUT INCREMENT_BY
---------- ------------------------------ --------------- --------- --- ------------
4 USERS 5 AVAILABLE YES 160
3 UNDOTBS1 90 AVAILABLE YES 640
2 SYSAUX 610 AVAILABLE YES 1280
1 SYSTEM 710 AVAILABLE YES 1280 ---都是自动扩展的

SQL> select file_id,tablespace_name,bytes/1024/1024,status,autoextensible,increment_by from dba_temp_files;
FILE_ID TABLESPACE_NAME BYTES/1024/1024 STATUS AUT INCREMENT_BY
---------- ------------------------------ --------------- ------- --- ------------
1 TEMP 29 ONLINE YES 80


3.查看某个表空间的大小
select maxbytes/1024/1024/1024 from dba_data_files where tablespace_name='BASE_DATA';
MAXBYTES/1024/1024/1024
-----------------------
933.571289 900多个g 现在已经用了850多个g 差不多91%，要改成85% 后来变成了1200g

查看所有表空间的使用情况
select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc;

4.给表空间添加数据文件
说明：如果是ASM，那么就填写相应的路径，如'+DATA_DG' '+FRA_1'之类的。
如果是文件系统，那么就直接指向直接路径即可。
ASM 环境下，add tablespace 加datafile ，可以使用自动扩展，next size 100M
文件系统，尽量不要开自动扩展，因为可能会导致磁盘使用100%，直接指定固定值即可。

4.1 大文件表空间添加数据文件：
查看是否是大文件表空间
select tablespace_name, bigfile from dba_tablespaces where tablespace_name='';
添加
alter tablespace BASE_DATA add datafile '+DATA1' size 30g autoextend on next 100m maxsize 500m;
--ORA-32771:cannot add file to bigfile tablespace bigfile tablespace 大文件表空间只能有一个数据文件。

alter tablespace BASE_DATA autoextend on next 100m maxsize 1200g; bigfile
只有一个数据文件，每次自动扩展100m，整个数据文件最终达到1.2T

4.2 普通表空间添加数据文件
alter database datafile '' size 20g autoextend on next 100m maxsize 1000m; smallfile
当前大小：20g 下次扩展100m，可以扩展10次，到1000m，就不可以扩展。
自动扩展100m -----12800

4.3 裸设备添加数据文件
(1)列出所有VG
root@ossrac1:/>lsvg
rootvg
datavg01
datavg02
datavg03
datavg04
mndhb_vg_01
mndhb_vg_02
mndhb_vg_03
arch1vg
datavg05
datavg06
ggvg

(2)找出VG中没有当前被使用的lv
root@ossrac1:/>lsvg -l datavg06 | grep close
lsvg -l datavg04 | grep close
v_mb0631_16g raw 32 32 4 closed/syncd N/A
v_mb0637_16g raw 32 32 4 closed/syncd N/A
v_mb0638_16g raw 32 32 4 closed/syncd N/A
v_mb0639_16g raw 32 32 4 closed/syncd N/A
v_mb0640_16g raw 32 32 4 closed/syncd N/A
v_mb0641_16g raw 32 32 4 closed/syncd N/A

(3)查看LV的大小，LV的大小为<PP SIZE> * <PPS>，该LV的小为512M*32=16G ，核查状态是否为“closed/syncd“
root@ossrac1:/>lslv v_mb0631_16g
LOGICAL VOLUME: v_mb0631_16g VOLUME GROUP: datavg06
LV IDENTIFIER: 00cc885100004c000000013a1a12f671.335 PERMISSION: read/write
VG STATE: active/complete LV STATE: closed/syncd
TYPE: raw WRITE VERIFY: off
MAX LPs: 512 PP SIZE: 512 megabyte(s)
COPIES: 1 SCHED POLICY: striped
LPs: 32 PPs: 32
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: maximum RELOCATABLE: no
INTRA-POLICY: middle UPPER BOUND: 4
MOUNT POINT: N/A LABEL: None
MIRROR WRITE CONSISTENCY: on/ACTIVE
EACH LP COPY ON A SEPARATE PV ?: yes (superstrict)
Serialize IO ?: NO
STRIPE WIDTH: 4
STRIPE SIZE: 128k
DEVICESUBTYPE : DS_LVZ
COPY 1 MIRROR POOL: None
COPY 2 MIRROR POOL: None
COPY 3 MIRROR POOL: None

(4)查看裸设备的权限，注意在LV的名称前做个一个”r”
root@ossrac1:/>ls -l /dev/rv_mb0631_16g
crw-rw---- 1 oracle dba 48,335 Sep 07 00:30 /dev/rv_mb0631_16g

(5)登陆库核查该裸设备有没有被使用
select * from dba_data_files f where f.file_name like '%v_mb0631_16g%'
select * from dba_temp_files f where f.file_name like '%v_mb0631_16g%';

(6)如上一步没有返回记录，则可使用， 文件大小为LV的大小减去32M，16*1024 – 32 = 16352
alter tablespace UNDOTBS4 add datafile '/dev/rv_mb0631_16g ' size 16352M autoextend off;


5.添加日志组，日志组添加成员
注意，只有当日志组状态为INACTIVE时，才可以对该组进行操作，当然你也可以切换一下：
alter system switch logfile;

alter database add logfile group 4 '/u01/oradata/tinadb/redo04.log' size 500m;
alter database add standby logfile group 4 '/u01/oradata/tinadb/redo04.log' size 500m; ---如果有standb db，那么那边也需要一同添加
alter database drop logfile group 3; --删除组

alter database add logfile member '/u01/oradata/tinadb/redo04_2.log' to group 4;
alter database drop logfile member '/u01/oradata/tinadb/redo04_2.log'; --删除组成员

6.查看表空间的使用率
set linesize 256
with ta as
(select tablespace_name, sum(decode(MAXBYTES,0 ,BYTES,MAXBYTES)) / 1024 / 1024 as file_mb
from dba_data_files
group by tablespace_name),
tb as
(select tablespace_name, sum(bytes) / 1024 / 1024 as seg_mb
from dba_segments
group by tablespace_name)
select ta.tablespace_name,
ta.file_mb,
tb.seg_mb,
Ta.file_mb-tb.seg_mb as free_mb,
round(seg_mb * 100 / file_mb) / 100 as usepecent,
round(seg_mb/0.8-file_mb) as needadd
from ta, tb
where ta.tablespace_name = tb.tablespace_name
order by usepecent desc;

7.修改自动扩展为100M
查看哪些数据文件开了自动扩展：--自动扩展的数据文件最大会扩展到32g
select file_name,autoextensible,increment_by from dba_data_files where autoextensible='YES';

select 'alter database datafile '''|| file_name || ''' autoextend on next 100m ;' from dba_data_files where autoextensible = 'YES' and increment_by < 12800;
alter database datafile '/u01/oradata/tinadb/users01.dbf' autoextend on next 100m;

8.创建新用户
创建单独的表空间
SQL> create tablespace ts_tina datafile '/u01/oradata/tinadb/ts_tina01.dbf' size 500m;
Tablespace created.

创建用户
SQL> create user tina identified by tina123 default tablespace ts_tina temporary tablespace temp;
User created.

授权
SQL> grant dba to tina;
Grant succeeded.

修改默认表空间
SQL> alter database default tablespace ts_tina;
Database altered.

9.Undo切换成表空间tbs2：
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '+DATA_DG' SIZE 2000M AUTOEXTEND ON NEXT 100M;
Tablespace created.
SQL> alter system set undo_tablespace='UNDOTBS2' scope=both;
System altered.

10.查询表所属于的表空间，注意对象名在数据库中是以大写存放的。
SQL> select owner,table_name,tablespace_name from dba_tables where table_name=upper('ti_vms_driving_log_bak');

OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
VMS TI_VMS_DRIVING_LOG_BAK TB_VMS

11.查询整个库的大小：
select sum(bytes)/1024/1024/1024 from dba_segments;

12.如何关闭自动扩展
SQL> alter database datafile '/u01/oradata/tinadb/ts_tina01.dbf' autoextend off;

13.删除表空间
SQL> drop tablespace TS_TINA include contents and datafiles;
删除表空间包括目录和数据文件。

14.大文件表空间
1）bigfile tablespace的属性:
1.只能有一个数据文件
2.BigFile表空间必须是AUTO管理的方式
3.查看默认的表空间类型
SELECT * from database_properties WHERE property_name = 'DEFAULT_TBS_TYPE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
---------------- ----------------- -----------------
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type

2）大文件表空间的大小：
发现表空间文件容量与DB_BLOCK_SIZE有关，
在初始建库时，DB_BLOCK_SIZE要根据实际需要，
设置为4K,8K、16K、32K、64K等几种大小，
ORACLE的物理文件最大只允许4194304个块（由操作系统决定），

smallfile tablespace表空间文件的最大值为 4194304×DB_BLOCK_SIZE/1024M。
即：
4k最大表空间为：16384M=16G
8K最大表空间为：32768M=32G
16k最大表空间为：65536M=64G
32K最大表空间为：131072M=128G
64k最大表空间为：262144M=256G
---------------------------------------------------------------------
Oracle 10g 新增的表空间类型：大文件 (Bigfile) 表空间。 　　 　　
大文件表空间从某种角度来说提高了 Oracle 在 VLDB 上的管理能力。
只有自动段空间管理的 LMT (Locally Managed Tablespaces ) 支持 BIGFILE 表空间。
大文件表空间只能包含一个文件，但是文件可以达到 4G 个数据块大小。
(以下用 BFT 指代 BIGFILE Tablespace。BFT 可以和以下存储技术结合使用：
自动存储管理(ASM) 　　LVM 　　OMF 　　
理论上的 BFT 可以达到下面所列的值：　　 　　
数据块大小(单位：K) BFT 最大值(单位：T) 　　
2k 8T 　　
4k 16T 　　
8k 32T 　　
16k 64T 　　
32k 128T　　

[/size]