col segment_name for a30看一下我的一個(gè)數(shù)據(jù)庫(kù):
col owner for a10
SELECT *
FROM (SELECT owner, segment_name,segment_type,block_id, blocks
FROM dba_extents
WHERE tablespace_name = 'SYSTEM' and file_id='&fileid'
ORDER BY block_id DESC)
WHERE ROWNUM < 11;
SQL> col segment_name for a30我們注重到高水位上有些索引是可以通過(guò)rebuild來(lái)改變其位置的,但是在正常模式下,Oracle不答應(yīng)rebuild這些索引:
SQL> col owner for a10
SQL> SELECT *
2 FROM (SELECT owner, segment_name,segment_type,block_id, blocks
3 FROM dba_extents
4 WHERE tablespace_name = 'SYSTEM' and file_id='&fileid'
5 ORDER BY block_id DESC)
6 WHERE ROWNUM < 11;
Enter value for fileid: 1
old 4: WHERE tablespace_name = 'SYSTEM' and file_id='&fileid'
new 4: WHERE tablespace_name = 'SYSTEM' and file_id='1'
OWNER SEGMENT_NAME SEGMENT_TYPE BLOCK_ID BLOCKS
---------- ------------------------------ ------------------ ---------- ----------
SYS EYGLE_ip TABLE 25609 128
SYS EYGLE_IP TABLE 25481 128
SYS EYGLE_IP TABLE 25353 128
SYS I_H_OBJ#_COL# INDEX 25305 8
SYS TEST TABLE 25297 8
SYS I_TEST INDEX 25289 8
SYS TEST TABLE 25281 8
SYS T TABLE 25273 8
SYS EYGLE_IP TABLE 25265 8
SYS EYGLE_IP TABLE 25257 8
10 rows selected.
SQL> alter index I_H_OBJ#_COL# rebuild;有兩種方式可以使得這些對(duì)象答應(yīng)被重建:
alter index I_H_OBJ#_COL# rebuild
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
SQL> shutdown immediate;2.通過(guò)一個(gè)內(nèi)部事件
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup migrate;
ORACLE instance started. Total System Global Area 139531744 bytes
Fixed Size 452064 bytes
Variable Size 121634816 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> alter index I_H_OBJ#_COL# rebuild;Index altered.SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.Total System Global Area 139531744 bytes
Fixed Size 452064 bytes
Variable Size 121634816 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> alter system set event='38003 trace name context forever, level 10' scope=spfile; System altered.SQL> startup force;38003事件的作用是:CBO Disable column stats for the dictionary objects in recursive SQL
ORACLE instance started.Total System Global Area 139531744 bytes
Fixed Size 452064 bytes
Variable Size 121634816 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> alter index i_h_obj#_col# rebuild;Index altered.
新聞熱點(diǎn)
疑難解答
圖片精選