Miscellaneous New Features in Oracle Database 11g Release 1
This article contains a collection of miscellaneous new features listed in the "Oracle Database 11G: New Features for Administrators" OCP syllabus.- Online Table Redefinition Enhancements
- Enhanced Finer Grained Dependency Management
- DDL With the WAIT Option (DDL_LOCK_TIMEOUT)
- Invisible Indexes
- Query Result Cache
- Adaptive Cursor Sharing
- Temporary Tablespace Enhancements
Online Table Redefinition Enhancements
By default, online table redefinitions no longer invalidate dependent objects (PL/SQL, views, synonyms etc.), provided the redefinition does not logically affect them. An exception to this behavior is triggers, which are associated directly with a table.To see this new behavior, first me must create a table with some dependent objects.
If we check the status of the schema objects we can see that all of them are valid.CONN test/test@db11g CREATE TABLE redef_tab ( id NUMBER, description VARCHAR2(50), CONSTRAINT redef_tab_pk PRIMARY KEY (id) ); CREATE VIEW redef_tab_v AS SELECT * FROM redef_tab; CREATE SEQUENCE redef_tab_seq; CREATE OR REPLACE PROCEDURE get_description ( p_id IN redef_tab.id%TYPE, p_description OUT redef_tab.description%TYPE) AS BEGIN SELECT description INTO p_description FROM redef_tab WHERE id = p_id; END; / CREATE OR REPLACE TRIGGER redef_tab_bir BEFORE INSERT ON redef_tab FOR EACH ROW WHEN (new.id IS NULL) BEGIN :new.id := redef_tab_seq.NEXTVAL; END; /
Now we perform an online table redefinition.COLUMN object_name FORMAT A20 SELECT object_name, object_type, status FROM user_objects; OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- REDEF_TAB TABLE VALID REDEF_TAB_SEQ SEQUENCE VALID GET_DESCRIPTION PROCEDURE VALID REDEF_TAB_PK INDEX VALID REDEF_TAB_BIR TRIGGER VALID REDEF_TAB_V VIEW VALID 6 rows selected. SQL>
CONN sys/password@db11g AS SYSDBA
-- Check table can be redefined
EXEC DBMS_REDEFINITION.can_redef_table('TEST', 'REDEF_TAB');
-- Create new table
CREATE TABLE test.redef_tab2 AS
SELECT *
FROM test.redef_tab WHERE 1=2;
-- Start Redefinition
EXEC DBMS_REDEFINITION.start_redef_table('TEST', 'REDEF_TAB', 'REDEF_TAB2');
-- Optionally synchronize new table with interim data before index creation
EXEC DBMS_REDEFINITION.sync_interim_table('TEST', 'REDEF_TAB', 'REDEF_TAB2');
-- Add new PK.
ALTER TABLE test.redef_tab2 ADD (CONSTRAINT redef_tab2_pk PRIMARY KEY (id));
-- Complete redefinition
EXEC DBMS_REDEFINITION.finish_redef_table('TEST', 'REDEF_TAB', 'REDEF_TAB2');
-- Remove original table which now has the name of the new table
DROP TABLE test.redef_tab2;
-- Rename the primary key constraint.
ALTER TABLE test.redef_tab RENAME CONSTRAINT redef_tab2_pk TO redef_tab_pk;Notice that theCONN test/test@db11g COLUMN object_name FORMAT A20 SELECT object_name, object_type, status FROM user_objects; OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- REDEF_TAB_SEQ SEQUENCE VALID GET_DESCRIPTION PROCEDURE VALID REDEF_TAB_V VIEW VALID REDEF_TAB2_PK INDEX VALID REDEF_TAB TABLE VALID 5 rows selected. SQL>
GET_DESCRIPTION procedure and REDEF_TAB_V view are still valid, but the REDEF_TAB_BIR trigger is gone. The trigger was still associated with the original table, renamed to REDEF_TAB2, so when the original table was dropped, the trigger was dropped with it.Enhanced Finer Grained Dependency Management
In previous releases, object dependencies were managed at the object level, so altering an object automatically invalidated all dependent objects. Oracle 11g has more granular dependency management, so only changes that directly affect an object will cause an invalidation. To show this create a table with a dependent package, which is in turn used by a view.
CREATE TABLE dep_tab (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT dep_tab_pk PRIMARY KEY (id)
);
CREATE OR REPLACE PACKAGE dep_api AS
FUNCTION get_desc (p_id IN dep_tab.id%TYPE)
RETURN dep_tab.description%TYPE;
END dep_api;
/
CREATE OR REPLACE PACKAGE BODY dep_api AS
FUNCTION get_desc (p_id IN dep_tab.id%TYPE)
RETURN dep_tab.description%TYPE
AS
l_description dep_tab.description%TYPE;
BEGIN
SELECT description
INTO l_description
FROM dep_tab
WHERE id = p_id;
RETURN l_description;
END get_desc;
END dep_api;
/
CREATE OR REPLACE VIEW dept_tab_v AS
SELECT id, dep_api.get_desc(id) AS description
FROM dep_tab;Add a column to the table and check the status of the schema objects. Prior to 11g we would expect both the package and the view to be invalidated by this operation.COLUMN object_name FORMAT A30 SELECT object_name, object_type, status FROM user_objects; OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ------------------- ------- DEP_TAB TABLE VALID DEP_TAB_PK INDEX VALID DEP_API PACKAGE VALID DEP_API PACKAGE BODY VALID DEPT_TAB_V VIEW VALID 5 rows selected. SQL>
Notice that the package specification and the view are not invalidated, because the table columns they reference are not changed by the addition of the new column. The package body is invalidated, but this is of little consequence since dependent objects reference the package specification, not the package body.ALTER TABLE dep_tab ADD ( record_type NUMBER(1) ); SELECT object_name, object_type, status FROM user_objects; OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ ------------------- ------- DEP_TAB TABLE VALID DEP_TAB_PK INDEX VALID DEP_API PACKAGE VALID DEP_API PACKAGE BODY INVALID DEPT_TAB_V VIEW VALID 5 rows selected. SQL>
Next, recreate the package specification, adding the prototype for a new procedure, then check the status of the schema objects. We would expect this operation to invalidate the dependent view in releases prior to 11g.
CREATE OR REPLACE PACKAGE dep_api AS
FUNCTION get_desc (p_id IN dep_tab.id%TYPE)
RETURN dep_tab.description%TYPE;
PROCEDURE stub;
END dep_api;
/
SELECT object_name, object_type, status FROM user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
DEP_TAB TABLE VALID
DEP_TAB_PK INDEX VALID
DEP_API PACKAGE VALID
DEP_API PACKAGE BODY INVALID
DEPT_TAB_V VIEW VALID
5 rows selected.
SQL>DDL With the WAIT Option (DDL_LOCK_TIMEOUT)
DDL commands require exclusive locks on internal structures. If these locks are not available the commands return with an "ORA-00054: resource busy" error message. This can be especially frustrating when trying to modify objects that are accessed frequently. To get round this Oracle 11g includes theDDL_LOCK_TIMEOUT parameter, which can be set at instance or session level using the ALTER SYSTEM and ALTER SESSION commands respectively.The
DDL_LOCK_TIMEOUT parameter indicates the number of seconds a DDL command should wait for the locks to become available before throwing the resource busy error message. The default value is zero. To see it in action, create a new table and insert a row, but don't commit the insert.Leave this session alone and in a new session, set theCREATE TABLE lock_tab ( id NUMBER ); INSERT INTO lock_tab VALUES (1);
DDL_LOCK_TIMEOUT at session level to a non-zero value and attempt to add a column to the table.The session will wait for 30 seconds before failing.ALTER SESSION SET ddl_lock_timeout=30; ALTER TABLE lock_tab ADD ( description VARCHAR2(50) );
ALTER TABLE lock_tab ADD (
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expiredALTER TABLE command and commit the insert in the first session within 30 seconds, the ALTER TABLE will return a successful message.ALTER TABLE lock_tab ADD ( description VARCHAR2(50) ); Table altered. SQL>
Invisible Indexes
Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless theOPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.The following script creates and populates a table, then creates an invisible index on it.CREATE INDEX index_name ON table_name(column_name) INVISIBLE; ALTER INDEX index_name INVISIBLE; ALTER INDEX index_name VISIBLE;
CREATE TABLE ii_tab (
id NUMBER
);
BEGIN
FOR i IN 1 .. 10000 LOOP
INSERT INTO ii_tab VALUES (i);
END LOOP;
COMMIT;
END;
/
CREATE INDEX ii_tab_id ON ii_tab(id) INVISIBLE;
EXEC DBMS_STATS.gather_table_stats(USER, 'ii_tab', cascade=> TRUE);WHERE clause ignores the index and does a full table scan.Setting theSET AUTOTRACE ON SELECT * FROM ii_tab WHERE id = 9999; ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 7 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| II_TAB | 1 | 3 | 7 (0)| 00:00:01 | ----------------------------------------------------------------------------
OPTIMIZER_USE_INVISIBLE_INDEXES parameter makes the index available to the optimizer.Making the index visible means it is still available to the optimizer when theALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE; SELECT * FROM ii_tab WHERE id = 9999; ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| II_TAB_ID | 1 | 3 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------
OPTIMIZER_USE_INVISIBLE_INDEXES parameter is reset.Invisible indexes can be useful for processes with specific indexing needs, where the presence of the indexes may adversely affect other functional areas. They are also useful for testing the impact of dropping an index.ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE; ALTER INDEX ii_tab_id VISIBLE; ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| II_TAB_ID | 1 | 3 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------
The current visibility status of an index is indicated by the
VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.Query Result Cache
Oracle 11g allows the results of SQL queries to be cached in the SGA and reused to improve performance. Set up the following schema objects to see how the SQL query cache works.The function contains a one second sleep so we can easily detect if it has been executed by checking the elapsed time of the query.CREATE TABLE qrc_tab ( id NUMBER ); INSERT INTO qrc_tab VALUES (1); INSERT INTO qrc_tab VALUES (2); INSERT INTO qrc_tab VALUES (3); INSERT INTO qrc_tab VALUES (4); INSERT INTO qrc_tab VALUES (5); CREATE OR REPLACE FUNCTION slow_function(p_id IN qrc_tab.id%TYPE) RETURN qrc_tab.id%TYPE DETERMINISTIC AS BEGIN DBMS_LOCK.sleep(1); RETURN p_id; END; / SET TIMING ON
Next, we query the test table using the slow function and check out the elapsed time. Each run takes approximately five seconds, one second sleep for each row queried.
SELECT slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
5 rows selected.
Elapsed: 00:00:05.15
SQL>RESULT_CACHE hint to the query tells the server to attempt to retrieve the information from the result cache. If the information is not present, it will cache the results of the query provided there is enough room in the result cache. Since we have no cached results, we would expect the first run to take approximately five seconds, but subsequent runs to be much quicker.
SELECT /*+ result_cache */ slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
5 rows selected.
Elapsed: 00:00:05.20
SELECT /*+ result_cache */ slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
5 rows selected.
Elapsed: 00:00:00.15
SQL>RESULT_CACHE_MODE parameter. When it is set to MANUAL, the RESULT_CACHE hint
must be used for a query to access the result cache.If we set theSHOW PARAMETER RESULT_CACHE_MODE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ result_cache_mode string MANUAL SQL>
RESULT_CACHE_MODE parameter to FORCE, the result cache is used by default, but we can bypass it using the NO_RESULT_CACHE hint.
ALTER SESSION SET RESULT_CACHE_MODE=FORCE;
SELECT slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
5 rows selected.
Elapsed: 00:00:00.14
SELECT /*+ no_result_cache */ slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
5 rows selected.
Elapsed: 00:00:05.14
SQL>Adaptive Cursor Sharing
DBAs are always encouraging developers to use bind variables, but when bind variables are used against columns containing skewed data they sometimes lead to less than optimum execution plans. This is because the optimizer peaks at the bind variable value during the hard parse of the statement, so the value of a bind variable when the statement is first presented to the server can affect every execution of the statement, regardless of the bind variable values.Oracle 11g uses Adaptive Cursor Sharing to solve this problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. This functionality requires no additional configuration. The following code provides and example of adaptive cursor sharing.
First we create and populate a test table.
DROP TABLE acs_test_tab;
CREATE TABLE acs_test_tab (
id NUMBER,
record_type NUMBER,
description VARCHAR2(50),
CONSTRAINT acs_test_tab_pk PRIMARY KEY (id)
);
CREATE INDEX acs_test_tab_record_type_i ON acs_test_tab(record_type);
DECLARE
TYPE t_acs_test_tab IS TABLE OF acs_test_tab%ROWTYPE;
l_tab t_acs_test_tab := t_acs_test_tab();
BEGIN
FOR i IN 1 .. 100000 LOOP
l_tab.extend;
IF MOD(i,2)=0 THEN
l_tab(l_tab.last).record_type := 2;
ELSE
l_tab(l_tab.last).record_type := i;
END IF;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).description := 'Description for ' || i;
END LOOP;
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO acs_test_tab VALUES l_tab(i);
COMMIT;
END;
/
EXEC DBMS_STATS.gather_table_stats(USER, 'acs_test_tab', method_opt=>'for all indexed columns size skewonly', cascade=>TRUE);RECORD_TYPE column is skewed, as shown by the presence of a histogram against the column.Next, we query the table and limit the rows returned based on theSELECT column_name, histogram FROM user_tab_cols WHERE table_name = 'ACS_TEST_TAB'; COLUMN_NAME HISTOGRAM ------------------------------ --------------- ID NONE RECORD_TYPE HEIGHT BALANCED DESCRIPTION NONE 3 rows selected. SQL>
RECORD_TYPE column with a literal value of "1".
SET LINESIZE 200
SELECT MAX(id) FROM acs_test_tab WHERE record_type = 1;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
1
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID cgt92vnmcytb0, child number 0
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = 1
Plan hash value: 3987223107
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 1;
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
1
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 0
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 3987223107
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 2;
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
100000
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 0
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 3987223107
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------V$SQL view entry for this query, we can see the IS_BIND_SENSITIVE column is marked as 'Y', so Oracle is aware this query may require differing execution plans depending on the bind variable values, but currently the IS_BIND_AWARE column is marked as 'N', so Oracle as not acted on this yet.If we run the statement using the second bind variable again, we can see that Oracle has decided to use an alternate, more efficient plan for this statement.SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_text = 'SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type'; SQL_ID CHILD_NUMBER I I ------------- ------------ - - 9bmm6cmwa8saf 0 Y N 1 row selected. SQL>
VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 2;
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
100000
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 1
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 509473618
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 138 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| ACS_TEST_TAB | 48031 | 422K| 138 (2)| 00:00:02 |
-----------------------------------------------------------------------------------V$SQL view, which now has the IS_BIND_AWARE column maked as "Y".Information about the cursor sharing histograms, statistics and selectivity is displayed using theSELECT sql_id, child_number, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_text = 'SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type'; SQL_ID CHILD_NUMBER I I ------------- ------------ - - 9bmm6cmwa8saf 0 Y N 9bmm6cmwa8saf 1 Y Y 2 rows selected. SQL>
V$SQL_CS_HISTOGRAM, V$SQL_CS_STATISTICS and V$SQL_CS_SELECTIVITY views respectively.SQL> SELECT * FROM v$sql_cs_histogram WHERE sql_id = '9bmm6cmwa8saf'; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT -------- ---------- ------------- ------------ ---------- ---------- 319A4A1C 4171522382 9bmm6cmwa8saf 1 0 0 319A4A1C 4171522382 9bmm6cmwa8saf 1 1 1 319A4A1C 4171522382 9bmm6cmwa8saf 1 2 0 319A4A1C 4171522382 9bmm6cmwa8saf 0 0 1 319A4A1C 4171522382 9bmm6cmwa8saf 0 1 1 319A4A1C 4171522382 9bmm6cmwa8saf 0 2 0 6 rows selected. SQL> SELECT * FROM v$sql_cs_statistics WHERE sql_id = '9bmm6cmwa8saf'; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME -------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ---------- 319A4A1C 4171522382 9bmm6cmwa8saf 1 2064090006 Y 1 50001 499 0 319A4A1C 4171522382 9bmm6cmwa8saf 0 2342552567 Y 1 3 3 0 2 rows selected. SQL> SELECT * FROM v$sql_cs_selectivity WHERE sql_id = '9bmm6cmwa8saf'; ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH -------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ---------- 319A4A1C 4171522382 9bmm6cmwa8saf 1 =L_RECORD_T 0 0.432283 0.528346 1 row selected. SQL>
Temporary Tablespace Enhancements
Oracle 11g has a new view calledDBA_TEMP_FREE_SPACE that displays information about temporary tablespace usage.
Armed with this information, you can perform an online shrink of a temporary tablespace using theSQL> SELECT * FROM dba_temp_free_space; TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE ------------------------------ --------------- --------------- ---------- TEMP 56623104 56623104 55574528 1 row selected. SQL>
ALTER TABLESPACE command.The shrink can also be directed to a specific tempfile using theSQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 40M; Tablespace altered. SQL> SELECT * FROM dba_temp_free_space; TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE ------------------------------ --------------- --------------- ---------- TEMP 42991616 1048576 41943040 1 row selected. SQL>
TEMPFILE clause.TheSQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/u01/app/oracle/oradata/DB11G/temp01.dbf' KEEP 30M; Tablespace altered. SQL> SELECT * FROM dba_temp_free_space; TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE ------------------------------ --------------- --------------- ---------- TEMP 31522816 65536 31457280 1 row selected. SQL>
KEEP clause specifies the minimum size of the tablespace or tempfile. If this is omitted, the database will shrink the tablespace or tempfile to the smallest possible size.For more information see:SQL> ALTER TABLESPACE temp SHRINK SPACE; Tablespace altered. SQL> SELECT * FROM dba_temp_free_space; TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE ------------------------------ --------------- --------------- ---------- TEMP 1114112 65536 1048576 1 row selected. SQL>
- Cross-Session PL/SQL Function Result Cache in Oracle Database 11g Release 1
- Redefining Tables Online
- Finer Grained Dependencies
- DDL_LOCK_TIMEOUT
- CREATE INDEX
- ALTER INDEX
- Result Cache Concepts
- Peeking of User-Defined Bind Variables
- Temporary Tablespaces
Back to the Top.
