1.如果某一个表空间的利用率已经达到90%,你发现另外一块磁盘DISK2上还有70%的剩余空间,请问如何将该表空间配置到DISK2磁盘上?
2.你发现索引表空间和数据文件所对应的表空间在同一块磁盘上,你想把索引表空间移植到另一块I/O操作比较少的硬盘上,请写出操作步骤。
3.如果在线联机重做日志文件配置的不合理,请写出重新配置的步骤。
请求各个高手帮忙啊~
2.你发现索引表空间和数据文件所对应的表空间在同一块磁盘上,你想把索引表空间移植到另一块I/O操作比较少的硬盘上,请写出操作步骤。
3.如果在线联机重做日志文件配置的不合理,请写出重新配置的步骤。
请求各个高手帮忙啊~
2 alter index rebuild tablespace new_tablespace_name;
这里的new_tablespace_name,位于你所说的另一块I/O操作比较少的硬盘上。3 oracle建议,普通负载(非高峰,非谷值)下20分钟左右切换一次日志比较合理。另外检查日志里,是否有日志切换等待,若有,则说明日志切换太频繁。
如:alter tablespace tbl_test add datafile 'd:\oradata\test02.dbf' size 500m;
2、重建索引(可以在线重建索引)
如:alter index idx_test rebuild tablespace tbl_test online;
3、重新配置不外乎增删日志组,移动日志组等。
如:alter database add logfile group 4 ('g:\oracle\oradata\redo4a.log') size 10m;
alter database rename file 'd:\oracle\oradata\redo01.log'
to 'd:\oracle\redo\disk1\redo01a.log'
SQL> select * from v$version;BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production5 rows selected.
1.==============SQL> create tablespace tbs01 datafile '/tools/tbs01a.dbf' size 10m;Tablespace created.SQL> col tablespace_name format a20
SQL> col file_name format a50
SQL> select tablespace_name,file_name,bytes/1024/1024 "Size_MB" from dba_data_files where tablespace_name='TBS01';TABLESPACE_NAME FILE_NAME Size_MB
-------------------- -------------------------------------------------- ----------
TBS01 /tools/tbs01a.dbf 10SQL> alter tablespace add datafile '/tools/test/tbs01b.dbf' size 10m;
alter tablespace add datafile '/tools/test/tbs01b.dbf' size 10m
*
ERROR at line 1:
ORA-02140: invalid tablespace name
SQL> alter tablespace tbs01 add datafile '/tools/test/tbs01b.dbf' size 10m;Tablespace altered.SQL> select tablespace_name,file_name,bytes/1024/1024 "Size_MB" from dba_data_files where tablespace_name='TBS01';TABLESPACE_NAME FILE_NAME Size_MB
-------------------- -------------------------------------------------- ----------
TBS01 /tools/tbs01a.dbf 10
TBS01 /tools/test/tbs01b.dbf 10 --路径不一样SQL> select tablespace_name,sum(bytes)/1024/1024 "Size_MB" from dba_data_files where tablespace_name='TBS01' group by tablespace_nam
2 SQL>
SQL> select tablespace_name,sum(bytes)/1024/1024 "Size_MB" from dba_data_files
2 where tablespace_name='TBS01'
3 group by tablespace_name
4 /TABLESPACE_NAME Size_MB
-------------------- ----------
TBS01 202.============SQL> create table t01 tablespace tbs01 as select * from dba_users;Table created.SQL> desc t01;
Name Null? Type
----------------------------------------------------------------- -------- ---------------------------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)SQL> create index idx_t01 on t01(user_id);Index created.SQL> select table_name,index_name,tablespace_name from user_indexes where table_name='T01';TABLE_NAME INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------ --------------------
T01 IDX_T01 SYSTEMSQL> alter index idx_t01 rebuild tablespace tbs01 online;
alter index idx_t01 rebuild tablespace tbs01 online
*
ERROR at line 1:
ORA-08120: Need to create SYS.IND_ONLINE$ table in order to (re)build index
SQL> !oerr ora 08120
08120, 00000, "Need to create SYS.IND_ONLINE$ table in order to (re)build index"
// *Cause: alter index Build/Rebuild online require existing of
// SYS.IND_ONLINE$ table.
// *Action: User/DBA needs to create sys.ind_online$ before alter the index
// /rdbms/admin/catcio.sql contains script to create ind_online$.
SQL> @?/rdbms/admin/catcio.sql --过程不贴了Table created.SQL>
SQL> alter index idx_t01 rebuild tablespace tbs01 online;Index altered.SQL> select table_name,index_name,tablespace_name from user_indexes where table_name='T01';TABLE_NAME INDEX_NAME TABLESPACE_NAME
-------------------- -------------------- ------------------------------
T01 IDX_T01 TBS011 row selected.SQL> 3.=================SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 50 104857600 1 NO INACTIVE 445162343 28-APR-10
2 1 51 104857600 1 NO CURRENT 445436170 29-APR-10
3 1 49 104857600 1 NO INACTIVE 444889088 27-APR-103 rows selected.SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER
---------- ------- ------- ------------------------------------------------------------
3 ONLINE /DB_Data/oradata/test/redo03.log
2 ONLINE /DB_Data/oradata/test/redo02.log
1 ONLINE /DB_Data/oradata/test/redo01.log3 rows selected.SQL> SQL> alter database add logfile group 4 ('/DB_Data/oradata/test/redo04.log') size 10m;Database altered.SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 50 104857600 1 NO INACTIVE 445162343 28-APR-10
2 1 51 104857600 1 NO CURRENT 445436170 29-APR-10
3 1 49 104857600 1 NO INACTIVE 444889088 27-APR-10
4 1 0 10485760 1 YES UNUSED 04 rows selected.SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER
---------- ------- ------- ------------------------------------------------------------
3 ONLINE /DB_Data/oradata/test/redo03.log
2 ONLINE /DB_Data/oradata/test/redo02.log
1 ONLINE /DB_Data/oradata/test/redo01.log
4 ONLINE /DB_Data/oradata/test/redo04.log4 rows selected.SQL> alter database drop logfile group 4;Database altered.SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 50 104857600 1 NO INACTIVE 445162343 28-APR-10
2 1 51 104857600 1 NO CURRENT 445436170 29-APR-10
3 1 49 104857600 1 NO INACTIVE 444889088 27-APR-103 rows selected.SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER
---------- ------- ------- ------------------------------------------------------------
3 ONLINE /DB_Data/oradata/test/redo03.log
2 ONLINE /DB_Data/oradata/test/redo02.log
1 ONLINE /DB_Data/oradata/test/redo01.log3 rows selected.SQL>