OCP-043 SHRINK SPACE CASCADE
来源:互联网 发布:知已的诗句 编辑:程序博客网 时间:2024/05/16 04:18
47. View the Exhibit and examine the properties of the USERS tablespace.
You execute the following statement to shrink the TRANS table existing on the USERS tablespace:
SQL> ALTER TABLE trans SHRINK SPACE CASCADE;
Which objects would be affected by this command? (Choose all that apply.)
A.the TRANS table
B.the B*Tree indexes on the TRANS table
C.the materialized views log of the TRANS table
D.the materialized views based on the TRANS table
E.the large object (LOB) segments of the TRANS table
Answer: AB
48. In one of your online transaction processing (OLTP) applications, users are manipulating and querying
a database table simultaneously. From the Segment Advisor, you find that one of the tables is highly
fragmented and you want to shrink the table immediately without affecting the currently active queries.
Which option would you use with the ALTER TABLE command to achieve this objective?
A.REBUILD
B.CASCADE
C.TRUNCATE
D.ROW MOVEMENT
E.SHRINK SPACE COMPACT
F.SHRINK SPACE CASCADE
Answer: E
Shrinking Database Segments Online
You use online segment shrink to reclaim fragmented free space below the high water mark in an Oracle Database segment. The benefits of segment shrink are these:
Compaction of data leads to better cache utilization, which in turn leads to better online transaction processing (OLTP) performance.
The compacted data requires fewer blocks to be scanned in full table scans, which in turns leads to better decision support system (DSS) performance.
Segment shrink is an online, in-place operation. DML operations and queries can be issued during the data movement phase of segment shrink. Concurrent DML operation are blocked for a short time at the end of the shrink operation, when the space is deallocated. Indexes are maintained during the shrink operation and remain usable after the operation is complete. Segment shrink does not require extra disk space to be allocated.
Segment shrink reclaims unused space both above and below the high water mark. In contrast, space deallocation reclaims unused space only above the high water mark. In shrink operations, by default, the database compacts the segment, adjusts the high water mark, and releases the reclaimed space.
Segment shrink requires that rows be moved to new locations. Therefore, you must first enable row movement in the object you want to shrink and disable any rowid-based triggers defined on the object. You enable row movement in a table with the ALTER
TABLE
... ENABLE
ROW
MOVEMENT
command.
Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:
IOT mapping tables
Tables with rowid based materialized views
Tables with function-based indexes
See Also:
Oracle Database SQL Reference for more information on theALTER
TABLE
command.Invoking Online Segment Shrink
Before invoking online segment shrink, view the findings and recommendations of the Segment Advisor. For more information, see "Using the Segment Advisor".
You invoke online segment shrink with Enterprise Manager (EM) or with SQL commands in SQL*Plus. The remainder of this section discusses the command line method.
Note:
You can invoke segment shrink directly from the Recommendation Details page in EM. (See Figure 14-4.) Or, to invoke segment shrink for an individual table in EM, display the table on the Tables page, select the table, and then click Shrink Segment in the Actions list. (See Figure 14-1.) Perform a similar operation in EM to shrink indexes, materialized views, and so on.You can shrink space in a table, index-organized table, index, partition, subpartition, materialized view, or materialized view log. You do this using ALTER
TABLE
,ALTER
INDEX
, ALTER
MATERIALIZED
VIEW
, or ALTER
MATERIALIZED
VIEW
LOG
statement with the SHRINK SPACE
clause. Refer to Oracle Database SQL Reference for the syntax and additional information on shrinking a database object, including restrictions.
Two optional clauses let you control how the shrink operation proceeds:
The
COMPACT
clause lets you divide the shrink segment operation into two phases. When you specifyCOMPACT
, Oracle Database defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the deallocation of the space until a future time. This option is useful if you have long-running queries that might span the operation and attempt to read from blocks that have been reclaimed. The defragmentation and compaction results are saved to disk, so the data movement does not have to be redone during the second phase. You can reissue theSHRINK SPACE
clause without theCOMPACT
clause during off-peak hours to complete the second phase.The
CASCADE
clause extends the segment shrink operation to all dependent segments of the object. For example, if you specifyCASCADE
when shrinking a table segment, all indexes of the table will also be shrunk. (You need not specifyCASCADE
to shrink the partitions of a partitioned table.) To see a list of dependent segments of a given object, you can run theOBJECT_DEPENDENT_SEGMENTS
procedure of theDBMS_SPACE
package.
As with other DDL operations, segment shrink causes subsequent SQL statements to be reparsed because of invalidation of cursors unless you specify theCOMPACT
clause.
Restrictions on the shrink_clause The shrink_clause
is subject to the following restrictions:
You cannot specify this clause for a cluster, a clustered table, or any object with a
LONG
column.Segment shrink is not supported for tables with function-based indexes or bitmap join indexes.
This clause does not shrink mapping tables of index-organized tables, even if you specify
CASCADE
.You cannot specify this clause for a compressed table.
You cannot shrink a table that is the master table of an
ON
COMMIT
materialized view. Rowid materialized views must be rebuilt after the shrink operation.
Examples
Shrink a table and all of its dependent segments (including LOB segments):
ALTER TABLE employees SHRINK SPACE CASCADE;
Shrink a LOB segment only:
ALTER TABLE employees MODIFY LOB (perf_review) (SHRINK SPACE);
Shrink a single partition of a partitioned table:
ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE;
Shrink an IOT index segment and the overflow segment:
ALTER TABLE cities SHRINK SPACE CASCADE;
Shrink an IOT overflow segment only:
ALTER TABLE cities OVERFLOW SHRINK SPACE;
- OCP-043 SHRINK SPACE CASCADE
- shrink space cascade/compact
- alter table XXX shrink space compact/cascade详解
- shrink space
- 测试alter table shrink space compact cascade及学习user_tables相关列的含义
- 测试alter table shrink space compact cascade及学习user_tables相关列的含义
- oracle下执行alter index .. shrink space cascade错误分析(一)
- oracle下执行alter index .. shrink space cascade错误分析(二)
- SHRINK SPACE CHECK
- shrink space报ora-10635
- 收缩表段(shrink space)
- 收缩表段(shrink space)
- 收缩表段(shrink space)
- shrink space产生大量日志
- 收缩表段(shrink space)
- 表收缩(shrink space)
- 收缩表段(shrink space)
- oracle shrink space 与move
- JavaScript中函数按值传递的解释
- 超过AIX单用户最大进程数导致oracle无法登陆的解决
- 会话跟踪
- 用环境变量控制c/c++的条件编译
- QT学习笔记 QTimer 与信号 槽
- OCP-043 SHRINK SPACE CASCADE
- Request.ServerVariables的用法
- 基于对话框的单实例应用程序
- ubuntu_Install Android SDK on Ubuntu 12.04 LTS
- Struts2拦截器实现原理的理解
- Activity的四种launchMode
- 大数据量,海量数据 处理方法总结
- Rdlc报表出现空白页解决方法
- android activity 生命周期详解