However, if the user is additionally employed by someone, then potentially the password of the user may change, which causes the database link broken.

When a password of the user of a database link changes, you would like to use the ALTER DATABASE LINK statement to update the database link.

The following statement updates the new password for the remote user of a personal database link:

ALTER DATABASE LINK private_dblink

CONNECT TO remote_user IDENTIFIED BY new_password;

And the following statement updates the new password of the remote user of a public database link:

ALTER PUBLIC DATABASE LINK public_dblink

CONNECT TO remote_user IDENTIFIED BY new_password;

To execute the ALTER DATABASE LINK and ALTER PUBLIC DATABASE LINK statements, your account must have the ALTER DATABASE LINK SYSTEM and ALTER PUBLIC DATABASE LINK system privilege respectively.

Oracle ALTER DATABASE LINK statement example

This example uses the ALTER DATABASE LINK statement to update the password for the user bob of the sales database link:

ALTER DATABASE LINK sales

CONNECT TO bob IDENTIFIED BY xyz@123!;

Oracle DROP DATABASE LINK

Introduction to the Oracle DROP DATABASE LINK statement

The DROP DATABASE LINK statement allows you to drop a database link from the database. Here is that the syntax of the DROP DATABASE LINK statement:

DROP DATABASE LINK dblink;

In this syntax, you specify the name of the database link ( dblink) that you simply want to get rid of after the DROP DATABASE LINK keywords.

The DROP DATABASE LINK statement can remove a personal database link. to get rid of a public database link, you add the general public keyword as shown within the following statement:

DROP PUBLIC DATABASE LINK dblink;

Note that to drop a public database link, you would like to possess the DROP PUBLIC DATABASE DATABASE LINK system privilege.

Restrictions of the DROP DATABASE LINK statement:

First, you can’t drop a database link during a schema of another user.
Second, you can’t qualify the dblink with the name of a schema like: schema.dblink because Oracle will interpret schema.dblink because the entire name of a database link in your own schema. Notice that the name of a database link can contain periods (.)

Oracle DROP DATABASE LINK statement example

This example removes the SALES private database link created within the CREATE DATABASE LINK tutorial:

DROP DATABASE LINK sales;

Useful Tips

Oracle Show Tables

If you’ve got worked with MySQL, you’ll be conversant in the SHOW TABLES command that lists all tables during a database:

SHOW TABLES;

Unfortunately, Oracle doesn’t directly support the SHOW TABLES command. However, you’ll list all tables during a database by querying from various data dictionary views.

Show tables owned by the present user

To show tables owned by the present user, you query from the user_tables view.

SELECT table_name

FROM user_tables

ORDER BY table_name;

Note that this view doesn’t show the OWNER column. Also, the user_tables table doesn’t contain the opposite tables that are accessible by the present user.

Show tables that are accessible by the present user

To show all tables that are currently accessible by the present user, no matter owners, you query from the all_tables view:

SELECT table_name

FROM all_tables

ORDER BY table_name;

If you would like to point out all tables of a selected owner, you add the OWNER column within the WHERE clause as shown within the following query:

SELECT *

FROM all_tables

WHERE OWNER = 'OT'

ORDER BY table_name;

Show all tables within the Oracle Database

To show all tables within the entire Oracle Database, you query from the dba_tables deem follows:

SELECT table_name

FROM dba_tables;

You will get the subsequent error message if you don’t have access to the dba_tables view:

ORA-00942: table or view doesn’t exist

In this case, you ought to request your database administrator to grant your account either privileges on the dba_tables view, or SELECT ANY DICTIONARY privilege, or SELECT_CATALOG_ROLE privilege.

The following picture illustrates the tables which will be returned from the user_tables, all_tables, and dba_tables views:

So, this brings us to the end of blog. This Tecklearn ‘How to Alter and Drop Oracle Database Link’ blog helps you with commonly asked questions if you are looking out for a job in Oracle DB. If you wish to learn Oracle DB and build a career in Database domain, then check out our interactive, Oracle 12c DBA Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/oracle-12c-dba-course/

Oracle 12c DBA Course Training

About the Course

Oracle Database Administration training will help you master the Oracle DBA. As part of the training, you will master the Oracle Database architecture, the database processes, memory structures, schema objects, security, data recovery and backup through hands-on projects and case studies. Oracle DBA Training helps students gain the skills and knowledge required to install, configure, and administer Oracle databases. Besides, this training also develops the full skill set required to become a successful Oracle Database Administrator.

Why Should you take Oracle DBA Training?

The Average salary of an Oracle DBA is $108,837 per annum – GlassDoor.com
According to Gartner, Oracle is the market leader in the Relational Database with 48.35% market share globally.
IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle Database

What is the Oracle Database
Pre-requisites for oracle database
Oracle database 12c installation
Multi-tenant architecture

Exploring the Oracle 12c Database Architecture

Explain the Memory Structures
Describe the Process Structures
Overview of Storage Structures

Preparing the Database Environment

Identify the tools for Administering an Oracle Database
Plan an Oracle Database Installation
Install the Oracle Software by using Oracle Universal Installer (OUI)
Create a Database by using the Database Configuration Assistant (DBCA)

Managing the Oracle Instance

Setting database initialization parameters
Describe the stages of database startup and shutdown

Database Storage Structures and Schema Objects

Managing Database Storage Structures
Overview of table space and data files
Overview of Primary Components
Identify the importance of checkpoints, redo log files, and archived log files
Configure ARHIVELOG mode

Database Startup & User Requests and Oracle Network Environment

Describe the stages of database startup and shutdown
Using alert log and trace files
Configure and Manage the Oracle Network
pFile and spFile
Tablespace Management
Storage and Relationship Structure

Oracle Backup & Recovery

Redo log File Management
Performing Database Recovery
Overview of Data Recovery Advisor
Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)

Deep Dive into Oracle Recovery Manager (RMAN)

Using the RMAN Recovery Catalog
Identify situations that require RMAN recovery catalog
Create and configure a recovery catalog
Synchronize the recovery catalog
Create and Use RMAN stored scripts
Back up the recovery catalog
Create and use a virtual private catalog

Configuring Backup Specifications

Using RMAN to Create Backups
Create image file backups
Create a whole database backup
Enable fast incremental backup
Using RMAN to Perform Recovery
Perform complete recovery from a critical or noncritical data file loss using RMAN
Perform incomplete recovery using RMAN

Data Movement and Oracle Data Pump

Moving Data
Describe and use methods to use move data (Directory objects, SQL* Loader, External tables)
Explain the general architecture of Oracle Data Pump

Data Dictionary & Dynamic Performance Tables

Oracle Data Dictionary contains information related to database privileges
Data dictionary views
Media failure
Flashback CDB

Database Tuning

What is database tuning
How to tune the database
Tuning the data dictionary

397