oracle表空间不写日志,oracle表空间+增添数据文件+日志文件

oracle表空间&＃43;添加数据文件&＃43;日志文件

oracle表空间&＃43;添加数据文件&＃43;日志文件

1.mount状态下查看数据文件&＃xff0c;临时文件&＃xff0c;日志文件

SQL> select file#,name,status,bytes/1024/1024 from v$datafile;   --默认是4个数据文件

FILE# NAME STATUSBYTES/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个日志文件&＃xff0c;大小是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视图(日志还有日志组&＃xff0c;日志组成员member的概念&＃xff0c;具体可以查一下百度)

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个表空间&＃xff0c;每个表空间一个数据文件

TS# NAME   INC BIG FLA ENC

---------- ------------------------------ --- --- --- ---

0 SYSTEM  YES NO  YES

1 SYSAUX  YES NO  YES

2 UNDOTBS1YES NO  YES

4 USERS  YES NO  YES

3 TEMP   NO  NO  YES

2.open状态下查看数据文件&＃xff0c;临时文件&＃xff0c;日志文件

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  YES80

3.查看某个表空间的大小

select maxbytes/1024/1024/1024 from dba_data_files where tablespace_name&＃61;&＃39;BASE_DATA&＃39;;

MAXBYTES/1024/1024/1024

-----------------------

933.571289  900多个g   现在已经用了850多个g  差不多91%&＃xff0c;要改成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&＃61;b.tablespace_name

order   by   ((a.bytes-b.bytes)/a.bytes)   desc;

4.给表空间添加数据文件

说明&＃xff1a;如果是ASM&＃xff0c;那么就填写相应的路径&＃xff0c;如&＃39;&＃43;DATA_DG&＃39; &＃39;&＃43;FRA_1&＃39;之类的。

如果是文件系统&＃xff0c;那么就直接指向直接路径即可。

ASM 环境下&＃xff0c;add tablespace 加datafile &＃xff0c;可以使用自动扩展&＃xff0c;next size 100M

文件系统&＃xff0c;尽量不要开自动扩展&＃xff0c;因为可能会导致磁盘使用100%&＃xff0c;直接指定固定值即可。

4.1 大文件表空间添加数据文件&＃xff1a;

查看是否是大文件表空间

select tablespace_name, bigfile from dba_tablespaces where tablespace_name&＃61;&＃39;&＃39;;

添加

alter tablespace BASE_DATA add datafile &＃39;&＃43;DATA1&＃39; 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

只有一个数据文件&＃xff0c;每次自动扩展100m&＃xff0c;整个数据文件最终达到1.2T

4.2 普通表空间添加数据文件

alter database datafile &＃39;&＃39; size 20g autoextend on next 100m maxsize 1000m;     smallfile

当前大小&＃xff1a;20g 下次扩展100m&＃xff0c;可以扩展10次&＃xff0c;到1000m&＃xff0c;就不可以扩展。

自动扩展100m -----12800

4.3 裸设备添加数据文件

(1)列出所有VG

root&＃64;ossrac1:/>lsvg

rootvg

datavg01

datavg02

datavg03

datavg04

mndhb_vg_01

mndhb_vg_02

mndhb_vg_03

arch1vg

datavg05

datavg06

ggvg

(2)找出VG中没有当前被使用的lv

root&＃64;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的大小&＃xff0c;LV的大小为 * &＃xff0c;该LV的小为512M*32&＃61;16G &＃xff0c;核查状态是否为“closed/syncd“

root&＃64;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)查看裸设备的权限&＃xff0c;注意在LV的名称前做个一个”r”

root&＃64;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 &＃39;%v_mb0631_16g%&＃39;

select * from dba_temp_files f where f.file_name like &＃39;%v_mb0631_16g%&＃39;;

(6)如上一步没有返回记录&＃xff0c;则可使用&＃xff0c; 文件大小为LV的大小减去32M&＃xff0c;16*1024 – 32 &＃61; 16352

alter tablespace  UNDOTBS4  add datafile &＃39;/dev/rv_mb0631_16g &＃39; size 16352M autoextend off;

5.添加日志组&＃xff0c;日志组添加成员

注意&＃xff0c;只有当日志组状态为INACTIVE时&＃xff0c;才可以对该组进行操作&＃xff0c;当然你也可以切换一下&＃xff1a;

alter system switch logfile;

alter database add  logfile  group 4 &＃39;/u01/oradata/tinadb/redo04.log&＃39; size 500m;

alter database add standby logfile  group 4 &＃39;/u01/oradata/tinadb/redo04.log&＃39; size 500m;   ---如果有standb db&＃xff0c;那么那边也需要一同添加

alter database drop logfile group 3;  --删除组

alter database add logfile member &＃39;/u01/oradata/tinadb/redo04_2.log&＃39; to group 4;

alter database drop logfile member &＃39;/u01/oradata/tinadb/redo04_2.log&＃39;; --删除组成员

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 &＃61; tb.tablespace_name

order by usepecent desc;

7.修改自动扩展为100M

查看哪些数据文件开了自动扩展&＃xff1a;--自动扩展的数据文件最大会扩展到32g

select file_name,autoextensible,increment_by from dba_data_files where autoextensible&＃61;&＃39;YES&＃39;;

select &＃39;alter database datafile &＃39;&＃39;&＃39;|| file_name || &＃39;&＃39;&＃39; autoextend on next 100m ;&＃39; from dba_data_files  where autoextensible &＃61; &＃39;YES&＃39;  and increment_by <12800;

alter database datafile &＃39;/u01/oradata/tinadb/users01.dbf&＃39; autoextend on next 100m;

8.创建新用户

创建单独的表空间

SQL> create tablespace ts_tina datafile &＃39;/u01/oradata/tinadb/ts_tina01.dbf&＃39; 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&＃xff1a;

SQL>  CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE &＃39;&＃43;DATA_DG&＃39; SIZE 2000M AUTOEXTEND ON NEXT 100M;

Tablespace created.

SQL>  alter system set undo_tablespace&＃61;&＃39;UNDOTBS2&＃39; scope&＃61;both;

System altered.

10.查询表所属于的表空间&＃xff0c;注意对象名在数据库中是以大写存放的。

SQL>  select owner,table_name,tablespace_name from dba_tables where table_name&＃61;upper(&＃39;ti_vms_driving_log_bak&＃39;);

OWNER                          TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------

VMS                            TI_VMS_DRIVING_LOG_BAK         TB_VMS

11.查询整个库的大小&＃xff1a;

select sum(bytes)/1024/1024/1024 from dba_segments;