oracle表不能新增数据文件,oracle表空间 添加数据文件 日志文件

* ，该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;