Retrieve Deleted Rows from Oracle Database 12c

Okay so last night I went to clear a table of historical data except for the last 7 days.  When I ran the command, it deleted the rows and I committed the transaction and then realized that I deleted the last 7 days and left the other 360 days worth that I had wanted to delete.  Greater Than vs Less Than, lesson there is to not write code on the fly when your tired at 2am in the morning.

I have flashback turned on and archive logging, but I found a neat little SQL method to get my missing rows back:

Insert into TABLE_I_DELETED_ROWS_FROM
(SELECT * FROM TABLE_I_DELETED_ROWS_FROM AS OF TIMESTAMP TO_TIMESTAMP('2014-07-06 10:45:10 PM','YYYY-MM-DD HH:MI:SS PM')
WHERE MYDATEFIELD > TO_DATE('2016-06-29','YYYY-MM-DD'))

I executed this at about 10:55 pm, and it put the 10,000 rows I deleted back into my system that were there at between 06-29 and 07-06.

Nice and easy.

This error is just one that drives you crazy. PeopleSoft has been making the move from a 32bit application to a 64bit application. Almost everywhere you will find the that the application runs in 64bit mode, except for in windows. The client application is still a 32-bit client. So if you are using Oracle for example, you need to ensure that the Oracle client software on the windows machine is 32-bit. This is also true with the tuxedo service, when downloading patches make sure that you download the 32-bit patches.

So if you encounter this error, make sure that the 32-bit libraries are installed and are being referred to for the client. DBBIN variable specifically, and if necessary you may need to add the binary path to the add to path variable in the configuration.

When working in one database you can make reference to data in another database by creating a database link.

To create an Oracle database link, the quickest way is to just create a public database link using a user id in the other database that has permissions to reference the data you need. {LINKNAME} = the name for the link you want to create, {LINKUSER} = the user on the remote database that has access, and {LINKUSERPASSWORD} is that users password.  {TNSNAMES-DB-ENTRY} is the database entry name from the TNSNAMES.ora file.

create public database link {LINKNAME} connect to {LINKUSER} identified by {LINKUSERPASSWORD using ‘{TNSNAMES-DB-ENTRY}’;

To Reference the data from the linked database, simply add the link identifier to the table you are selecting – for example:

select * from remotetable@{LINKNAME};

To Remove the database link:

drop public database link {LINKNAME};

Outer joins is just one of the items that is required from time to time, but since I typically never recommend using them with PeopleSoft, I always forget the syntax.  Since my days go back to Oracle 7, I remember the messy (+) notation and so I always end up looking it up.  The good news is Oracle superseded the (+) notation by going to ISO 99 standard as of Oracle 9i, however it still supports (+) notations.

There are three types of joins you can do, left outer, right outer and full outer join. In a left outer join, all the records of the table listed on the left will be returned with the combined results of the join to the table listed on the right, however, if the right listed table does not have a matching row, NULLS will be returned for the columns of the right listed table.  The right outer join does the same thing just in reverse, whereas the full outer join basically does a UNION of the left & right outer joins.

Some database platforms do not support full outer joins so you can basically do it by doing a left outer join combined with with a right outer join using the union statement.

SELECT A.NAME, D.DEPARTMENT FROM EMPLOYEE A LEFT OUTER JOIN DEPARTMENT D ON A.DEPTID = D.DEPTID;

If you want to do the Oracle (+) syntax:

SELECT A.NAME, D.DEPARTMENT FROM EMPLOYEE A, DEPARTMENT D WHERE A.DEPTID = E.DEPTID (+);

When doing SQL in Oracle where you want to search for something containing a string, you can use the like command.

 Select * from table1 where field1 like ‘somevalue’;

This will return rows with field1 = somevalue, not exactly useful.  But you can use the wildcard ‘%’ so

 Select * from table1 where field1 like ‘%somevalue%’;

This will return rows with field1 containing the string ‘somevalue’ in any form.  There is another wildcard ‘_’ which will match any character in that position, so

Select * from tabe1 where field1 like ‘_omevalue’;

This will return rows with field1 = aomevalue, bomevalue, comevalue, domevalue, …., zomevalue

If you want to use the the literal of % and _ then you will have to put an escape character in front of it, and tell Oracle what the escape character is:

Select * from table1 where field1 like ‘lookfor\%’ escape ‘\’

This will return rows where field1 = ‘lookfor%’