Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Suspend large operations that run out of space or reach space limits after executing for a long time, fix the problem, and make the statement resume execution.
In this way you can write applications without worrying about running into space-related errors.
When you suspend a statement, you should log the suspension in the alert log. You should also register a procedure to be executed when the statement is suspended.
Using a view, you can monitor the progress of the statement and indicate whether the statement is currently executing or suspended.
Suspending a statement automatically results in suspending the transaction. Thus all transactional resources are held during a statement suspend and resume. When the error condition disappears, the suspended statement automatically resumes execution.
A resumable space allocation can be suspended and resumed multiple times during execution.
A suspension timeout interval is associated with resumable space allocations. A resumable space allocation that is suspended for the timeout interval (the default is two hours) wakes up and returns an exception to the user.
A suspended statement may be forced to throw an exception using the DBMS_RESUMABLE.ABORT() procedure.
storage settings inappropriate for the table being inserted
storage settings inappropriate for the rollback segment being used for the transaction
SELECT x.ksppinm NAME, y.ksppstvl VALUE, ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND x.ksppinm = '_system_trig_enabled';
-- if necessary enable system event triggers
ALTER SYSTEM SET "_system_trig_enabled" = TRUE SCOPE=BOTH;
CREATE USER resume_demo
IDENTIFIED BY resume_demo
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 1M ON uwdata;
GRANT create session, create table, resumable
GRANT select ON v_$mystat TO resume_demo;
CREATE OR REPLACE TRIGGER logon_trigger
execute immediate 'alter session enable resumable';
WHERE rownum = 1;
set linesize 151
col user_id format 999
col session_id format 999
col timeout format 99999
col name format a25
col sql_text format a35
-- session_id equals gv$session sid
SELECT user_id, session_id, status, timeout, name
SELECT sid, username