SQLCL-Project Survival Guide

Murphy's Law Edition: If it can fail, it will fail!

---

Table of Contents

1. Deployment Monitoring

2. Session Troubleshooting

3. Deployment Recovery

4. Known Issues & Lessons Learned

5. Quick Reference

---

Deployment Monitoring

Check Current Deployment Status

When you need to see where your deployment is currently at:

-- View recent deployment actions
SELECT * FROM databasechangelog_actions ORDER BY id DESC;

-- View deployment execution history
SELECT * FROM databasechangelog ORDER BY dateexecuted DESC;

Use Case: Identify which changeset is currently being executed or which was the last successful deployment step.

---

Session Troubleshooting

Identify Active SQLCL Session

To check which SQL statement is currently running in your SQLCL session:

-- Find active sessions
SELECT sql_id, osuser FROM v$session WHERE status = 'ACTIVE';

-- Get the actual SQL statement
SELECT * FROM v$sql WHERE sql_id IN ('sql_id');

Kill a Stuck Session

If a session is hanging or needs to be terminated:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

⚠️ Warning: Make sure you have the correct SID and SERIAL# before executing this command!

---

Deployment Recovery

Release Locks After Cancelled Deployment

If you cancel or kill a SQLCL deployment session and need to restart:

sqlcl> lb release-locks;

When to use:

• After manually cancelling a deployment (Ctrl+C)

• After killing a stuck SQLCL session

• When you encounter "locked" errors on restart

---

Known Issues & Lessons Learned

🔥 Critical: FBDA + ALTER TABLE RENAME COLUMN

Issue: Oracle Project Command generates ALTER TABLE ... RENAME COLUMN statements when detecting column renames. However, when the table is in Flashback Data Archive (FBDA), this operation is not allowed.

Symptom:

• Instead of throwing an error, the FBDA process runs to 100% CPU

• Database becomes unresponsive

• Environment requires reboot

Root Cause: Oracle Project Command does not check if a table is in FBDA before generating rename column statements.

Solution:

1. Remove the table from Flashback Data Archive

   ALTER TABLE your_table NO FLASHBACK ARCHIVE;

2. Truncate the table (if data loss is acceptable)

   TRUNCATE TABLE your_table;

3. Re-run the deployment command

Prevention:

• Always check if tables are in FBDA before structural changes

• Consider excluding FBDA tables from automated schema comparison tools

-- Check if table is in FBDA
SELECT table_name, flashback_archive_name 
FROM user_flashback_archive_tables 
WHERE table_name = 'YOUR_TABLE';

---