SQL

What is Recovery Catalog? A recovery catalog is a database schema used by RMAN Which is used to stored Metadata of one ormore databases. Benefits: To store this files from target databases:This recovery catalog contains metadata of Rman operations of each registered target database Database Registration:The process of enrolling the target database with in a … Read more

SELECT NAME ,OPEN_MODE FROM V$DATABASE;SELECT LOG_MODE FROM V$DATABASE;SELECT NAME FROM V$DATAFILE;SELECT NAME FROM V$TABLESPACE; CREATE TABLESPACE(TB1) RMAN > BACKUP DATABASE PLUS ARCHIVELOG; LIST BACKUP OF CONTROLFILE; LIST BACKUP OF TABLESPCAE TB1; DROP TABLESPACE TB1 INCLUDING CONTENTS AND DATAFILES; SELECT NAME FROM V$DATAFILE; SELECT NAME FROM V$TABLESPACE; LIST BACKUP OF TABLESPCAE TB1; =======> Not present in … Read more

The ROW_NUMBER built-in SQL function provides superior support for ordering the results of a query. ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the … Read more

For each row in the database, the ROWID pseudocolumn returns the address of the row. Every table in an Oracle database has a pseudocolumn named ROWID. Oracle Database rowid values contain information necessary to locate a row: • The data object number of the object • The data block in the data file in which … Read more

SQL Macros You can create SQL macros (SQM) to factor out common SQL expressions and statements into reusable, parameterized constructs that can be used in other SQL statements. Starting with Oracle Database release 19c, version 19.7, SQL table macros are supported. SQL table macros are expressions, typically used in a FROM clause, to act as … Read more

The Oracle COALESCE() function accepts a list of arguments and returns the first one that evaluates to a non-null value. In this syntax, the COALESCE() function returns the first non-null expression in the list. It requires at least two expressions. In case all expressions evaluate to null, the function returns null. The following example returns one because it is … Read more

Overview of Triggers A database trigger is a compiled stored program unit, written in either PL/SQL or Java, that Oracle Database invokes (“fires”) automatically in certain situations. A trigger fires whenever one of the following operations occurs: 1. DML statements on a particular table or view, issued by any user DML statements modify data in … Read more