-- ========================================================
-- FINAL WORKING SCRIPT – DROP COLUMN + PARALLEL 8 (NO PLS-00103)
-- ========================================================
-- >>> EDIT ONLY THESE THREE LINES <<<
DEFINE owner = 'HR'
DEFINE table_name = 'BIG_TABLE'
DEFINE column_name = 'OLD_COLUMN'
-- ========================================================
PROMPT === 1. Mark column as UNUSED (instant) ===
ALTER TABLE &&owner..&&table_name SET UNUSED COLUMN &&column_name;
COMMIT;
PROMPT Column &&column_name is now UNUSED and invisible to all new transactions.
-- ========================================================
PROMPT === 2. Submit background job with PARALLEL 8 ===
DECLARE
v_job_action CLOB;
BEGIN
-- First remove any old job with the same name
BEGIN
DBMS_SCHEDULER.DROP_JOB(job_name => 'DROP_UNUSED_&&table_name._P8', force => TRUE);
EXCEPTION WHEN OTHERS THEN NULL;
END;
-- Build the job action as CLOB (this avoids all quoting nightmares)
v_job_action := q'[
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL DDL PARALLEL 8';
-- Do the heavy physical drop with checkpointing
EXECUTE IMMEDIATE
'ALTER TABLE ]' || '&&owner..&&table_name' || q'[ DROP UNUSED COLUMNS CHECKPOINT 10000';
DBMS_OUTPUT.PUT_LINE('Physical drop with PARALLEL 8 completed successfully');
END;]';
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'DROP_UNUSED_&&table_name._P8',
job_type => 'PLSQL_BLOCK',
job_action => v_job_action,
start_date => SYSTIMESTAMP,
enabled => TRUE,
auto_drop => TRUE,
comments => 'Physical drop of unused columns with PARALLEL 8'
);
DBMS_OUTPUT.PUT_LINE('Background job submitted successfully with PARALLEL 8');
END;
/
PROMPT
PROMPT Job is now running in the background with 8 parallel processes.
PROMPT You can close TOAD/SQL Developer immediately — everything continues on the server.
-- ========================================================
PROMPT === MONITORING COMMANDS (run any time) ===
PROMPT
PROMPT -- Job status
PROMPT SELECT job_name, status, actual_start_date, run_duration, error#
PROMPT FROM user_scheduler_job_run_details
PROMPT WHERE job_name = 'DROP_UNUSED_&&table_name._P8';
PROMPT -- Real-time progress (you will see ~8 rows, one per parallel slave)
PROMPT SELECT sid, opname, sofar, totalwork,
PROMPT ROUND(sofar/totalwork*100,2)||'%' pct, elapsed_seconds/60 mins
PROMPT FROM v$session_longops
PROMPT WHERE opname = 'Drop Column' AND target LIKE '%&&table_name%';
PROMPT Done! Column already disappeared from queries, physical cleanup running safely in background.
====
-- ========================================================
-- SAFE DROP COLUMN + PARALLEL 8 - FULL SCRIPT (Dec 2025)
-- ========================================================
-- >>>>>>>> EDIT THESE 4 LINES ONLY <<<<<<<<
DEFINE owner = 'YOUR_SCHEMA_NAME'
DEFINE table_name = 'YOUR_TABLE_NAME'
DEFINE column_name = 'YOUR_COLUMN_NAME'
-- Set to 'Y' ONLY if you really want to kill the stuck session (rarely needed now)
DEFINE KILL_CURRENT_SESSION = 'N' -- Keep 'N' in almost all cases
-- ========================================================
PROMPT
PROMPT === STEP 1: Mark column as UNUSED (instant, zero downtime) ===
ALTER TABLE &&owner..&&table_name SET UNUSED COLUMN &&column_name;
COMMIT;
PROMPT Column &&column_name is now invisible to all new queries and DESC!
-- ========================================================
PROMPT
PROMPT === STEP 2: Schedule PHYSICAL DROP with PARALLEL 8 in background ===
BEGIN
-- Clean any previous job with same name
BEGIN
DBMS_SCHEDULER.DROP_JOB('DROP_UNUSED_&&table_name||'_PAR8', FORCE => TRUE);
EXCEPTION WHEN OTHERS THEN NULL;
END;
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'DROP_UNUSED_&&table_name||'_PAR8',
job_type => 'PLSQL_BLOCK',
job_action => q'[
BEGIN
-- Enable parallel DML inside the job
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL DDL PARALLEL 8';
DBMS_OUTPUT.PUT_LINE('Starting physical drop with PARALLEL 8...');
EXECUTE IMMEDIATE
'ALTER TABLE &&owner..&&table_name DROP UNUSED COLUMNS CHECKPOINT 10000';
DBMS_OUTPUT.PUT_LINE('Physical drop completed successfully with PARALLEL 8');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR: '||SQLERRM);
RAISE;
END;
]',
start_date => SYSTIMESTAMP + 5/1440, -- start 5 seconds from now
enabled => TRUE,
auto_drop => TRUE,
comments => 'Physical drop of unused column with PARALLEL 8'
);
DBMS_OUTPUT.PUT_LINE('Background job with PARALLEL 8 submitted successfully!');
END;
/
PROMPT
PROMPT Job is now running in the background with 8 parallel processes.
PROMPT You can safely close TOAD/SQL Developer now — everything continues on the server.
-- ========================================================
PROMPT
PROMPT === MONITORING QUERIES (copy-paste & run anytime) ===
PROMPT
PROMPT -- 1. Job status
PROMPT SELECT job_name, status, actual_start_date, run_duration, error#
PROMPT FROM user_scheduler_job_run_details
PROMPT WHERE job_name = 'DROP_UNUSED_&&table_name||'_PAR8'
PROMPT ORDER BY log_date DESC;
PROMPT
PROMPT -- 2. Real-time progress with PARALLEL 8 slaves
PROMPT SELECT sid, username, opname, target, sofar, totalwork,
PROMPT ROUND(sofar/totalwork*100,2) || '%' pct_done,
PROMPT elapsed_seconds/60 mins_running, message
PROMPT FROM v$session_longops
PROMPT WHERE opname = 'Drop Column'
PROMPT AND target LIKE '%&&table_name%'
PROMPT AND sofar <> totalwork;
PROMPT
PROMPT -- 3. See the 8 parallel execution servers in action
PROMPT SELECT sid, px_server#, px_server_group, degree
PROMPT FROM v$session
PROMPT WHERE program LIKE '%(P0%'
PROMPT AND sql_id = (SELECT sql_id FROM v$session WHERE program LIKE '%scheduler%');
PROMPT
PROMPT -- 4. Confirm column is gone after job finishes
PROMPT SELECT column_name, hidden_column, segment_column_id
PROMPT FROM user_tab_cols
PROMPT WHERE table_name = UPPER('&&table_name')
PROMPT AND column_name = UPPER('&&column_name');
PROMPT
PROMPT Script finished. Your table stays 100% available the whole time.
PROMPT Typical speed gain with PARALLEL 8: 5–7× faster than serial drop.
-- End of script
No comments:
Post a Comment