SQL Developer, Database Copy and TRUNCATING unwanted tables

I just experienced something scary with SQL Developer and the “Database Copy” utility. One table in production was truncated by a mistake. I have always been a little against using the SQL Developer functionality “Database Copy …” in production, but for this one task during deployment of a new release, this customer (including me) have always used this feature. The customer have two somewhat static tables in a meta database (in a test zone), where they store table and columns comments (don’t ask me why they are not using the COMMENT feature in the Oracle database). During a release the customer copy the data from these two tables into two production databases. By accident or some kind of bug SQL Developer started to truncate ALL tables in the schema.

 

Setup:

Note! All database described below are version 19.3, and are running on Exadata machines. In the description below all names of objects, schemas and databases are changed for this blogg.

We have a database MY_SOURCE which stores metadata information (for instance table and column comments) about the database model of our ECP application. The two tables in question (COMMENTS_TALBES and COMMENTS_COLUNSN) are stored in a schema called “SCOTT”. This is a database maintained in the test restricted environment.

In addition, we have two production databases, MY_DEST and MY_DEST2, where the different bank schemas is distributed. In addition to the bank specific schemas, we also have an common schema for different meta data (non bank specific). I’ll call this schema also for “SCOTT”.

For simplicity I’ll call the application MY_APP.

What we tried to do?

Everytime we have a new release of the MY_APP application (and update of the bank schemas) we copy the data in the two tables (KP_TAB_COMMENTS and KP_COL_COMMENTS) from the SCOPTT schema in the MY_SOURCE database, to two identical tables in the SCOTT schemas in the MY_DEST and MY_DEST2 databases. To do this we have usually used SQL Developer and the “Database Copy” feature.

What happened at the last release?

At the last release  I was called to assist in copying the data (for the two tables in question) from the MY_SOURCE to the two production databases (as usual). First I copied the data from MY_SOURCE database to the MY_DEST database. This went perfectly fine. The I used the same method to copy the data from the same two table into the MY_DEST2 database. This is when the “disaster” happened.

The method: Using the “Database Copy” utility

Before explaining what went wrong, I’ll show you the method I used to copy data from DESC database to the ECP2 database. I’ll do this step by step showing you screenshots.

I start the process by choosing “Tools” from the SQL Developer main menu. Then I choose “Database Copy”, and continue with the following:

Step 1

In step 1 I choose source and destination connections. Because the tables already exists in the destination schema, I uncheck the “Copy DDL”. I check both the “Copy Data” and the “Truncate Destination Before Copying” options, and click “Next”

Step 2

Since I only want to copy data from two tables, I only check the “Table” options, and click “Next”.

Step 3

Next I look-up the two tables in question, and select them for copying data. Then I click “Next”.

Step 4

I have a quick look at the summary, and click “Finish”.

As I said above, the first part copying data from the MY_SOURCE database to the MY_DEST database when perfectly OK.

The task resulted in the following log:

Copy Option: Objects Copy
Source Connection: MY_SOURCE_CONN
Destination Connection: MY_DEST_CONN
DDL Objects:
Tables
Data Objects:
Tables
COMMENTS_COLUMNS 
COMMENTS_TABLES
Copy DDL: No
Copy Data: Yes
Truncate Existing Objects
--- START --------------------------------------------------------------------


--- END --------------------------------------------------------------------

--- START --------------------------------------------------------------------
  TRUNCATE TABLE "COMMENTS_COLUMNS";


Table "COMMENTS_COLUMNS" truncated.

  TRUNCATE TABLE "COMMENTS_TABLES";


Table "COMMENTS_TABLES" truncated.

--- END --------------------------------------------------------------------

--- START --------------------------------------------------------------------
Moving Data for object COMMENTS_COLUMNS

Insert 11,355 rows into COMMENTS_COLUMNS in 6,833 milliseconds
--- END --------------------------------------------------------------------

Moving Data for object COMMENTS_TABLES

Insert 676 rows into COMMENTS_TABLES in 344 milliseconds
--- END --------------------------------------------------------------------

--- START --------------------------------------------------------------------

--- END --------------------------------------------------------------------

So far, so good. The logs shows that everything went ok.

“The disaster”: SQL Developer starts truncating tables

Then I start the next part of the job: to copy the same data into the MY_DEST2 database. Because I have just copied the data to the MY_DEST database, I choose this use this as the source (instead of the MY_SORCE). This does not really matter, and is not the cause of the disaster.

I use the same method (as shown above) to do this copying. The ONLY difference is that this time the two tables (COMMENTS_TABLE and COMMENTS_COLUMNS) is for some reason deleted from the SCOTT schema in the MY_DEST2 database. I forget about this and uncheck the “Copy DDL” option, just as I did above (when copying from MY_SOURCE to MY_DEST). Of course the copying of the data for the two tables will fail, but the outcome is a whole lot worse.

Here is the log from what happened:

Copy Option: Objects Copy
Source Connection: MY_DEST
Destination Connection: MY_DEST2
DDL Objects:
Tables
Data Objects:
Tables
KP_COL_COMMENTS
KP_TAB_COMMENTS
Copy DDL: No
Copy Data: Yes
Truncate Existing Objects
--- START --------------------------------------------------------------------
Skipping COMMENTS_COLUMNS
Skipping COMMENTS_TABLES
--- END --------------------------------------------------------------------

--- START --------------------------------------------------------------------

--- END --------------------------------------------------------------------

--- START --------------------------------------------------------------------

  TRUNCATE TABLE "OTHER_PRODUDUCTION_TABLE";

Table "OTHER_PRODUDUCTION_TABLE" truncated.

  TRUNCATE TABLE "OTHER_PRODUDUCTION_TABLE2";

Error starting at line : 1 in command -

  TRUNCATE TABLE "OTHER_PRODUDUCTION_TABLE2"

Error report -
ORA-30657: operation not supported on external organized table
30657.0000 -  "operation not supported on external organized table"
*Cause:    User attempted on operation on an external table which is
           not supported.
*Action:   Don't do that!
--- END --------------------------------------------------------------------

--- START --------------------------------------------------------------------

Moving Data for object OTHER_PRODUDUCTION_TABLE

Insert 20 rows into OTHER_PRODUDUCTION_TABLE in 1,148 milliseconds

--- END --------------------------------------------------------------------

Moving Data for object COMMENTS_COLUMNS
Error occurred inserting data for TABLE: COMMENTS_COLUMNS.  Batch 1 containing 500 rows failed. 
  ORA-00942: table or view does not exist

--- START --------------------------------------------------------------------

--- END --------------------------------------------------------------------

 

The only difference in the procedure this time, was that I choose the MY_DEST connection as the source instead of the MY_SOURCE connection. Else from this I followed exactly the same steps with SQL Developer “Database Copy” feature as described above. And of course that the two tables was missing. But this should not have led SQL Developer to start truncating and copying all other tables than the two I had choosen.

The result

As you see from the log. Now the “Database Copy” feature skips the two chosen tables (COMMENTS_COLUMNS and COMMENTS_TABLE). This I would expect, since the two table was missing in the destination. But instead of stopping here, it starts to TRUCATE all the rest of the tables in the schema. The first table which is truncated is the OTHER_PRODUDUCTION_TABLE table. Luckily the second table is an external table, and the truncate fails with an ORA-message. At this time SQL Developer shows a dialog message with the choices to “skip and continue” or to “Cancel”. At this moment I have realised the mistake that the two tables were missing in the schema, and that I have unchecked the “Copy DDL” option. So I clicked “Cancel”. First when looking at the log (shown above) I realised the disaster. The feature had started truncating all other tables in the schema. This must be a BUG, and not an intended action.




					

Post a Comment

Your email is never published nor shared. Required fields are marked *