|
oracle表空間+添加數(shù)據(jù)文件+日志文件 1.mount狀態(tài)下查看數(shù)據(jù)文件，臨時文件，日志文件 SQL> select file#,name,status,bytes/1024/1024 from v$datafile; --默認(rèn)是4個數(shù)據(jù)文件 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; --默認(rèn)是1個臨時文件 1 /u01/oradata/tinadb/temp01.dbf ONLINE 29 sql>select * from v$logfile; --默認(rèn)是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; --默認(rèn)是5個表空間，每個表空間一個數(shù)據(jù)文件 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狀態(tài)下查看數(shù)據(jù)文件，臨時文件，日志文件 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 ---都是自動擴(kuò)展的 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 現(xiàn)在已經(jīng)用了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.給表空間添加數(shù)據(jù)文件 說明：如果是ASM，那么就填寫相應(yīng)的路徑，如'+DATA_DG' '+FRA_1'之類的。 如果是文件系統(tǒng)，那么就直接指向直接路徑即可。 ASM 環(huán)境下，add tablespace 加datafile ，可以使用自動擴(kuò)展，next size 100M 文件系統(tǒng)，盡量不要開自動擴(kuò)展，因?yàn)榭赡軙?dǎo)致磁盤使用100%，直接指定固定值即可。 4.1 大文件表空間添加數(shù)據(jù)文件： 查看是否是大文件表空間 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 大文件表空間只能有一個數(shù)據(jù)文件。 alter tablespace BASE_DATA autoextend on next 100m maxsize 1200g; bigfile 只有一個數(shù)據(jù)文件，每次自動擴(kuò)展100m，整個數(shù)據(jù)文件最終達(dá)到1.2T 4.2 普通表空間添加數(shù)據(jù)文件 alter database datafile '' size 20g autoextend on next 100m maxsize 1000m; smallfile 當(dāng)前大?。?0g 下次擴(kuò)展100m，可以擴(kuò)展10次，到1000m，就不可以擴(kuò)展。 自動擴(kuò)展100m -----12800 4.3 裸設(shè)備添加數(shù)據(jù)文件 (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中沒有當(dāng)前被使用的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的大小為 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)查看裸設(shè)備的權(quán)限，注意在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)登陸庫核查該裸設(shè)備有沒有被使用 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)如上一步?jīng)]有返回記錄，則可使用， 文件大小為LV的大小減去32M，16*1024 – 32 = 16352 alter tablespace UNDOTBS4 add datafile '/dev/rv_mb0631_16g ' size 16352M autoextend off; 5.添加日志組，日志組添加成員 注意，只有當(dāng)日志組狀態(tài)為INACTIVE時，才可以對該組進(jìn)行操作，當(dāng)然你也可以切換一下： 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.修改自動擴(kuò)展為100M 查看哪些數(shù)據(jù)文件開了自動擴(kuò)展：--自動擴(kuò)展的數(shù)據(jù)文件最大會擴(kuò)展到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.創(chuàng)建新用戶 創(chuàng)建單獨(dú)的表空間 SQL> create tablespace ts_tina datafile '/u01/oradata/tinadb/ts_tina01.dbf' size 500m; Tablespace created. 創(chuàng)建用戶 SQL> create user tina identified by tina123 default tablespace ts_tina temporary tablespace temp; User created. 授權(quán) SQL> grant dba to tina; Grant succeeded. 修改默認(rèn)表空間 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.查詢表所屬于的表空間，注意對象名在數(shù)據(jù)庫中是以大寫存放的。 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; |
|
|
