Oracle Structure Objects

Redefine a Table Online with DBMS_REDEFINITION, No DML Blocking Purpose A DBA moving a table to a new tablespace, adding compression, or rebuilding a badly fragmented segment has one hard requirement: users can't lose access to that table while the change is happening. For decades the available tools — CREATE TABLE AS …

Oracle PL/SQL: SHOW ERRORS and dba_errors Explained Purpose DBA_ERRORS is the data dictionary view that holds the current compilation errors for every stored object in the database. It is the first place to look when a procedure, function, package or trigger is created but does not work. The view describes the current …

Uncovering Table Storage Usage in Oracle Database: A Guide with dba_segments For Oracle Database administrators (DBAs) and developers, managing storage efficiently is critical. Keeping track of table sizes helps identify potential space hogs and optimize resource allocation. This guide delves into a powerful SQL query …

Oracle DBMS_METADATA.GET_DDL: Extract Object DDL Purpose Oracle 9i introduced the DBMS_METADATA package. It can retrieve object definitions as DDL or as XML. The function GET_DDL turns that ability into a single query. You give it an object type, an object name and a schema. It returns the creation statement as a CLOB. …

Find Tables Containing a Specific Column in Oracle Database Purpose Which tables in this database have a column called CUSTOMER_ID? Sooner or later every DBA or developer needs the answer. The DBA_TAB_COLUMNS view lists the columns of every table, view, and cluster in the database. One query against it returns each …

Unveiling the Mystery: Demystifying an Oracle SQL Code Snippet for Object Size Analysis In the ever-evolving realm of database management, efficient resource utilization is paramount. Oracle, a widely recognized database management system, offers powerful tools to help optimize storage and performance. This blog post …

Unveiling Your Database Toolkit: Exploring Objects Owned by a User in Oracle In the intricate world of database management, understanding the landscape of objects is crucial for efficient operation. This blog post empowers you to leverage a specific Oracle Database SQL code snippet to list all objects owned by a …

Unveiling the Mystery: Demystifying an Oracle Database Procedure In the realm of relational databases, procedures serve as powerful tools for encapsulating complex sets of instructions and improving code reusability. They act as building blocks, streamlining development and maintenance. Today, we'll delve into the …

How to Search for Objects in Oracle Database This post explains how to search for objects (tables, views, sequences, etc.) in an Oracle database using SQL. Sample SQL Command 1set pages 999 2col owner format a15 3col object_name format a40 4col object_type format a20 5select owner 6, object_name 7, object_type 8from …

How to Identify and Fix Invalid Objects in an Oracle Database Purpose A list of invalid objects tells you more than it first seems to. Ten invalid objects in one application schema right after a release can point to one changed table. Hundreds of invalid objects across many schemas more often follow a patch or an …