Table Dropped in Oracle Database ? Worry not, we can recover it

There are a couple of options we can use to recover dropped tables in 19c Oracle database version 

Restore table from Recycle Bin (assume not purged)

To recover the table using the recycle bin method, the recycle bin should be enabled 

Check current value 

SHOW PARAMETER recyclebin;

If this is not enabled, enable it using commands below

ALTER SYSTEM SET recyclebin = ON SCOPE=SPFILE;

 

Shutdown immediate

startup

 

drop table techno_user.test_table;

Check if table exists in recyclebin after drop 

col owner for a20

col owner for a15

col object_name for a30

col ORIGINAL_NAME for a10

set line 120

SELECT owner, object_name, original_name, type, droptime

FROM dba_recyclebin WHERE original_name = 'TEST_TABLE' and owner='TECHNO_USER' ;

 

OWNER           OBJECT_NAME                    ORIGINAL_N TYPE                      DROPTIME

--------------- ------------------------------ ---------- ------------------------- -------------------

TECHNO_USER     BIN$JtPeMal0R/Wwtmwyqoos3g==$0 TEST_TABLE TABLE                     2026-01-11:11:06:16

Check if you want to recover only a row or a table

SELECT * FROM techno_user."BIN$JtPeMal0R/Wwtmwyqoos3g==$0";

Restore it to either original value or you can rename it while restoring 

SQL> FLASHBACK TABLE techno_user.test_table TO BEFORE DROP;

Flashback complete.

SQL> FLASHBACK TABLE techno_user.test_table TO BEFORE DROP RENAME TO TEST_TABLE_BKP ;

Flashback complete.

 

 

Read More

Want to Convert number to text/word form? Here is the solution

Oracle Database has great functionality that converts any number to its word format, suppose number "2 "you want to write it as "two", you can do it using Oracle database. you may not need it for single double, triple, or quadruple numbers but what if your number is big and it makes you lazy to read it or convert it into Word format, Here is the solution.

Make use of a dummy table dual along with a few other functions such as to_char and to_date, Here is the exact query to convert your number 3434590 into word or text format

SQL> select to_char(to_date(3434590,'j'),'jsp') from dual;

TO_CHAR(TO_DATE(3434590,'J'),'JSP')

------------------------------------------------------------

three million four hundred thirty-four thousand five hundred ninety

Where J stands for Jubilian date and which starts from 1 to 5373484 and JSP stands for Julian dates SPelled(SP)

as there is no direct function in oracle to convert number to words, you can try this method. Here is another simple example

Read More

Flashback query in oracle

You must have heard about the time machine in movies or magazines, just think of an oracle providing the same feature with its databases, and that is called a flashback. 

We can go back in time to query data from the table, of course, there are certain limits to it, but it’s possible, we will learn this with an example. 

Here is the table employee, with 8 rows 

 

SQL> select count(*) from employee;

 

  COUNT(*)

----------

         8

and this particular snapshot is captured at the below timings 

 

SQL> select sysdate from dual;

 

SYSDATE

-------------------

2022-07-21 23:37:12

 

Now, deleted a couple of rows using the below query

 

SQL> delete from employee where emp_id>305;

 

2 rows deleted.

 

SQL> commit;

 

Commit complete.

 

now we can see number of rows in the table 

 

SQL> select count(*) from employee;

 

  COUNT(*)

----------

         6

 

so basically, 2 rows are deleted from the table, what if you want to read those 2 deleted rows, you have the option to use the flashback query option, below is the exact format to achieve it

Check the number of rows based on timestamp, as we saw we had 8  rows preset in the table at 23:37 but later we deleted them, based on this timing, check the number of rows

 

SQL> select count(*) from employee as of timestamp TO_DATE('2022-07-21 23:37:12','YYYY-MM-DD HH24:MI:SS');

 

  COUNT(*)

----------

         8

 

see here, we were able to fetch data based on the flashback option, this is just reading, what if you want to insert data back to the table, use the below commands

 

SQL> insert into employee select * from employee as of timestamp TO_DATE('2022-07-21 23:37:12','YYYY-MM-DD HH24:MI:SS') where emp_id>305;

 

2 rows were created.

 

SQL> commit;

 

Commit complete.

 

now check the count in the table, you will be surprised to see, that data is inserted back to the table.

 

SQL> select count(*) from employee;

 

  COUNT(*)

----------

         8

 

Read More

pfile and spfile in oracle database

 In this short article, we will discuss the server parameter file and initialization parameter file i.e., spfile and pfile

 

pfile - parameter file, is a text-based file, readable and editable with any text editor

spfile- server parameter file, binary file, readable with only some utility and can not be edited.

 

pfile is called the initialization parameter file because this is the first file used to start (initialize) the database, spfile is created later on. 

The default extension of these files is ".ora", in an ASM file name can be with any random or fixed number i.e. spfiletechnodb.1223.343543 (technodb is database name)

The following parameters can be seen in the parameter files

 

• memory parameters such as memory target, sga, pga, shared_pool, java pool, large pool
• database and instance name (can be different)
• parameters related to different physical files such as control file, datafiles, archive files
• processes and session details
• trace file location, audit settings, and many more

 

the very first stage of the database startup is "nomount" and that’s where this parameter file will get read, it will load database memory and process settings and takes the location of control files.

 

The default location of the parameter file is based on the operating system you are using

in Linux/Unix it is located at $ORACLE_HOME/dbs location and for windows operation system it is %ORACLE_HOME%\database

 

Parameter change

Assume your database is running on pfile and you want to change the parameter of the database, then you need to perform the below steps

 

• shutdown the database
• add/change the parameter in pfile
• start the database

 

Now assume your database is running on spfile- then based on condition, you may not at all need to restart the database

• change the parameter using alter system command
• Restart the database if required

 

Read More

Rename pluggable database

There might be a situation when you created a pluggable database with the wrong name then it's not time to worry, you can simply rename it using rename command, in another situation where the client requested to change the name of the pluggable database, then rename command will to amazing work. Here we will discuss renaming oracle PDB

 

Below are the pluggable database present in my system I want to rename technopdb3 to technopdb2 

Below are steps to perform it

The first step is to close the pluggable database 

 

SQL> alter pluggable database technopdb3 close immediate;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

         4 TECHNOPDB3                     MOUNTED

         5 TECHNOPDB_NEW                  READ WRITE NO

SQL>

 

pluggable database technopdb3 has been closed, now open same pluggable database in restricted mode 

 

SQL> alter pluggable database technopdb3 open restricted;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                      OPEN MODE  RESTRICTED

---------- ----------------------------- ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

         4 TECHNOPDB3                     READ WRITE YES

         5 TECHNOPDB_NEW                  READ WRITE NO

 

now connect to pluggable database using alter session command and execute rename command as mentioned below 

 

SQL> alter session set container=technopdb3;

 

Session altered.

 

SQL> alter pluggable database technopdb3 rename global_name to technopdb2;

 

Pluggable database altered.

 

 

Now close the pluggable database and open in normal mode

 

SQL> alter pluggable database close;

Pluggable database altered.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> show pdbs

 

    CON_ID CON_NAME                      OPEN MODE  RESTRICTED

---------- ----------------------------- ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

         4 TECHNOPDB2                     READ WRITE NO

         5 TECHNOPDB_NEW                  READ WRITE NO

SQL>

 

In this way, technopdb3 has been renamed to technopdb2

Read More