Thursday, November 20, 2014

SQL Scripts for Oracle Database Link

In posts Get the source code for Oracle database views and materialized view and Get source code for Oracle database objects: user_source view, we show how to get the SQL scripts that create some database objects.

To get the SQL scripts for existing database links, we can take advantage of dbms_metadata.get_ddl().The following query returns the database links created.

SQL> select  owner, db_link from all_db_links order by owner, db_link;

OWNER                          DB_LINK
------------------------------ ----------------
PROD                           DL_DATA
PROD                           DL_EC1
To get the original SQL scripts that create these database links, we use dbms_metadata.get_ddl() as shown below.
SQL> select dbms_metadata.get_ddl('DB_LINK', 'DL_EC1') from dual;

DBMS_METADATA.GET_DDL('DB_LINK','DL_EC1')
--------------------------------------------------------------------------------

  CREATE DATABASE LINK "DL_EC1"    USING '(description=(address=(protocol=TCP)
(host=ec2-xx-xx-xxx-178.compute-1.amazonaws.com)(port=1521))
(connect_data=(sid=XE)))'

Tuesday, November 18, 2014

Drop User in Oracle

Post Drop All Objects In an Oracle Schema uses PL/SQL to iteratively drop database objects. We can simply drop a user and its database objects in Oracle using "drop user.. cascade". This provides an easy way to clean up the database.

We have to drop a user by connecting to another user. If we try to drop a user that we are currently connecting to, we will get error ORA-01940. The Sqlplus session is connected to user1.

SQL> drop user user1 cascade;
drop user user1 cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
The user that we are connecting to needs to have "drop user" privilege. In the following example, the Sqlplus session is connected to user2.
SQL> drop user user1 cascade;
rop user user1 cascade

RROR at line 1:
RA-01031: insufficient privileges
We connected to sys and grant "drop user" privilege to user2.
SQL> grant drop user to user2;

Grant succeeded.
Now we connect to user2 and are able to drop user1
SQL> drop user user1 cascade;

User dropped.