1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Oracle 9i New Feature - Resumable Space Management

Oracle 9i New Feature - Resumable Space Management

Initialization parameters:
There are no initialization parameters associated with this feature.
Purpose of this feature:
If there is a long running query or transaction that gets effected or fails because of the space constraints this new feature is helpful to identify those problems and fix them without redoing the entire query or transaction. The space failures include the following:
Error Conditions That Are Correctable:
ORA - 01650 (unable to extend RBS)
ORA - 01653 ( unable to extend table)
ORA - 01654 (unable to extend index)
ORA-01628 ( Max extents reached for the RBS)
ORA-01631 ( max extents reached for the table)
ORA-01654(max extents reached for the index)
ORA - 01536 (space quota exceeded for the tablespace)
are the general messages that can be faced while a transaction or a query is being executed against some objects of the database to process the data or to insert the data. This new feature enables to suspend that transaction and resume the same after the table or index is taken care of by providing additional space foe the index/table/tablespace. The activity of the session can be suspended and resumed.
Operations That Are Resumable:
01. select statements that require temporary segments for sorting purposes.
02. DML statements like insert, update and delete that generate undo.
03. DDL statements like the following:
a. create table as select
b. create index
c. alter index .. rebuild
d. alter (table or index) .. (move partition or rebuild partition )
e. create materialized view or materialized view log
04. Import and Export - for these utilities there are separate parameters associated with these tools.
Parameters Associated with Export Utility
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
Parameters Associated with Import Utility
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
05. SQL Loader - for this there are separate 'resumable' parameters:
resumable -- enable or disable resumable for current session (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)
Basically the limitations are concentrated around the system managed or dictionary managed tablespace/s and objects in that/those tablespace/s.
01. DDL like create table or create index with explicit 'MAXEXTENTS' clause which leads to space constraints and the action gets aborted, the resumable space management can not be done by Oracle as that is an activity prior to creation of an object. The resumable Space Management activity is for those DML and DDL activities that are used after the object comes into existence.
Save: This limitation can be over come if 'maxextents' is set to 'unlimited' or by using Locally Managed Tablespaces.
02. If the Rollback Segments are located in the 'dictionary managed' Rollback Segments Tablespace then space allocation for those RBS is not 'resumable'. However, space allocation for the user objects like tables, indexes and like objects, is 'resumable.
Save: This limitation can be overcome by using Automated Undo Management (AUM) or by using Locally Managed Tablespaces.
Impact of Enabling 'Resumable Space Management' feature
It holds up the system resources and hence the users are to be granted that privilege.
Alter user <username> grant resumable to <username>;
This privilege is granted by default to DBA role.
Enabling and disabling Resumable Space Allocation
Resumable Space Management is controlled on a session by session level and is disabled by default..
At Session Level 'Resumable Space Allocation:
At the session level unless 'Resumable Space Allocation' is enabled, it cannot be used.
To enable the feature the following statement is to be issued:
alter session enable resumable;
to disable:
alter session disable resumable;

The suspended session is enabled after a set time out period.
At the session level 'timeout interval' can also be set by issuing the following statement:
alter session enable resumable timeout 3600;
At the session level naming the resumable sessions are enabled using the following statement:
Alter session enable resumable timeout 3600 name 'insert into table';

At the database level DBA can set the 'resumable space allocation' for each session when ever a user is logged into by creating a database level after suspend trigger.
create or replace trigger resumable_default_timeout
after suspend
on database
-- 3600 is the number of seconds, which means 1 hour.
create or replace trigger resumable_session_start
after logon on database
execute immediate 'alter session enable resumable';
A suggestion to mange the Errors generated because of the constraints of space
Let a table be created in the schema of SYS to deposit all the space allocation errors raised for various objects in designated table. The sample script is provided by Oracle:
This package has five procedures that can be used in concert with resumable space management. They are
1. abort
This is to cancel all the suspended statements for a given session.
dbms_resumable.abort(session_id in number);
2. get_session_time_out

This is a function and returns the current value of a given session's resumable space management time out setting in seconds in the form of a number.
SQL> select dbms_resumable.get_session_timeout(8) from dual;
This procedure sets the time out period for the session before the suspended statement is restarted.
SQL> exec dbms_resumable.set_session_timeout(8,14400);
PL/SQL procedure successfully completed.
This is to verify the effect of the statement given above.
SQL> select dbms_resumable.get_session_timeout(8) from dual;
4. get_timeout
this function returns a number value for timeout set for a given or current session.
SQL> select dbms_resumable.get_timeout from dual;
5. set_timeout
this procedure is to set a value for the time out of the session current or given.
SQL> exec dbms_resumable.set_timeout(28800);
PL/SQL procedure successfully completed.
6. space_error_info is a function to return space related errors.
return Boolean;

Reviews and Comments

PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2019

About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy