SELECT table_name, constraint_name, cname1 || Nvl2(cname2,’,’ ||cname2,NULL) ||Nvl2(cname3,’,’ ||cname3,NULL) || Nvl2(cname4,’,’ ||cname4,NULL) ||Nvl2(cname5,’,’ ||cname5,NULL) || Nvl2(cname6,’,’ ||cname6,NULL) ||Nvl2(cname7,’,’ ||cname7,NULL) || Nvl2(cname8,’,’ ||cname8,NULL) columns from ( SELECT b.table_name, b.constraint_name, max(decode( position,…
Read MoreRenaming a pluggable database After renaming the pdb we need to move the datafiles to new directory if needed.
Read MoreMasking a column using dbms_redact To mask a sensitive data from any column using dbms_redact package. Here profile column in redactt table is masked. [oracle@tom ~]$ !sq sqlplus /…
Read MoreThe sys and dba privileged schemas can view the details of columns masked using DBMS_REDACT .
Read MoreImporting excel data to oracle table using sqldeveloper Choose the table and right click Uncheck Header Select columns Choose match by position
Read MoreTo find the job details set pages 1000 lines 1000 col schema_user for a20 col this_date for a20 col next_date for a20 col what for a80 col instance for 999999999…
Read MoreDownload sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip set db environment cd /home/oracle unzip sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip cd sqlt/install sqlplus / as sysdba sql> start sqlcreate.sql Enter password for SQLTXPLAIN Enter Tablespace name Enter Temp tablespace name…
Read Moreset lines 1000 pages 1000 col RBS format a20 col sid format 9999 col user format a28 col status format a12 SELECT r.name “RBS”, s.sid, s.serial#, s.username “USER”, t.status, t.cr_get,…
Read MoreTO FIND THE CURRENT SCN ======================== select current_scn from v$database; TO FIND THE SCN FROM TIMESTAMP =============================== select timestamp_to_scn(to_timestamp(’05-09-2018 12:46:21′,’dd-mm-yyyy hh24:mi:ss’)) scn from dual; TO FIND THE TIMESTAMP FROM SCN…
Read Moreset pages 1000 lines 1000 col column_name for a40 col index_name for a45 col column_expression for a40 set lines 120 pages 50000 select c.index_name, c.column_name “COLUMN_NAME”, c.column_position, a.column_expression from dba_ind_columns…
Read More