All we need is an easy explanation of the problem, so here it is.
I wrote a script to run with SQLPlus which deletes the previous seven days of data in my Oracle XE database. I logged into the database with SQLPlus, ran the script, and it showed me the correct rows had been deleted. I tried to query the same date range again just to make sure, and the query returned no results (i.e. the records were deleted).
However, when I then connect to the database using Oracle SQL Manager, I can go to the same table and all the data is still there! If I run the query in SQL Manager, it is then deleted from the table in SQL manager.
Anyone know why this is happening? The credentials I use to log in are the same in both instances and I’ve made sure to be logged out of SQL Manager when running SQLPlus and vice-versa. I’m running version 18c of XE database.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Does your SQLPlus script execute a
COMMIT when it finishes the
DELETE? If not, then the
DELETE transaction hasn’t been committed (persisted) yet. From the same connection in SQLPlus it’ll look like the rows were deleted, but that’s because it’s still in the middle of the same active transaction.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂