Posts Tagged ‘oracle’

SQL Formatter – Using Java/PL/SQL by Sayan Malakshinov

Posted by Sriram Sanka on September 17, 2022

It is very easy to Format SQL using Third Party tools, Recently I found one source to get the SQL Formatted from the Sql prompt without connecting to any Third Party GUI Tools.

I prefer to use SQLPLUS more than GUI tools., You can see the code formatted beautifully and very readable.

Step 1: Load oracle.dbtools-common.jar into the Database.

Microsoft Windows [Version 10.0.19044.2006]
(c) Microsoft Corporation. All rights reserved.

C:\Users\Dell>loadjava -u sriram/zaqmlp123 C:\app\Dell\product\21c\dbhomeXE\jlib\oracle.dbtools-common.jar

Step 2: Grant required java Privileges to the User and Create Java and Write a wrapper Package using the java

C:\Users\Dell>sqlplus /nolog

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Sep 17 16:52:53 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

SQL> conn sriram@xepdb1
Enter password:
Connected.
SQL>
SQL> exec dbms_java.grant_permission( 'SRIRAM', 'SYS:java.lang.RuntimePermission', 'oracle.DbmsJavaScriptUser', '' );

PL/SQL procedure successfully completed.

SQL> exec dbms_java.grant_permission( 'SRIRAM', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.jdk.nashorn.internal.runtime', '' );

PL/SQL procedure successfully completed.

SQL> exec dbms_java.grant_permission( 'SRIRAM', 'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' );

PL/SQL procedure successfully completed.

SQL> select
  2
SQL> ed
Wrote file afiedt.buf

  1  CREATE or replace AND COMPILE JAVA SOURCE NAMED SQLFormatter AS
  2  import oracle.dbtools.app.Format;
  3  import java.sql.Connection;
  4  import java.sql.DriverManager;
  5  import java.sql.SQLException;
  6  import oracle.sql.BLOB;
  7  import oracle.sql.CLOB;
  8  import java.io.StringWriter;
  9  import java.io.PrintWriter;
 10  public class SQLFormatter {
 11      private static String getStackTrace(Exception e) {
 12         StringWriter writer = new StringWriter();
 13         PrintWriter printWriter = new PrintWriter( writer );
 14         e.printStackTrace( printWriter );
 15         printWriter.flush();
 16         return writer.toString();
 17      }
 18      public static Format getFormat() {
 19          oracle.dbtools.app.Format format = new oracle.dbtools.app.Format();
 20          format.options.put("singleLineComments", Format.InlineComments.CommentsUnchanged);
 21          format.options.put("kwCase", Format.Case.UPPER);
 22          format.options.put("idCase", Format.Case.NoCaseChange);
 23          format.options.put("adjustCaseOnly", false);
 24          format.options.put("formatThreshold", 1);
 25          format.options.put("alignTabColAliases", false);
 26          format.options.put("alignTypeDecl", true);
 27          format.options.put("alignNamedArgs", true);
 28          format.options.put("alignEquality", false);
 29          format.options.put("alignAssignments", true);
 30          format.options.put("alignRight", false);
 31          format.options.put("identSpaces", 3);
 32          format.options.put("useTab", false);
 33          format.options.put("breaksComma", Format.Breaks.Before);
 34          format.options.put("breaksProcArgs", false);
 35          format.options.put("breaksConcat", Format.Breaks.Before);
 36          format.options.put("breaksAroundLogicalConjunctions", Format.Breaks.Before);
 37          format.options.put("breaksAfterSelect", true);
 38          format.options.put("commasPerLine", 1);
 39          format.options.put("breakOnSubqueries", true);
 40          format.options.put("breakAnsiiJoin", true);
 41          format.options.put("breakParenCondition", true);
 42          format.options.put("maxCharLineSize", 120);
 43          format.options.put("forceLinebreaksBeforeComment", false);
 44          format.options.put("extraLinesAfterSignificantStatements", Format.BreaksX2.Keep);
 45          //format.options.put("flowControl", Format.FlowControl.IndentedActions);
 46          format.options.put("spaceAroundOperators", true);
 47          format.options.put("spaceAfterCommas", true);
 48          //format.options.put("spaceAroundBrackets", Format.Space.Default);
 49          return format;
 50      }
 51    public static String format(String str)
 52    {
 53      String res;
 54      try {
 55         Format f = SQLFormatter.getFormat();
 56         res = f.format(str);
 57         }
 58      catch (Exception e){
 59         res = "Error: " + e.getMessage() + " [ " + SQLFormatter.getStackTrace(e) + " ]";
 60      }
 61      return res;
 62    }
 63    public static CLOB formatClob(oracle.sql.CLOB clob)
 64    throws SQLException
 65    {
 66      String str = clob.getSubString(1, (int) clob.length());
 67      String res = SQLFormatter.format(str);
 68      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 69      CLOB resClob = CLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);
 70      resClob.setString(1L, res);
 71      return resClob;
 72    }
 73* }
 74  /

Java created.

SQL> select
  2
SQL> ed
Wrote file afiedt.buf

  1  create or replace package SQLFormatter as
  2    FUNCTION Format(str in varchar2) RETURN VARCHAR2
  3    AS LANGUAGE JAVA NAME 'SQLFormatter.format(java.lang.String) return java.lang.String';
  4    FUNCTION FormatClob(str in clob) RETURN CLOB
  5    AS LANGUAGE JAVA NAME 'SQLFormatter.formatClob(oracle.sql.CLOB) return oracle.sql.CLOB';
  6* end;
  7  /

Package created.

SQL> desc SQLFormatter
FUNCTION FORMAT RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 STR                            VARCHAR2                IN
FUNCTION FORMATCLOB RETURNS CLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 STR                            CLOB                    IN

SQL> select  sql_id||' - '||sql_text as "UnClear" from v$sqlarea where sql_id='gngtvs38t0060'
  2  union
  3  select sql_id||' - '||SQLFormatter.format(sql_text) "Readable" from v$sqlarea where sql_id='gngtvs38t0060'
  4  /

UnClear
------------------------------------------------------------------------------------------------------------------------
gngtvs38t0060 - SELECT /*+ CONNECT_BY_FILTERING */ s.privilege# FROM sys.sysauth$ s        CONNECT BY s.grantee# = PRIOR
 s.privilege#                                 AND (s.privilege# > 0 OR s.privilege# = -352)
 START WITH (s.privilege# > 0 OR s.privilege# = -352) AND s.grantee# IN     (SELECT c1.privilege# FROM sys.codeauth$ c1
WHERE c1.obj# = :1)            UNION                                                                      SELECT c2.priv
ilege# FROM sys.codeauth$ c2 WHERE c2.obj# = :2              ORDER BY 1 ASC









gngtvs38t0060 - SELECT /*+ CONNECT_BY_FILTERING */
   s.privilege#
FROM
   sys.sysauth$ s
CONNECT BY
   s.grantee# = PRIOR s.privilege#
AND (
         s.privilege# > 0
      OR s.privilege# =-352
   )
START WITH
   (
         s.privilege# > 0
      OR s.privilege# =-352
   )
AND s.grantee# IN (
      SELECT
         c1.privilege#
      FROM
         sys.codeauth$ c1
      WHERE
         c1.obj# =:1
   )
UNION
SELECT
   c2.privilege#
FROM
   sys.codeauth$ c2
WHERE
   c2.obj# =:2
ORDER BY 1 ASC


2 rows selected.

Refer http://orasql.org/2020/12/23/format-sql-or-pl-sql-directly-in-oracle-database/ for more info.

https://github.com/xtender/xt_scripts/tree/master/extra/SQLFormatter

Hope this is useful for you as well 🙂

Posted in formatter, sql | Tagged: formatter, java, oracle, plsql, sql | Leave a Comment »

Query for Top 10 by Executions:

Posted by Sriram Sanka on September 13, 2022

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        executions, rows_processed, rows_processed/executions "Rows/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE executions > 100
 ORDER BY executions DESC)
WHERE rownum  <=10
;

Posted in Performance, sql | Tagged: oracle, sql | Leave a Comment »

Gather the Header Information and the Content of an Export Dumpfile

Posted by Sriram Sanka on September 12, 2022

Oracle has provided a Beautiful script to check the Header Information of Oracle Export Dumpfile.

Refer How to Gather the Header Information and the Content of an Export Dumpfile ? (Doc ID 462488.1) for More Info.

CREATE OR REPLACE PROCEDURE show_dumpfile_info(
  p_dir  VARCHAR2 DEFAULT 'DATA_PUMP_DIR',
  p_file VARCHAR2 DEFAULT 'EXPDAT.DMP')
AS
-- p_dir        = directory object where dump file can be found
-- p_file       = simple filename of export dump file (case-sensitive)
  v_separator   VARCHAR2(80) := '--------------------------------------' ||
                                '--------------------------------------';
  v_path        all_directories.directory_path%type := '?';
  v_filetype    NUMBER;                 -- 0=unknown 1=expdp 2=exp 3=ext
  v_fileversion VARCHAR2(15);           -- 0.1=10gR1 1.1=10gR2 (etc.)
  v_info_table  sys.ku$_dumpfile_info;  -- PL/SQL table with file info
  type valtype  IS VARRAY(23) OF VARCHAR2(2048);
  var_values    valtype := valtype();
  no_file_found EXCEPTION;
  PRAGMA        exception_init(no_file_found, -39211);

BEGIN

-- Dump file details:
-- ==================
-- For Oracle10g Release 2 and higher:
--    dbms_datapump.KU$_DFHDR_FILE_VERSION        CONSTANT NUMBER := 1;
--    dbms_datapump.KU$_DFHDR_MASTER_PRESENT      CONSTANT NUMBER := 2;
--    dbms_datapump.KU$_DFHDR_GUID                CONSTANT NUMBER := 3;
--    dbms_datapump.KU$_DFHDR_FILE_NUMBER         CONSTANT NUMBER := 4;
--    dbms_datapump.KU$_DFHDR_CHARSET_ID          CONSTANT NUMBER := 5;
--    dbms_datapump.KU$_DFHDR_CREATION_DATE       CONSTANT NUMBER := 6;
--    dbms_datapump.KU$_DFHDR_FLAGS               CONSTANT NUMBER := 7;
--    dbms_datapump.KU$_DFHDR_JOB_NAME            CONSTANT NUMBER := 8;
--    dbms_datapump.KU$_DFHDR_PLATFORM            CONSTANT NUMBER := 9;
--    dbms_datapump.KU$_DFHDR_INSTANCE            CONSTANT NUMBER := 10;
--    dbms_datapump.KU$_DFHDR_LANGUAGE            CONSTANT NUMBER := 11;
--    dbms_datapump.KU$_DFHDR_BLOCKSIZE           CONSTANT NUMBER := 12;
--    dbms_datapump.KU$_DFHDR_DIRPATH             CONSTANT NUMBER := 13;
--    dbms_datapump.KU$_DFHDR_METADATA_COMPRESSED CONSTANT NUMBER := 14;
--    dbms_datapump.KU$_DFHDR_DB_VERSION          CONSTANT NUMBER := 15;
-- For Oracle11gR1:
--    dbms_datapump.KU$_DFHDR_MASTER_PIECE_COUNT  CONSTANT NUMBER := 16;
--    dbms_datapump.KU$_DFHDR_MASTER_PIECE_NUMBER CONSTANT NUMBER := 17;
--    dbms_datapump.KU$_DFHDR_DATA_COMPRESSED     CONSTANT NUMBER := 18;
--    dbms_datapump.KU$_DFHDR_METADATA_ENCRYPTED  CONSTANT NUMBER := 19;
--    dbms_datapump.KU$_DFHDR_DATA_ENCRYPTED      CONSTANT NUMBER := 20;
-- For Oracle11gR2:
--    dbms_datapump.KU$_DFHDR_COLUMNS_ENCRYPTED   CONSTANT NUMBER := 21;
--    dbms_datapump.KU$_DFHDR_ENCRIPTION_MODE     CONSTANT NUMBER := 22;
-- For Oracle12cR1:
--    dbms_datapump.KU$_DFHDR_COMPRESSION_ALG     CONSTANT NUMBER := 23;

-- For Oracle10gR2: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 15;
-- For Oracle11gR1: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 20;
-- For Oracle11gR2: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 22;
-- For Oracle12cR1: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 23;

-- Show header output info:
-- ========================

  dbms_output.put_line(v_separator);
  dbms_output.put_line('Purpose..: Obtain details about export ' ||
        'dumpfile.        Version: 18-DEC-2013');
  dbms_output.put_line('Required.: RDBMS version: 10.2.0.1.0 or higher');
  dbms_output.put_line('.          ' ||
        'Export dumpfile version: 7.3.4.0.0 or higher');
  dbms_output.put_line('.          ' ||
        'Export Data Pump dumpfile version: 10.1.0.1.0 or higher');
  dbms_output.put_line('Usage....: ' ||
        'execute show_dumfile_info(''DIRECTORY'', ''DUMPFILE'');');
  dbms_output.put_line('Example..: ' ||
        'exec show_dumfile_info(''MY_DIR'', ''expdp_s.dmp'')');
  dbms_output.put_line(v_separator);
  dbms_output.put_line('Filename.: ' || p_file);
  dbms_output.put_line('Directory: ' || p_dir);

-- Retrieve Export dumpfile details:
-- =================================

  SELECT directory_path INTO v_path FROM all_directories
   WHERE directory_name = p_dir
      OR directory_name = UPPER(p_dir);

  dbms_datapump.get_dumpfile_info(
           filename   => p_file,       directory => UPPER(p_dir),
           info_table => v_info_table, filetype  => v_filetype);

  var_values.EXTEND(23);
  FOR i in 1 .. 23 LOOP
    BEGIN
      SELECT value INTO var_values(i) FROM TABLE(v_info_table)
       WHERE item_code = i;
    EXCEPTION WHEN OTHERS THEN var_values(i) := '';
    END;
  END LOOP;

  dbms_output.put_line('Disk Path: ' || v_path);

  IF v_filetype >= 1 THEN
    -- Get characterset name:
    BEGIN
      SELECT var_values(5) || ' (' || nls_charset_name(var_values(5)) ||
        ')' INTO var_values(5) FROM dual;
    EXCEPTION WHEN OTHERS THEN null;
    END;
    IF v_filetype = 2 THEN
      dbms_output.put_line(
         'Filetype.: ' || v_filetype || ' (Original Export dumpfile)');
      dbms_output.put_line(v_separator);
      SELECT DECODE(var_values(13), '0', '0 (Conventional Path)',
        '1', '1 (Direct Path)', var_values(13))
        INTO var_values(13) FROM dual;
      dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
      dbms_output.put_line('...Direct Path Export Mode.......: ' || var_values(13));
      dbms_output.put_line('...Export Version................: ' || var_values(15));
    ELSIF v_filetype = 1 OR v_filetype = 3 THEN
      SELECT SUBSTR(var_values(1), 1, 15) INTO v_fileversion FROM dual;
      SELECT DECODE(var_values(1),
                    '0.1', '0.1 (Oracle10g Release 1: 10.1.0.x)',
                    '1.1', '1.1 (Oracle10g Release 2: 10.2.0.x)',
                    '2.1', '2.1 (Oracle11g Release 1: 11.1.0.x)',
                    '3.1', '3.1 (Oracle11g Release 2: 11.2.0.x)',
                    '4.1', '4.1 (Oracle12c Release 1: 12.1.0.x)',
        var_values(1)) INTO var_values(1) FROM dual;
      SELECT DECODE(var_values(2), '0', '0 (No)', '1', '1 (Yes)',
        var_values(2)) INTO var_values(2) FROM dual;
      SELECT DECODE(var_values(14), '0', '0 (No)', '1', '1 (Yes)',
        var_values(14)) INTO var_values(14) FROM dual;
      SELECT DECODE(var_values(18), '0', '0 (No)', '1', '1 (Yes)',
        var_values(18)) INTO var_values(18) FROM dual;
      SELECT DECODE(var_values(19), '0', '0 (No)', '1', '1 (Yes)',
        var_values(19)) INTO var_values(19) FROM dual;
      SELECT DECODE(var_values(20), '0', '0 (No)', '1', '1 (Yes)',
        var_values(20)) INTO var_values(20) FROM dual;
      SELECT DECODE(var_values(21), '0', '0 (No)', '1', '1 (Yes)',
        var_values(21)) INTO var_values(21) FROM dual;
      SELECT DECODE(var_values(22),
                    '1', '1 (Unknown)',
                    '2', '2 (None)',
                    '3', '3 (Password)',
                    '4', '4 (Password and Wallet)',
                    '5', '5 (Wallet)',
        var_values(22)) INTO var_values(22) FROM dual;
      SELECT DECODE(var_values(23),
                    '2', '2 (None)',
                    '3', '3 (Basic)',
                    '4', '4 (Low)',
                    '5', '5 (Medium)',
                    '6', '6 (High)',
        var_values(23)) INTO var_values(23) FROM dual;
      IF v_filetype = 1 THEN
        dbms_output.put_line(
           'Filetype.: ' || v_filetype || ' (Export Data Pump dumpfile)');
        dbms_output.put_line(v_separator);
        dbms_output.put_line('...Database Job Version..........: ' || var_values(15));
        dbms_output.put_line('...Internal Dump File Version....: ' || var_values(1));
        dbms_output.put_line('...Creation Date.................: ' || var_values(6));
        dbms_output.put_line('...File Number (in dump file set): ' || var_values(4));
        dbms_output.put_line('...Master Present in dump file...: ' || var_values(2));
        IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 AND v_fileversion >= '2.1' THEN
          dbms_output.put_line('...Master in how many dump files.: ' || var_values(16));
          dbms_output.put_line('...Master Piece Number in file...: ' || var_values(17));
        END IF;
        dbms_output.put_line('...Operating System of source db.: ' || var_values(9));
        IF v_fileversion >= '2.1' THEN
          dbms_output.put_line('...Instance Name of source db....: ' || var_values(10));
        END IF;
        dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
        dbms_output.put_line('...Language Name of characterset.: ' || var_values(11));
        dbms_output.put_line('...Job Name......................: ' || var_values(8));
        dbms_output.put_line('...GUID (unique job identifier)..: ' || var_values(3));
        dbms_output.put_line('...Block size dump file (bytes)..: ' || var_values(12));
        dbms_output.put_line('...Metadata Compressed...........: ' || var_values(14));
        IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
          dbms_output.put_line('...Data Compressed...............: ' || var_values(18));
          IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN
            dbms_output.put_line('...Compression Algorithm.........: ' || var_values(23));
          END IF;
          dbms_output.put_line('...Metadata Encrypted............: ' || var_values(19));
          dbms_output.put_line('...Table Data Encrypted..........: ' || var_values(20));
          dbms_output.put_line('...Column Data Encrypted.........: ' || var_values(21));
          dbms_output.put_line('...Encryption Mode...............: ' || var_values(22));
        END IF;
      ELSE
        dbms_output.put_line(
           'Filetype.: ' || v_filetype || ' (External Table dumpfile)');
        dbms_output.put_line(v_separator);
        dbms_output.put_line('...Database Job Version..........: ' || var_values(15));
        dbms_output.put_line('...Internal Dump File Version....: ' || var_values(1));
        dbms_output.put_line('...Creation Date.................: ' || var_values(6));
        dbms_output.put_line('...File Number (in dump file set): ' || var_values(4));
        dbms_output.put_line('...Operating System of source db.: ' || var_values(9));
        IF v_fileversion >= '2.1' THEN
          dbms_output.put_line('...Instance Name of source db....: ' || var_values(10));
        END IF;
        dbms_output.put_line('...Characterset ID of source db..: ' || var_values(5));
        dbms_output.put_line('...Language Name of characterset.: ' || var_values(11));
        dbms_output.put_line('...GUID (unique job identifier)..: ' || var_values(3));
        dbms_output.put_line('...Block size dump file (bytes)..: ' || var_values(12));
        IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
          dbms_output.put_line('...Data Compressed...............: ' || var_values(18));
          IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 22 AND v_fileversion >= '4.1' THEN
            dbms_output.put_line('...Compression Algorithm.........: ' || var_values(23));
          END IF;
          dbms_output.put_line('...Table Data Encrypted..........: ' || var_values(20));
          dbms_output.put_line('...Encryption Mode...............: ' || var_values(22));
        END IF;
      END IF;
      dbms_output.put_line('...Internal Flag Values..........: ' || var_values(7));
      dbms_output.put_line('...Max Items Code (Info Items)...: ' ||
                  dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE);
    END IF;
  ELSE
    dbms_output.put_line('Filetype.: ' || v_filetype);
    dbms_output.put_line(v_separator);
    dbms_output.put_line('ERROR....: Not an export dumpfile.');
  END IF;
  dbms_output.put_line(v_separator);

EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('Disk Path: ?');
    dbms_output.put_line('Filetype.: ?');
    dbms_output.put_line(v_separator);
    dbms_output.put_line('ERROR....: Directory Object does not exist.');
    dbms_output.put_line(v_separator);
  WHEN no_file_found THEN
    dbms_output.put_line('Disk Path: ' || v_path);
    dbms_output.put_line('Filetype.: ?');
    dbms_output.put_line(v_separator);
    dbms_output.put_line('ERROR....: File does not exist.');
    dbms_output.put_line(v_separator);
END;
/

Lets Execute the Procedure and Observe the Output.

SQL> SET serveroutput on SIZE 1000000
SQL> exec show_dumpfile_info('DATA_PUMP_DIR', 'EXPDAT.DMP') ;
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile.        Version: 18-DEC-2013
Required.: RDBMS version: 10.2.0.1.0 or higher
.          Export dumpfile version: 7.3.4.0.0 or higher
.          Export Data Pump dumpfile version: 10.1.0.1.0 or higher
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE');
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp')
----------------------------------------------------------------------------
Filename.: EXPDAT.DMP
Directory: DATA_PUMP_DIR
Disk Path: C:\app\Dell\product\21c\admin\xe\dpdump/
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...Database Job Version..........: 21.00.00.00.00
...Internal Dump File Version....: 5.1
...Creation Date.................: Mon Sep 12 13:14:38 2022
...File Number (in dump file set): 1
...Master Present in dump file...: 1 (Yes)
...Master in how many dump files.: 1
...Master Piece Number in file...: 1
...Operating System of source db.: <>
...Instance Name of source db....: DESKTOP-<>:xe
...Characterset ID of source db..: 873 (AL32UTF8)
...Language Name of characterset.: AL32UTF8
...Job Name......................: "SYS"."SYS_EXPORT_FULL_01"
...GUID (unique job identifier)..: <>
...Block size dump file (bytes)..: 4096
...Metadata Compressed...........: 1 (Yes)
...Data Compressed...............: 0 (No)
...Compression Algorithm.........: 3 (Basic)
...Metadata Encrypted............: 0 (No)
...Table Data Encrypted..........: 0 (No)
...Column Data Encrypted.........: 0 (No)
...Encryption Mode...............: 2 (None)
...Internal Flag Values..........: 514
...Max Items Code (Info Items)...: 26
----------------------------------------------------------------------------

PL/SQL procedure successfully completed.

SQL>

Posted in DATAPUMP, Export, METADATA, Oracle Administration | Tagged: DATAPUMP, EXPDP, METADATA, oracle | Leave a Comment »

Solving Sudoku Using SQL – By Anton Scheffer

Posted by Sriram Sanka on September 12, 2022

Here is an Excellent piece of code that Gets the Sudoku Resolved.

with x( s, ind ) as
( select sud, instr( sud, ' ' )
  from ( select '&' sud from dual )
  union all
  select substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 )
       , instr( s, ' ', ind + 1 )
  from x
     , ( select to_char( rownum ) z
         from dual
         connect by rownum <= 9
       ) z
  where ind > 0
  and not exists ( select null
                   from ( select rownum lp
                          from dual
                          connect by rownum <= 9
                        )
                   where z = substr( s, trunc( ( ind - 1 ) / 9 ) * 9 + lp, 1 )
                   or    z = substr( s, mod( ind - 1, 9 ) - 8 + lp * 9, 1 )
                   or    z = substr( s, mod( trunc( ( ind - 1 ) / 3 ), 3 ) * 3
                                      + trunc( ( ind - 1 ) / 27 ) * 27 + lp
                                      + trunc( ( lp - 1 ) / 3 ) * 6
                                   , 1 )
                 )
)
select line_wrap(s,9)
from x
where ind = 0
/

Posted in Functions In oracle, sql, SUDOKU | Tagged: oracle, sql, SUDOKU | Leave a Comment »

Date Difference(datediff) Using Oracle SQL – By Thomas Kyte(ASKTOM)

Posted by Sriram Sanka on September 11, 2022

Its an Old Good Script from my Script Collection to find the number of minutes/Seconds/Hours between two dates- Original Author – TomKyte.

create or replace function datediff( p_what in varchar2,
                                       p_d1   in date,
                                         p_d2   in date ) return number
    as
        l_result    number;
    begin
        select (p_d2-p_d1) *
               decode( upper(p_what),
                       'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL )
        into l_result from dual;
 
       return l_result;
   end;
   /

WITH data
     AS (SELECT To_date(SYSDATE - 2, 'dd-mon-yyyy hh24:mi:ss') d1,
                To_date(SYSDATE, 'dd-mon-yyyy hh24:mi:ss')     d2
         FROM   dual)
SELECT Datediff('ss', d1, d2) AS seconds,
       Datediff('mi', d1, d2) AS minutes,
       Datediff('hh', d1, d2) hours
FROM   data

SQL> WITH data
  2       AS (SELECT To_date(SYSDATE - 2, 'dd-mon-yyyy hh24:mi:ss') d1,
  3                  To_date(SYSDATE, 'dd-mon-yyyy hh24:mi:ss')     d2
  4           FROM   dual)
  5  SELECT Datediff('ss', d1, d2) AS seconds,
  6         Datediff('mi', d1, d2) AS minutes,
  7         Datediff('hh', d1, d2) hours
  8  FROM   data ;

   SECONDS    MINUTES      HOURS
---------- ---------- ----------
    172800       2880         48

SQL>

Posted in Functions In oracle, Uncategorized | Tagged: datediff, function, oracle, sql | Leave a Comment »

Article From Infoworld about Oracle Code flaw

Posted by Sriram Sanka on January 19, 2012

Yesterday, there was an interesting article was posted/published On InfoWorld By Paul Venezia.

http://www.infoworld.com/d/security/fundamental-oracle-flaw-revealed-184163
http://www.infoworld.com/t/data-management/calling-all-oracle-customers-184103?source=fssr

Oracle has published this On Its MOS Bug 12371955 Backup task can cause increased SCN growth rate leading to ORA-600 [2252] errors with Document ID [ID 12371955.8] and Old existing Document 253977.1 will also give some information on it.

for this to be fixed , One should apply the latest CPU patch released on 18-Jan-2012 by Oracle.

Oracle has published an article on MOS yesterday: note 1376995.1 with an associated script “scnhealthcheck.sql” you can find and download in note 1393363.1.which are very helpful.

Posted in Uncategorized | Tagged: infoworld, oracle, oracle customers | Leave a Comment »

My Experiences with Databases & More

Oracle-MySQL-SQL SERVER-Python-Azure-AWS-Oracle Cloud-GCP etc

Moderator at

Member at

Subscribe

Categories

Archives

Visits n Views

Blogs I Follow

Follow Blog via Email

Total Views

$riram $anka

Posts Tagged ‘oracle’

SQL Formatter – Using Java/PL/SQL by Sayan Malakshinov

Query for Top 10 by Executions:

Gather the Header Information and the Content of an Export Dumpfile

Solving Sudoku Using SQL – By Anton Scheffer

Date Difference(datediff) Using Oracle SQL – By Thomas Kyte(ASKTOM)

Article From Infoworld about Oracle Code flaw

My Experiences with Databases & More

Oracle-MySQL-SQL SERVER-Python-Azure-AWS-Oracle Cloud-GCP etc

Moderator at

Member at

Subscribe

Categories

Archives

Visits n Views

Blogs I Follow

Follow Blog via Email

Total Views

$riram $anka

Posts Tagged ‘oracle’

SQL Formatter – Using Java/PL/SQL by Sayan Malakshinov

Share this

Like this:

Query for Top 10 by Executions:

Share this

Like this:

Gather the Header Information and the Content of an Export Dumpfile

Share this

Like this:

Solving Sudoku Using SQL – By Anton Scheffer

Share this

Like this:

Date Difference(datediff) Using Oracle SQL – By Thomas Kyte(ASKTOM)

Share this

Like this:

Article From Infoworld about Oracle Code flaw

Share this

Like this: