Kaley's (mostly) Oracle Database Blog
Hi! I'm Kaley Crum, Oracle DBA.
Welcome to my (mostly) Oracle database blog.

Archive: Mar 2016

Oracle Drop If Exists Procedure

One of the things I’ve always wanted in Oracle was drop if exists functionality.  Inevitably, I’ll be creating a script that creates a bunch of tables and, for one reason or another, I’ll need to re-run the script because a data type in the table has changed or the length of a field needs to be tweaked, so I start dropping tables and I’ll miss one, or I’ll miss and index, and as a result the script dies halfway through with a ORA-00955 name is already used by an existing object error.

The solution, I’ve determined, was to roll my own drop_if_exists() procedure.

Continue Reading

See All Tables Foreign-Keyed to a Parent Table in Oracle

The below SQL query will find all children tables of any specified parent table.  If you do not have access to DBA_CONSTRAINTS/DBA_CONS_COLUMNS data dictionary view, use ALL_CONSTRAINTS/ALL_CONS_COLUMNS instead.  Or ask your DBA very nicely for the SELECT ANY DICTIONARY system privilege (or SELECT_CATALOG_ROLE).

 

Creating a Database Link in Another User’s Schema

Awhile back, I was tasked with creating a database link in another user’s schema, when I wasn’t able to login as the user.  I searched the internet–the majority of articles said that it couldn’t be done, and they encouraged people to create public database links instead of private database links.

Well, I found a working method–here it is.  Just replace INSERT_USERNAME  with your intended target schema.