Skip to content
Home » Oracle » How to Resolve ORA-01729: database link name expected

How to Resolve ORA-01729: database link name expected

  • Oracle

ORA-01729

Tried to create a database link wich is able to connect to a remote database, but it failed with ORA-01729.

Reserved Word

SQL> create database link access connect to hr identified by hr using 'ORCLPDB';
create database link access connect to hr identified by hr using 'ORCLPDB'
                     *
ERROR at line 1:
ORA-01729: database link name expected

Begin with Digit

SQL> create database link 123 connect to hr identified by hr using 'ORCLPDB';
create database link 123 connect to hr identified by hr using 'ORCLPDB'
                     *
ERROR at line 1:
ORA-01729: database link name expected

Public Database Link

SQL> create database link public.boston connect to hr identified by hr using 'ORCLPDB';
create database link public.boston connect to hr identified by hr using 'ORCLPDB'
                     *
ERROR at line 1:
ORA-01729: database link name expected

ORA-01729 means that the identifier you chose cannot be used for the db link name, which is invalid and unqualified for an object name in Oracle database.

Which means, don't use any reserved words, don't use names begin with a digit either.

Solution

To have a qualified object name, you need to conform to the Oracle Database Object Names and Qualifiers.

SQL> create database link access123 connect to hr identified by hr using 'ORCLPDB';

Database link created.

Public Database Link

To create a legal public database link, don't treat PUBLIC as a schema, you need to use CREATE PUBLIC DATABASE LINK statement to do it.

SQL> create public database link boston connect to hr identified by hr using 'ORCLPDB';

Database link created.

If you really want to name an object by a keyword or beginning with a digit, you should use double quotations to enclose its name. To better understanding how to properly use a quoted identifier (exact form), we have some examples for you.

For more error patterns about invalid identifier, you may refer to the post: How to Resolve ORA-00903: invalid table name.

Leave a Reply

Your email address will not be published. Required fields are marked *