Tag Archives: SQL

SQL*Plus Enhancements

12c, SQL,

PDB Support

SQL*Plus includes support for the multitenant architecture.

The SHOW command displays information about PDBs.

SQL> SHOW CON_ID

CON_ID
------------------------------
1
SQL> SHOW CON_NAME

CON_NAME
------------------------------
CDB$ROOT
SQL> SHOW PDBS

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO

Command History

Before we can use the HISTORY command we have to make sure it is turned on. In the example below we attempt to use the HISTORY command with no options and we are told the HISTORY command is not enabled, so we enable it as instructed. Continue reading SQL*Plus Enhancements →

Mark data deleted or In-Database Archiving

12c, SQL,

Rather than deleting data, some applications have a concept of “mark for delete”, so the data remains present in the table, but is not visible to the application. This is usually achieved by doing the following.

  • Add an extra column to the relevant tables that holds a flag to indicate the data is deleted.
  • Add an extra predicate to every statement that checks the deleted status, like “WHERE deleted = 'N'“, to exclude the deleted rows from the SQL and then create a view on that table to display only “active” records.

In-Database Archiving is a feature added to Oracle Database 12c to allow this type of “mark for delete” functionality out-of-the-box, with fewer changes to the existing application code.

Continue reading Mark data deleted or In-Database Archiving →