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

Oracle : Database Links - Create, Use and Drop

A database link is an important schema object in the oracle database, we should know details about it in order to implement it in real-time applications,

Assume you have two different applications with two different databases, and while generating reports, you need to fetch and merge data from both the database, it’s a good idea to use a database link, i.e. connecting 2 databases together

We will discuss more

Generally, there are 2 types of database links,

• Public 
• Private 

Public - database link, which can be accessed by all users in the databases, 

Private- database link, which can be accessed by only specific users

 The syntax for creating database links is quite similar 

 create [Public/private] database link <link name>

 Explanation with example 

 Assume below details 

	Database	Schema	Connection link
Source details	technodb	tech_owner	technodb
Target details	asgard	tech_as_owner	asgard

 We will be creating database link asgard_link in database technodb, connecting to the asgard database, and will try to fetch data from a remote database

Create a private database link

Connect to the database using userid tech_owner and execute create database link command, before executing the command remember that, you need to have to create database link privileges assigned.

SQL> select grantee, privilege from dba_sys_privs where grantee='TECH_OWNER';

 

GRANTEE                        PRIVILEGE

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

TECH_OWNER                     CREATE DATABASE LINK

You can see, tech_owner has create database link privileges

Now create a private database link 

SQL> show user

USER is "TECH_OWNER"

SQL> create database link asgard_link

  2  connect to tech_as_owner identified by User#123

  3  using 'asgard';

 

Database link created.

 

Here,

asgard_link – is the database link name

tech_as_owner is a remote user and its password

‘asgard’ – connection string to connect remote database 

Verifying if the database link is working

Execute select query to fetch data from remote database using db link, if returns value, it working fine or in case of error, need to rectify

 SQL> show user

USER is "TECH_OWNER"

SQL> select sysdate from dual@asgard_link;

SYSDATE

---------

29-AUG-22

 

Create a public database link

Similarly, we can create a public database link using the below statements, make sure you have create public database link  privilege granted

SQL> grant create public database link to tech_owner; 

Grant succeeded.

SQL> show user

USER is "TECH_OWNER" 

SQL> create public database link asgard_link_pub

  2  connect to tech_as_owner identified by User#123

  3  using 'asgard'; 

Database link created.

 Now try accessing it through different accounts, you will be able to fetch the data,

SQL> show user

USER is "TECH_OWNER"

SQL>  select sysdate from dual@ASGARD_LINK_PUB ; 

SYSDATE

---------

29-AUG-22

SQL> conn /as sysdba

Connected.

SQL>  select sysdate from dual@ASGARD_LINK_PUB ; 

SYSDATE

---------

29-AUG-22 

How to drop database link

to drop the private database link, you need to connect using a username account and then execute the drop command,

SQL> show user

USER is "TECH_OWNER"

SQL> drop database link ASGARD_LINK; 

Database link dropped.

 To drop the public database link, you need to mention the public identifier, like below

 SQL> drop public database link ASGARD_LINK_PUB ; 

Database link dropped.

 

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

ORA-39161: Full database jobs require privileges

I was trying to take full export backup of the database using user from the pluggable database, the export command threw below error

 

Users\shaik>expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

 

Export: Release 19.0.0.0.0 - Production on Tue Jun 14 20:42:47 2022

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Password:

 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

ORA-31631: privileges are required

ORA-39161: Full database jobs require privileges

 

I realized that SYS, SYSTEM does not need any explicit privileges to be granted while performing export and import, but while using any other user, we need to grant proper privileges to take full database export.

 

SQL> alter session set container=technopdb;

 

Session altered.

 

SQL> grant DATAPUMP_EXP_FULL_DATABASE to techno_user;

 

Grant succeeded.

 

Here I granted DATAPUMP_EXP_FULL_DATABASE privileges to user and tried taking export and it worked

 

C:\Users\shaik>expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

 

Export: Release 19.0.0.0.0 - Production on Tue Jun 14 20:44:50 2022

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Password:

 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "TECHNO_USER"."SYS_EXPORT_FULL_01":  userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

 

 

Here we need to note that, taking export and performing import requires special privileges

Read More