Oracle DB

Here is an SQL, which demonstrates the use of Row_Number() function:with places_table as (select 'Africa' continent, 'Nigeria' country, 'Lagos' city from dualunionselect 'Africa' continent, 'Nigeria' country, 'Abuja' city from dualunionselect…

Here are 2 examples of how to delete or replace xml part:    Supposing that we have a variable l_xmltype of type XMLTYPE containing an XML:   --delete XML;select xmltype(deletexml(xmltype('<s:a xmlns:s="qw"><s:b><return></return></s:b></s:a>'),…

Here is how to program a scheduled job in Oracle DB to run at specific date and time:The following job will run at 17:00 every day:SET DEFINE OFF;DECLARE  X NUMBER;BEGIN …

Here is how to query the dependencies of an object in Oracle DB:(in this example, which tables a package specification is depended on)select  /*+ NO_PARALLEL */ * from (SELECT DISTINCTb.object_name…

Find out which objects are locked using the following query:   select   substr(a.os_user_name,1,15) "OS User",   substr(a.oracle_username,1,8) "DB User",   substr(b.owner,1,8) "Schema",   substr(b.object_name,1,20) "Object Name",   substr(b.object_type,1,10) "Type",   substr(c.segment_name,1,15) "RBS",   substr(d.used_urec,1,12) "# of Records",  …

You can find the number of uncommited transactions using the following query:SELECT ROWS_PROCESSED,sql_textFROM v$sqlareaWHERE (address, hash_value) IN(SELECT sql_address, sql_hash_valueFROM v$sessionWHERE sid in (select sid from v$session where schemaname='VAG'))or using v$transaction…

Some sample commands for Add/Drop/Rename objects:--Dropping the primary key:ALTER TABLE "VAG"."TEST_TABLE" drop primary key;drop index "VAG"."TEST_TABLE_PK";--Adding primary key:CREATE UNIQUE INDEX "VAG"."TEST_TABLE_PK" ON "VAG"."TEST_TABLE" ("TEST_TABLE_ID");ALTER TABLE "VAG"."TEST_TABLE" ADD CONSTRAINT "TEST_TABLE_PK" PRIMARY…

For exporting the DDL of data objects without the storage specifications, this is what we should do:EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);select DBMS_METADATA.GET_DDL('TABLE','PROJECTS') from DUAL;EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');

This is how to get the size of a BLOB in SQL query:select file_id, doc_xml, dbms_lob.getlength(doc_xml) from my_files order by dbms_lob.getlength(doc_xml) desc