DBA Blogs

Loading CLOB data (more than 32k char) into Oracle DB through Apex

Tom Kyte - 6 hours 1 min ago
Hi, I am currently working on developing apex web application which requires to capture CLOB data and save it to DB. I am using Rich text editor to capture the content and noticed that only max of 32k characters can be loaded through the page item. when I try to push data more than 32k char, no data is being sent to the DB. Please suggest me a way to capture data more than 32k through apex page item. Oracle DB Version: 12c Apex version:20.1 Kindly let me know if I am missing any details Thanks, Murugananth
Categories: DBA Blogs

are WITH READ ONLY sand WITH CHECK OPTION syntaxes used for views and tables?

Tom Kyte - 6 hours 1 min ago
Hi, I am not sure whether or not WITH READ ONLY and WITH CHECK OPTION syntaxes used for views and tables only. I read on Oracle Help Center with the following link https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/CREATE-VIEW.html#GUID-61D2D2B4-DACC-4C7C-89EB-7E50D9594D30. It is said that: WITH READ ONLY Specify WITH READ ONLY to indicate that the table or view cannot be updated. WITH CHECK OPTION Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause. I hope to receive your response. Thanks, Kris.
Categories: DBA Blogs

ORA-01031: insufficient privileges, cannot login as any user

Tom Kyte - 6 hours 1 min ago
I ran below command and restarted docker container. <code>alter system set processes = 1 scope = spfile;</code> after this I am not able to login to DB at all. <code> root@30b2f9030f89:/u01/app/oracle/product/11.2.0/xe/bin# sqlplus /nolog SQL*Plus: Release 11.2.0.2.0 Production on Wed Aug 5 14:59:14 2020 Copyright (c) 1982, 2011, Oracle. All rights reserved. SQL> conn / as sysdba ERROR: ORA-01031: insufficient privileges </code> I tried to resolve it with below command, tried login again, still getting same error orapwd file=filename password=password entries=100 Also tried editting /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora file and manually assign processes=100 and back to processes=1 this didnt help. can someone guide here please?
Categories: DBA Blogs

Can we use RETURNING CLAUSE along with CURRENT OF clause in update statement.

Tom Kyte - 6 hours 1 min ago
Hi Tom, I am using below update statement in my procedure to return few columns that are getting updated in the update statement. <code>UPDATE DUMMY_TABLE SET DUMMY_STATUS = 'ABC' WHERE CURRENT OF DUMMY_CURSOR RETURNING DUMMY_FIELD1, DUMMY_FIELD2 BULK COLLECT INTO TAB_FIELD1, TAB_FIELD2;</code> The above code works if i am not using CURRENT OF CLAUSE, but is giving error when used like above... I want to know if i am making any syntax error here...or this is not possible at all.. Note: I cannot remove current of clause from the update statement, and still I have to return the columns that are getting updated. Thanks
Categories: DBA Blogs

Different Ways to Access Oracle Cloud Infrastructure

Pakistan's First Oracle Blog - 7 hours 27 min ago

This is a quick jot down of different ways you can access the ever-improving Oracle Cloud Infrastructure (OCI). Most types of Oracle Cloud Infrastructure resources have a unique, Oracle-assigned identifier called an Oracle Cloud ID (OCID).

You can access Oracle Cloud Infrastructure using the Console (a browser-based interface) or the REST API. To access the Console, you must use a supported browser. You can go to the sign-in page. You will be prompted to enter your cloud tenant, your user name, and your password. The Oracle Cloud Infrastructure APIs are typical REST APIs that use HTTPS requests and responses.

All Oracle Cloud Infrastructure API requests must be signed for authentication purposes. All Oracle Cloud Infrastructure API requests must support HTTPS and SSL protocol TLS 1.2. Oracle Cloud Infrastructure provides a number of Software Development Kits (SDKs) and a Command Line Interface (CLI) to facilitate development of custom solutions.

Software Development Kits (SDKs) Build and deploy apps that integrate with Oracle Cloud Infrastructure services. Each SDK provides the tools you need to develop an app, including code samples and documentation to create, test, and troubleshoot. In addition, if you want to contribute to the development of the SDKs, they are all open source and available on GitHub.

  • SDK for Java
  • SDK for Python
  • SDK for TypeScript and JavaScript
  • SDK for .NET
  • SDK for Go
  • SDK for Ruby

Command Line Interface (CLI) The CLI provides the same core capabilities as the Oracle Cloud Infrastructure Console and provides additional commands that can extend the Console's functionality. The CLI is convenient for developers or anyone who prefers the command line to a GUI.

Categories: DBA Blogs

Re-Register ArchiveLogs and Backups

Hemant K Chitale - 10 hours 53 min ago
If you as the DBA or someone else as the SysAdmin has had to delete and restore or relocate ArchiveLogs or RMAN Backups to another mount point, you'd find that Oracle can no longer identify them.

This would also happen if you run a CREATE CONTROLFILE -- all information about RMAN Backups and ArchiveLogs that was formerly in the controlfile is "lost" because your database starts with a controlfile that has information only about DataFiles and Online RedoLogs.

How do you re-register ArchiveLogs and RMAN Backups ?

I begin this demo with 3 ArchiveLogs (Sequence#50 to #52) and one BackupSet created today.


SQL> l
1 select sequence#, name from v$archived_log
2 where dest_id=1
3 and (completion_time > sysdate-1 OR first_time > sysdate-1)
4* order by sequence#
SQL> /

SEQUENCE# NAME
---------- --------------------------------------------------
50 /opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf
51 /opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf
52 /opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf

SQL>
RMAN> list archivelog all completed after "sysdate-1";

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
144 1 50 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf

145 1 51 A 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf

146 1 52 A 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf


RMAN>
RMAN> list backup completed after "sysdate-1";


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 790.52M DISK 00:00:23 06-AUG-20
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20200806T173252
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp
List of Datafiles in backup set 17
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 5356172 06-AUG-20 NO /opt/oracle/oradata/ORCLCDB/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Full 18.02M DISK 00:00:01 06-AUG-20
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20200806T173317
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_08_06/o1_mf_s_1047749597_hlqmly7z_.bkp
SPFILE Included: Modification time: 06-AUG-20
SPFILE db_unique_name: ORCLCDB
Control File Included: Ckp SCN: 5356230 Ckp time: 06-AUG-20

RMAN>



Sometime later, these get deleted and  I can no longer find them.




RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 device type=DISK
validation succeeded for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_48_1036108814.dbf RECID=139 STAMP=1047211353
validation succeeded for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_49_1036108814.dbf RECID=142 STAMP=1047211650
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf RECID=144 STAMP=1047749103
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf RECID=145 STAMP=1047749332
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf RECID=146 STAMP=1047749334
Crosschecked 5 objects


RMAN>
RMAN> crosscheck backup completed after "sysdate-1";

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp RECID=17 STAMP=1047749572
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_08_06/o1_mf_s_1047749597_hlqmly7z_.bkp RECID=18 STAMP=1047749598
Crosschecked 2 objects


RMAN>

oracle19c>pwd
/opt/oracle/archivelog/ORCLCDB
oracle19c>ls -ltr |tail -2
-rw-r-----. 1 oracle oinstall 6656 Jul 31 12:02 1_48_1036108814.dbf
-rw-r-----. 1 oracle oinstall 203776 Jul 31 12:07 1_49_1036108814.dbf
oracle19c>
oracle19c>pwd
/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06
oracle19c>ls -ltr
total 0
oracle19c>

RMAN> list expired backup;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 790.52M DISK 00:00:23 06-AUG-20
BP Key: 17 Status: EXPIRED Compressed: NO Tag: TAG20200806T173252
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp
List of Datafiles in backup set 17
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 5356172 06-AUG-20 NO /opt/oracle/oradata/ORCLCDB/system01.dbf

RMAN> delete expired backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 device type=DISK

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
17 17 1 1 EXPIRED DISK /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp RECID=17 STAMP=1047749572
Deleted 1 EXPIRED objects


RMAN>



So, ArchiveLogs from Sequence#50 to Sequence#52 have been deleted and so has today's backupset (although the controlfile autobackup is still present).

I ask the SysAdmin to restore the misssing files.  He restores them to a different mountpoint  -- under /NEWFS.
I then re-register them.



oracle19c>pwd
/NEWFS/archivelog/ORCLCDB
oracle19c>ls -l
total 4432
-rw-r-----. 1 oracle oinstall 1720832 Aug 6 17:25 1_50_1036108814.dbf
-rw-r-----. 1 oracle oinstall 2808320 Aug 6 17:28 1_51_1036108814.dbf
-rw-r-----. 1 oracle oinstall 1536 Aug 6 17:28 1_52_1036108814.dbf
oracle19c>

oracle19c>pwd
/NEWFS/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06
oracle19c>ls -l
total 809504
-rw-r-----. 1 oracle oinstall 828932096 Aug 6 17:33 o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp
oracle19c>

SQL> alter database register physical logfile '/NEWFS/archivelog/ORCLCDB/1_50_1036108814.dbf';

Database altered.

SQL> alter database register physical logfile '/NEWFS/archivelog/ORCLCDB/1_51_1036108814.dbf';

Database altered.

SQL> alter database register physical logfile '/NEWFS/archivelog/ORCLCDB/1_52_1036108814.dbf';

Database altered.

SQL>
RMAN> list archivelog all completed after "sysdate-1";

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
148 1 50 A 31-JUL-20
Name: /NEWFS/archivelog/ORCLCDB/1_50_1036108814.dbf

144 1 50 X 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf

149 1 51 A 06-AUG-20
Name: /NEWFS/archivelog/ORCLCDB/1_51_1036108814.dbf

145 1 51 X 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf

150 1 52 A 06-AUG-20
Name: /NEWFS/archivelog/ORCLCDB/1_52_1036108814.dbf

146 1 52 X 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf

147 1 53 A 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_53_1036108814.dbf


RMAN>
RMAN> crosscheck archivelog all completed after "sysdate-1";

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=285 device type=DISK
validation succeeded for archived log
archived log file name=/NEWFS/archivelog/ORCLCDB/1_50_1036108814.dbf RECID=148 STAMP=1047752869
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf RECID=144 STAMP=1047749103
validation succeeded for archived log
archived log file name=/NEWFS/archivelog/ORCLCDB/1_51_1036108814.dbf RECID=149 STAMP=1047752894
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf RECID=145 STAMP=1047749332
validation succeeded for archived log
archived log file name=/NEWFS/archivelog/ORCLCDB/1_52_1036108814.dbf RECID=150 STAMP=1047752905
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf RECID=146 STAMP=1047749334
validation succeeded for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_53_1036108814.dbf RECID=147 STAMP=1047751145
Crosschecked 7 objects


RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=285 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
144 1 50 X 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf

145 1 51 X 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf

146 1 52 X 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf RECID=144 STAMP=1047749103
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf RECID=145 STAMP=1047749332
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf RECID=146 STAMP=1047749334
Deleted 3 EXPIRED objects


RMAN>
RMAN> list archivelog all completed after "sysdate-1";

List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
148 1 50 A 31-JUL-20
Name: /NEWFS/archivelog/ORCLCDB/1_50_1036108814.dbf

149 1 51 A 06-AUG-20
Name: /NEWFS/archivelog/ORCLCDB/1_51_1036108814.dbf

150 1 52 A 06-AUG-20
Name: /NEWFS/archivelog/ORCLCDB/1_52_1036108814.dbf

147 1 53 A 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_53_1036108814.dbf


RMAN>
--- note that ArchiveLog 53 is a new one that has been generated recently, in the default location

RMAN> catalog start with '/NEWFS/FRA';

searching for all files that match the pattern /NEWFS/FRA

List of Files Unknown to the Database
=====================================
File Name: /NEWFS/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /NEWFS/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp

RMAN> list backup completed after "sysdate-1";


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Full 18.02M DISK 00:00:01 06-AUG-20
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20200806T173317
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_08_06/o1_mf_s_1047749597_hlqmly7z_.bkp
SPFILE Included: Modification time: 06-AUG-20
SPFILE db_unique_name: ORCLCDB
Control File Included: Ckp SCN: 5356230 Ckp time: 06-AUG-20

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 790.52M DISK 00:00:23 06-AUG-20
BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20200806T173252
Piece Name: /NEWFS/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp
List of Datafiles in backup set 19
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 5356172 06-AUG-20 NO /opt/oracle/oradata/ORCLCDB/system01.dbf

RMAN> crosscheck backup completed after "sysdate-1";

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_08_06/o1_mf_s_1047749597_hlqmly7z_.bkp RECID=18 STAMP=1047749598
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/NEWFS/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp RECID=19 STAMP=1047753112
Crosschecked 2 objects


RMAN>



To re-register ArchiveLogs, I used the SQL command "ALTER DATABASE REGISTER PHYSICAL LOGFILE'.  RMAN is then able to identify these ArchiveLogs as well.  I then use CROSSCHECK and DELETE EXPIRED in RMAN to delete information about the old location of the same ArchiveLogs.

To re-register RMAN Backups, I used the RMAN command "CATALOG START WITH".  And then used "CROSSCHECK" to confirm that the BackupPiece(s) is/are available.




Categories: DBA Blogs

Oracle 11g on AWS RDS Will Be Force Upgraded in Coming Months

Pakistan's First Oracle Blog - 15 hours 36 min ago
To make a long story short: If you have Oracle 11g running on AWS RDS, then start thinking, planning, and implementing it's upgrade to a later version, preferably Oracle 19c. 

This is what AWS has to say about this:

Oracle has announced the end date of support for Oracle Database version 11.2.0.4 as December 31, 2020, after which Oracle Support will no longer release Critical Patch Updates for this database version. Amazon RDS for Oracle will end support for Oracle Database version 11.2.0.4 Standard Edition 1 (SE1) for License Included (LI) model on October 31, 2020. For the Bring Your Own License (BYOL) model, Amazon RDS for Oracle will end the support for Oracle Database version 11.2.0.4 for all editions on December 31, 2020. All 11.2.0.4 SE1 LI instances will be automatically upgraded to 19c starting on November 1, 2020. Likewise, the 11.2.0.4 BYOL instances will be automatically upgraded to 19c starting on January 1, 2021. We highly recommend you upgrade your existing Amazon RDS for Oracle 11.2.0.4 DB instances and validate your applications before the automatic upgrades begin. 

The bit which probably would apply to most of enterprise customers who are running Oracle 11g with BYOL license is this:

January 1, 2021Amazon RDS for Oracle starts automatic upgrades of DB instances restored from snapshots to 19c
Instead of leaving to the last minute, its better to upgrade it sooner. There are lots of things which need to be taken into consideration for this upgrade within and outside of the database. If you need any hand with that, feel free to reach out.
Categories: DBA Blogs

How to copy the csv files from the shared path to HP-Unix server Data base directory

Tom Kyte - Wed, 2020-08-05 16:06
Hi Sir, I'm using the Oracle 9i database in the HP-UNIX system. The directory name is "XX_DATA" Shared network path "\\mkees01\public\Details.csv" May you please help me by providing the code for below scenario. From the Shared network path file - Details.csv how to copy to the oracle database directory? Regards, Sankar
Categories: DBA Blogs

JSON_OBJECT throws error in Stored Procedure

Tom Kyte - Wed, 2020-08-05 16:06
Dear Team, I am trying to use the JSON functions in a stored procedure and TOAD throws the syntax error. <b> Found 'value', an alias is not allowed here (reserved for XMLCOLATTVAL, XMLFOREST and XMLATTRIBUTES only)</b> Below is the query that I use. However when I try to run outside the stored procedure, it works fine. <code>SELECT JSON_OBJECT ( 'empid' value '1' , 'name' value 'Tom' , 'address' value '23333' ) into emp_json FROM dual ;</code> Could you please help me to identify the cause of the error. Thank You.
Categories: DBA Blogs

Best practice to delete rows with a CLOB column

Tom Kyte - Wed, 2020-08-05 16:06
Environment: Oracle 12.1.0.2 on Exadata I have a table with 30 columns, one of which is a CLOB, that contains about 26 million rows. I have a purge job (PL/SQL packaged procedure) that DELETEs rows from nine (9) other tables based on a list of IDs from the driver table that contains the CLOB. I save the list of IDs in a global temporary table and use that to delete the associated rows from the other tables that are done in the correct order to maintain all the FK relationships. I am running a test today that has identified about 98,000 IDs to be purged but I have about 5 million that need to be purged to 'catch up' and going forward I'll have about 10,000 per day to purge. The DELETE on the other 9 tables runs very quickly. The table with the CLOB column is taking many times longer than the others, like several hours as opposed to several minutes. I'm guessing there is a better and more efficient way to remove the rows from the table containing the CLOB and to regain the storage used by the deleted CLOB. I'm currently issuing just a 'DELETE FROM <table> WHERE ID IN (SELECT ID FROM <gtt>', where ID is the PK. I would appreciate any suggestions and order of operational steps to accomplish this purge, both for the 'catch up' and the daily run of 10,000. Thanks in advance for all the help!! Much appreciated! -gary
Categories: DBA Blogs

Rebuilding Oracle Text Indexes

Tom Kyte - Wed, 2020-08-05 16:06
Dear Team, In our IFS Applications product, we heavily use Oracle text indexes. we are not updating the indexes real time instead we do it at a separate time interval through <i>Ctx_Ddl.Sync_Index</i> passing a CLOB document. We synchronize the indexes with a default 30 minute interval. In the maintenance cycle, we optimize an index once a week using <i>Ctx_DdL.Optimize_Index</i> method using FAST option. Also we perform an ALTER INDEX REBUILD for the text index once a week. Recently we encountered some errors with this REBUILD operation at few customers so having a thought how we should go forward. When we went through the Oracle documentation, we saw some articles related to this but they are somewhat confusing. https://docs.oracle.com/en/database/oracle/oracle-database/19/ccref/CTX_DDL-package.html#GUID-28E079B1-D5CA-4264-B1C0-A1C5CE174C55 It says: "Using this procedure to optimize the index is recommended over using the <i>ALTER INDEX</i> statement." https://docs.oracle.com/en/database/oracle/oracle-database/19/ccapp/maintaining-oracle-text-indexes.html#GUID-10365262-8B48-40AA-B1F3-DE3268EA9B39 It says: "You might rebuild an index when you want to index with a new preference" We have few questions to clarify. 1. We are <b>not changing any preferences</b> of the text index once after it was created at the installation time, so do we want to rebuild the text index in a scheduled manner? Is there any other benefit doing so? 2. Do you believe <i>Ctx_Ddl_Optimize_Index </i>with <b>REBUILD </b>option instead <b>FAST </b>option would be a good option to have if we skip rebuilding the index using <i>ALTER INDEX</i> statement? Or is it unnecessary in our situation. 3. Going forward, do you see any other risks if we are to remove <i>ALTER INDEX REBUILD</i> & continue with only <i>Ctx_Ddl_Optimize_Index</i> with <b>FAST </b>option? May be in aspects such as performance, etc. Thanks & Best Regards, Navinth
Categories: DBA Blogs

Comparing 2 Nested Table Collection Which have 180 field defined in each collection by passing field name dynamically.

Tom Kyte - Wed, 2020-08-05 16:06
I have 2 tables which of same structure with around 180 columns in each table with one columns as PK. Each table have around 200 k records. I need to compare to tables columns by column and if for that records any difference is found for any of the remaining 179 columns then need to track that column name and the both the old values and new value from that column from both tables. This can be achieve by SQL statement with UNION and group and LEAD functions but as need to compare the for 179 fields the length of the SQL code is very long. So thought of used nested table collection to compare both the tables by bulk collect both the values is 2 different nested tables and iterate them. First loop to iterate using collection count value and second loop using USER_TAB_COLS to iterate based on number of columns in the tables. Is there any possible to pass the field name to the nested loop dynamically ? Below is the sample code for that. <code>SET SERVEROUTPUT ON; DECLARE TYPE TEST1_TYPE IS TABLE OF TEST1%ROWTYPE ; TEST1_TAB TEST1_TYPE; TEST2_TAB TEST1_TYPE; lcCol1 VARCHAR2(3000); lcCol2 VARCHAR2(3000); lQuery VARCHAR2(3000); CURSOR CUR_TAB_COL IS SELECT COLUMN_NAME ,DATA_TYPE FROM USER_TAB_COLS WHERE TABLE_NAME='TEST1' ORDER BY COLUMN_ID; TYPE COL_TYPE IS TABLE OF CUR_TAB_COL%ROWTYPE; COL_TAB COL_TYPE; BEGIN SELECT * BULK COLLECT INTO TEST1_TAB FROM TEST1 ORDER BY ID; SELECT * BULK COLLECT INTO TEST2_TAB FROM TEST2 ORDER BY ID; OPEN CUR_TAB_COL; FETCH CUR_TAB_COL BULK COLLECT INTO COL_TAB; CLOSE CUR_TAB_COL; FOR I IN 1..TEST2_TAB.count LOOP FOR j IN COL_TAB.FIRST..COL_TAB.LAST LOOP lQuery:='SELECT TEST1_TAB('||i||').'||COL_TAB(j).COLUMN_NAME||',FROM DUAL'; EXECUTE IMMEDIATE lQuery INTO lcCol1; lQuery:='SELECT TEST2_TAB('||i||').'||COL_TAB(j).COLUMN_NAME||',FROM DUAL'; EXECUTE IMMEDIATE lQuery INTO lcCol2; END LOOP; END LOOP; END; /</code>
Categories: DBA Blogs

Oracle Cloud's Beefed Up Security

Pakistan's First Oracle Blog - Wed, 2020-08-05 01:23
During the first few months of the COVID-19 pandemic, many organizations expected a slowdown in their digital transformation efforts. But surprisingly, things haven't slowed down in many places instead, many enterprises accelerated their use of cloud-based services to help them manage and address emerging priorities in the new normal, which includes a distributed workforce and new digital strategies. 

More and more companies, especially those in regulated industries, want to adopt the latest cloud technologies, but they often face barriers due to strict data privacy or compliance requirements. As cloud adoption grows, we’re seeing exponential growth in cloud resources. With this we’re also seeing growth in permissions, granted to humans and workloads, to access and change those resources. This introduces potential risks, including the misuse of privileges, that can compromise your organization’s security.

To mitigate these risks, ideally every human or workload should only be granted the permissions they need, at the time they need them. This is the security best practice known as “least privilege access.” Oracle Cloud Infrastructure Identity and Access Management (IAM) lets you control who has access to your cloud resources. You can control what type of access a group of users have and to which specific resources. 

Compartments are a fundamental component of Oracle Cloud Infrastructure for organizing and isolating your cloud resources. You use them to clearly separate resources for the purposes of measuring usage and billing, access (through the use of policies), and isolation (separating the resources for one project or business unit from another). A common approach is to create a compartment for each major part of your organization. 

The first step in establishing least privilege is understanding which permissions a user has today and which have been used recently. Then, you need to understand which permissions this user is likely to need in the future, so you avoid getting into a manually intensive trial-and-error loop of assigning incremental permissions. Once you have that, you need to decide how to construct your identity and access management (IAM) policies so that you can reuse roles across several compartments.

In the Console, you view your cloud resources by compartment. This means that after you sign in to the Console, you'll choose which compartment to work in (there's a list of the compartments you have access to on the left side of the page). Notice that compartments can be nested inside other compartments. The page will update to show that compartment's resources that are within the current region. If there are none, or if you don't have access to the resource in that compartment, you'll see a message.

This experience is different when you're viewing the lists of users, groups, dynamic groups, and federation providers. Those reside in the tenancy itself (the root compartment), not in an individual compartment.

As for policies, they can reside in either the tenancy or a compartment, depending on where the policy is attached. Where it's attached controls who has access to modify or delete it. 
Categories: DBA Blogs

How prefix_index and substring_index improve wildcard searches in Oracle Text

Tom Kyte - Tue, 2020-08-04 21:46
Hi Team, I'm doing some Oracle Text work related to prefix_index and substring_index (we use Oracle 11g). I'm interested in why: (1)Prefix indexing improves performance for right truncated wildcard searches such as TO% (2)A substring index improves left-truncated and double-truncated wildcard queries such as %ing or %benz% Could you help to check whether my following understanding is right or wrong? Thanks! (Maybe the following understanding and questions are not very clear. I just want to know the search logic and process of queries like %abc, abc%, %abc% using prefix_index and substring_index by Oracle Text) (1) I want to confirm principles of extracting tokens using Oracle Text. After we extract tokens from text (tokens are stored in table $I), keywords that user inputs to query will be extract into tokens, too. If at least one keyword token is equal to text token, then user can get matched text. For example, text1 is extracted into token1 and token2 (stored in table $I). Then user inputs some keywords to query. Keywords are extracted into token2 and token3, since token2 is stroed in $I, user can get search result containing text1. However, if keywords are extracted into token3 and token4. Suppose that token3 is prefix of token2, user still can't get search result containing text1 because token2 != token3. Am I understanding right or wrong? Thanks. (2)When we use prefix_index (and set prefix_length_min=2, prefix_length_max=3), some prefix tokens ?token_type = 6 ) will be stored in $I. When we use right truncated wildcard searches, for example: (a) Search to%. Length of "to" is 2. So it will directly search in tokens (only token_type=6) in $I table. If token "to" is in $I and its token_type is 6, then user can get search results matching to%. If token "to" is not in $I, then search result is empty. But we find that sometimes one prefix token (token_type = 6) is stored in $I table (e.g. this token is "??"). Then we search ??%, the search result is empty. Do you know the reason? Thanks. (b) Search toto%. Length of "toto" is 4 (prefix_legth_max is 3). So it will directly search every token (token_type = 0) in $I table. Am I understanding right or wrong? Thanks. (3)When we use substring_index, some substring tokens will be stored in $P. When we use left truncated wildcard searches, for example: (a) Search %abc, then it will directly search in $P table using column PAT_PART2. If PAT_PART2 has token abc, then user can get search results matching %abc. If PAT_PART2 hasn't token abc, then search result is empty. (b) Search %a (length of "a" is 1), since length of all tokens in $P table in column PAT_PART2 is larger than 1, so it will directly search every token (token_type = 0) in $I table. Am I understanding right or wrong? Thanks. And I want to know that why using substring_index can improves double-truncated wildcard queries like %benz% (I understand substring_index can improve left-truncated wildcard queries, but I want to know the process and principle of querying %benz% by Oracle Text. Could you help to explain it, thanks!)
Categories: DBA Blogs

Oracle Cloud Shell

Hemant K Chitale - Tue, 2020-08-04 10:28
The Oracle Cloud (accessible even with a free account) now includes a Cloud Shell feature with 5GB of storage.

Here is a quick demo of this feature.


Categories: DBA Blogs

SQLLDR Process is showing password

Tom Kyte - Tue, 2020-08-04 03:26
Hi Tom, we have a risk of exposing our APPS schema password for host based concurrent programs in Oracle Apps. The ps -ef command exposes the password unless encrypted. We have gone back and added the ENCRYPT option for the Options Field for the Concurrent Program definitions. We have also used $FCP_LOGIN instead of $1 while invoking SQL*Plus or SQL*Loader. As part of these changes, we had a test case where we were trying to observe the this sub process, either SQL*Plus or SQL Loader itself in the ps -ef command. We have observed that SQL*Plus does not display the password in the ps -ef command. However, SQL Loader does display the password. Hence, Can you please let us know the way to fix this or any workarounds for this. Thanks in Advance
Categories: DBA Blogs

Managing Blockchain tables in Oracle Database 20c

Oracle’s multi model database 20c is a yearly short term release support, users and developers will benefit from the converged database approach of managing all data in one single database...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Query Statspack’s “SQL ordered by” sections over a time period

Pythian Group - Fri, 2020-07-31 09:26

In my previous blog post <link>, I presented the statspack_load_trends.sql script, which provides a high-level overview of workload dynamics on DB instance(s) over time. In this post, I’ll present the statspack_top_sqls.sql script, which returns SQL performance statistics by mining the Statspack repository. You can download it <here>.

The script provides the same SQLs with the same performance statistics as in Statspack’s various “SQL ordered by” sections. However, it does so by reporting the figures of all categories in a single line, and, more importantly, does it over a time range, considering each available consecutive pair of snapshots. Thus, it provides a way to quickly identify SQLs contributing the most to a particular “SQL ordered by” category over a time period. Alternatively, we can also check for specific SQL(s) behavior over time.

Its core logic is based on Oracle’s $ORACLE_HOME/rdbms/admin/sprepins.sql (StatsPack Report Instance) script, but it doesn’t rely on its existence to run. Similarly as for statspack_load_trends.sql, it queries the Statspack repository directly. It doesn’t create or use any (temporary) objects, not even the global temporary table that sprepins.sql uses when producing the report. We can also use it to analyze a repository imported from another DB and handles periods spanning instance restart(s).

Important note in case you want to compare the results with Statspack reports

Testing the script by comparing its output to regular Statspack reports (created by running $ORACLE_HOME/rdbms/admin/spreport.sql or sprepins.sql), I noticed that sometimes the numbers in the “SQL ordered by” sections didn’t match between the two. Examples include SQLs reported by my script, but not by Statspack reports. Or even Statspack reports reporting the same SQL (same hash value) multiple times in the same “SQL ordered by” section.
The root cause of those anomalies is described in the MOS note “Statspack Reports Show Different Results In “SQL ordered by …” When Taken At Different Times with Same Snapid (Doc ID 2258762.1)”: “When more than one statspack reports are taken repeatedly in the same session by non-perfstat user, some data might get mixed up in the temporary table used by the reporting script, and the result may get corrupted.”.

The problem was not connecting as the owner of the Statspack repository (usually PERFSTAT) when generating multiple consecutive snapshots looping over a snapshot range. The same was true also when creating a single Statpack report.
The takeaway is to always connect as the Statspack repository owner when running spreport.sql, especially if you use any helper scripts which generate Statspack reports for a series of snapshots.

Usage Starting the script

Let’s see the script in action analyzing a sample Swingbench run on a 2 node RAC database. The output is wide, so I suggest to spool it to a file for easier viewing/plotting:

SQL> spool top_sqls.txt
SQL> @statspack_top_sqls.sql

List SQL by [elapsed_time | cpu_time | buffer_gets | disk_reads | executions | parse_calls | max_sharable_mem | max_version_count | cluster_wait_time]:

Enter a value - default "elapsed_time" :

First, we specify by which category we want the SQLs to be ordered by. We can choose one of the above-listed possibilities, which are the same categories the “SQL ordered by” Statspack report’s sections displays. The script reports the same SQLs in the same order as they appear in the selected Statspack report category.

Suppose we want to order SQLs by “cpu_time”, and that the corresponding Statspack report lists 10 SQLs in the “SQL ordered by CPU” section. The script lists the same ones. However, the added benefit of the script is that it reports values, which the Statspack report doesn’t display. For example, the “SQL ordered by CPU” Statspack report section doesn’t display the “Physical Reads” statistic. Instead, the “SQL ordered by Elapsed time” section lists it. If a SQL isn’t qualified to display in the “SQL ordered by Elapsed time” section, we won’t get those values from the Statspack report.

Next, we provide the DBID and instance number we want to be analyzed. If we don’t provide an instance number, the script considers all which are present in the repository:

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ --------------------------------
 1558102526        1 ORCL         orcl1        ol7-122-rac1.localdomain
 1558102526        2 ORCL         orcl2        ol7-122-rac2.localdomain


Enter DBID to analyze - default "1558102526" :
Enter instance number or "all" to analyze all instancs for DBID = 1558102526 - default "all" :

Finally, we specify the time range we’d like to analyze:

Enter begin time for report [DD-MON-YYYY HH24:MI] - default "30-APR-2020 10:54" : 15-FEB-2020 12:30
Enter end time for report [DD-MON-YYYY HH24:MI] - default "30-APR-2020 22:54" : 15-FEB-2020 13:00
Script output

Let’s check what the script output looks like. Because of the output width, I have broken it up in several sections. The whole file containing the spool, and the respective Statspack reports for comparison, can be obtained here .

Since the original spool file contains 280 lines, I’m showing an abbreviated version of the first 8 columns. The full output lists SQLs between each snapshot. Since we didn’t specify which instance number we’re interested in, both instances are considered. Additionally, we see it’s normal and expected, that consecutive snapshots don’t have consecutive numbers. Snapshots 4 and 21 are two consecutive snapshots on instance 1, as we can confirm by checking their snap time. The other columns are self-explanatory:

INSTANCE_NUMBER  B_SNAP_ID  E_SNAP_ID B_SNAP_TIME        E_SNAP_TIME        INTERVAL_MIN  DBTIMEMIN        AAS
--------------- ---------- ---------- ------------------ ------------------ ------------ ---------- ----------
1          1          2 15-FEB-20 12:30:00 15-FEB-20 12:40:00           10       2.30       0.23
&lt;removed 16 lines listing SQLs&gt;
2          3 15-FEB-20 12:40:00 15-FEB-20 12:50:00           10       1.60       0.16
&lt;removed 25 lines listing SQLs&gt;
3          4 15-FEB-20 12:50:00 15-FEB-20 13:00:00           10       1.55       0.15
&lt;etc&gt;
4         21 15-FEB-20 13:00:00 15-FEB-20 13:10:00           10       1.66       0.17

21 22 15-FEB-20 13:10:00 15-FEB-20 13:20:00 10 1.30 0.13

22 23 15-FEB-20 13:20:00 15-FEB-20 13:30:00 10 1.18 0.12

2 11 12 15-FEB-20 12:30:00 15-FEB-20 12:40:00 10 3.81 0.38

12 13 15-FEB-20 12:40:00 15-FEB-20 12:50:00 10 2.70 0.27

13 14 15-FEB-20 12:50:00 15-FEB-20 13:00:00 10 2.50 0.25

14 15 15-FEB-20 13:00:00 15-FEB-20 13:10:00 10 2.94 0.29

15 16 15-FEB-20 13:10:00 15-FEB-20 13:20:00 10 2.18 0.22

16 17 15-FEB-20 13:20:00 15-FEB-20 13:30:00 10 1.98 0.20

 

Let’s check an excerpt of the output for snapshots 1-2 and 2-3. Apart from the “HV” column (SQL old hash value), the other columns are self-explanatory. For blog post brevity, I’m showing only the first 10 SQLs per snapshot pair.

B_SNAP_ID  E_SNAP_ID         HV ELAPSED_TIME_SEC EXECUTIONS ELAPSED_PER_EXEC_SEC PERCENT_OF_DBTIME_USED CPU_TIME_SEC CPU_TIME_MS_PER_EXEC
--------- ---------- ---------- ---------------- ---------- -------------------- ---------------------- ------------ --------------------
        1          2 3565022785            80,55        483                  ,17                  58,47        14,63                 30,3
                     2319948924             55,8       5931                  ,01                  40,51         7,69                  1,3
                     1852190137            14,22       1024                  ,01                  10,32         7,75                 7,57
                     1113394757             8,17      12332                    0                   5,93         2,97                  ,24
                     4194254847              6,4        483                  ,01                   4,64          ,84                 1,73
                     1283549268             4,55        169                  ,03                    3,3          ,89                 5,28
                     2588369535             4,21         24                  ,18                   3,06         1,12                46,55
                     4212635381             4,18         24                  ,17                   3,04         1,09                45,39
                     4219272024             3,97       1396                    0                   2,88          ,86                  ,62
                     2835506982             3,74        173                  ,02                   2,71          ,57                 3,32
                     (..)                                                                                                                                         
        2          3 3565022785            46,93        956                  ,05                  48,79        18,87                19,73
                     2319948924            22,85      11550                    0                  23,75         7,52                  ,65
                     1852190137            15,35       2158                  ,01                  15,95        11,98                 5,55
                     1283549268             6,36        380                  ,02                   6,61         1,65                 4,33
                     2835506982                6        377                  ,02                   6,24         1,03                 2,72
                     1822227481             5,32       7742                    0                   5,53         1,26                  ,16
                     4194254847             4,69        957                    0                   4,87         1,22                 1,28
                     3463613875             4,61        380                  ,01                   4,79          ,62                 1,62
                     1113394757             4,07      25794                    0                   4,23         3,28                  ,13
                     4219272024             3,89       2945                    0                   4,04         1,43                  ,49
                     (..)
B_SNAP_ID  E_SNAP_ID         HV PHYSICAL_READS PHYSICAL_READS_PER_EXECUTION BUFFER_GETS GETS_PER_EXECUTION ROWS_PROCESSED ROWS_PROCESSED_PER_EXECUTION PARSE_CALLS
--------- ---------- ---------- -------------- ---------------------------- ----------- ------------------ -------------- ---------------------------- -----------
        1          2 3565022785           5860                        12,13      261329             541,05            483                            1         483
                     2319948924           4614                          ,78      205925              34,72          26467                         4,46           1
                     1852190137            394                          ,38      131100             128,03           1024                            1        1025
                     1113394757            336                          ,03      124291              10,08         172648                           14           1
                     4194254847            396                          ,82        7760              16,07            483                            1           1
                     1283549268            262                         1,55        6188              36,62            169                            1         169
                     2588369535             76                         3,17       13104                546             24                            1          24
                     4212635381             76                         3,17       13104                546            737                        30,71           1
                     4219272024            167                          ,12       19979              14,31           1396                            1           1
                     2835506982            255                         1,47        3579              20,69            173                            1         173
                                                                                                                                                                  
        2          3 3565022785           1138                         1,19      511742             535,29            956                            1         957
                     2319948924            487                          ,04      402425              34,84          51879                         4,49           0
                     1852190137            164                          ,08      274493              127,2           2158                            1        2157
                     1283549268            303                           ,8       13726              36,12            380                            1         380
                     2835506982            448                         1,19        7218              19,15            377                            1         377
                     1822227481            259                          ,03       23226                  3           7742                            1           0
                     4194254847            150                          ,16       14371              15,02            957                            1           0
                     3463613875            298                          ,78        5844              15,38            380                            1           0
                     1113394757              0                            0      260084              10,08         361116                           14           0
                     4219272024             76                          ,03       42277              14,36           2945                            1           0
B_SNAP_ID  E_SNAP_ID         HV MAX_SHARABLE_MEM_KB LAST_SHARABLE_MEM_KB MAX_VERSION_COUNT LAST_VERSION_COUNT DELTA_VERSION_COUNT CLUSTER_WAIT_TIME_SEC
--------- ---------- ---------- ------------------- -------------------- ----------------- ------------------ ------------------- ---------------------
        1          2 3565022785               55,39                55,39                 1                  1                   1                 13,67
                     2319948924               47,44                47,44                 1                  1                   1                  9,38
                     1852190137               55,38                55,38                 1                  1                   1                  2,65
                     1113394757               43,41                43,41                 1                  1                   1                  2,17
                     4194254847               47,64                47,64                 1                  1                   1                  1,26
                     1283549268               59,46                59,46                 1                  1                   1                   ,83
                     2588369535               55,38                55,38                 1                  1                   1                  2,21
                     4212635381               35,51                35,51                 1                  1                   1                  2,21
                     4219272024               27,42                27,42                 1                  1                   1                  1,15
                     2835506982               55,38                55,38                 1                  1                   1                   ,39
                                                                                                                                                       
        2          3 3565022785              113,27               113,27                 2                  2                   1                 15,91
                     2319948924               47,44                47,44                 1                  1                   0                     9
                     1852190137              113,25               113,25                 2                  2                   1                  1,32
                     1283549268              121,42               121,42                 2                  2                   1                  1,42
                     2835506982              113,27               113,27                 2                  2                   1                   ,72
                     1822227481                31,5                 31,5                 1                  1                   0                   1,5
                     4194254847               47,64                47,64                 1                  1                   0                  1,76
                     3463613875               59,63                59,63                 1                  1                   0                   ,81
                     1113394757               43,41                43,41                 1                  1                   0                   ,41
                     4219272024               27,42                27,42                 1                  1                   0                  1,77
B_SNAP_ID  E_SNAP_ID         HV CWT_PERCENT_OF_ELAPSED_TIME AVG_HARD_PARSE_TIME_MS MODULE                      SQL_TEXT
--------- ---------- ---------- --------------------------- ---------------------- --------------------------- ----------------------------------------------------------------
        1          2 3565022785                       16,97                        JDBC Thin Client            BEGIN :1 := orderentry.neworder(:2 ,:3 ,:4 ); END;
                     2319948924                        16,8                        New Order                   SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C
                     1852190137                       18,61                                                    BEGIN :1 := orderentry.browseproducts(:2 ,:3 ,:4 ); END;
                     1113394757                       26,55                        Browse Products             SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C
                     4194254847                       19,72                        New Order                   INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_
                     1283549268                       18,16                                                    BEGIN :1 := orderentry.newcustomer(:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:
                     2588369535                       52,52                                                    BEGIN :1 := orderentry.SalesRepsQuery(:2 ,:3 ,:4 ); END;
                     4212635381                        52,9                        Sales Rep Query             SELECT TT.ORDER_TOTAL, TT.SALES_REP_ID, TT.ORDER_DATE, CUSTOMERS
                     4219272024                       28,85                        New Order                   INSERT INTO ORDER_ITEMS ( ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UN
                     2835506982                       10,53                                                    BEGIN :1 := orderentry.browseandupdateorders(:2 ,:3 ,:4 ); END;
                                                                                                              
        2          3 3565022785                       33,89                        JDBC Thin Client            BEGIN :1 := orderentry.neworder(:2 ,:3 ,:4 ); END;
                     2319948924                       39,39                        New Order                   SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C
                     1852190137                        8,63                                                    BEGIN :1 := orderentry.browseproducts(:2 ,:3 ,:4 ); END;
                     1283549268                       22,26                                                    BEGIN :1 := orderentry.newcustomer(:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:
                     2835506982                       12,06                                                    BEGIN :1 := orderentry.browseandupdateorders(:2 ,:3 ,:4 ); END;
                     1822227481                       28,14                        New Order                   SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAG
                     4194254847                       37,54                        New Order                   INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_
                     3463613875                       17,49                        Update Customer Details     INSERT INTO CUSTOMERS ( CUSTOMER_ID , CUST_FIRST_NAME , CUST_LAS
                     1113394757                        9,98                        Browse Products             SELECT PRODUCTS.PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, C
                     4219272024                       45,53                        New Order                   INSERT INTO ORDER_ITEMS ( ORDER_ID, LINE_ITEM_ID, PRODUCT_ID, UN

 

Final note

Oracle’s sprepins.sql script has a /*+ first_rows */ hint in the inline view containing analytical functions used to compute the current and previous row values from the stats$sql_summary table. The hint is present, but not enabled in statspack_top_sqls.sql. If due to some reason you need to re-enable it, just search for and enable it in the script (or use it’s alternative first_rows(0) ).

Categories: DBA Blogs

Generating and Storing a Report in the Database

Tom Kyte - Fri, 2020-07-31 08:06
>Hi Tom 's team, I am happy for your quick response my old question <b>https://asktom.oracle.com/pls/apex/asktom.search?tag=storing-and-accessing-reports-in-the-database-on-oracle-apex-of-version-20</b> I make a new question for my present problem. I listed those steps I created for that problem: a. I created a table named from file report_archive_tbl.sql on my database or directly on APEX. b. I created a process named store_blobquery_report when press Print button. Print button redirect to url I created for a report query. <code>declare l_report blob; begin l_report := apex_util.get_print_document ( p_application_id => :APP_ID, -- assign id of app or no need p_report_query_name => 'multiquery', -- <b>create name for a report query</b> p_report_layout_name => 'multiquery', -- <b>create name for a report layout</b> p_report_layout_type => 'rtf', -- <b>create a format for a report</b> p_document_format => 'pdf'-- <b>define format for a report<b> ); insert into report_archive ( filename, mimetype, report, created_date, created_by ) values ( 'Multi Query Search Results ('||to_char(sysdate,'DDMonYYYY')||')', 'application/pdf', l_report, sysdate, :USER ); end;</code> <b>c. When I want to click print report, one row were inserted into the database for each report you ran. </b>. That step while I pressed Print button, it only show that report downloaded but on a table of report_archive had no data. Thank you in advance ^_^.
Categories: DBA Blogs

Save compressed BLOB to OS file

Tom Kyte - Fri, 2020-07-31 08:06
Hello - We're doing following in oracle db - Extract comma delimited file from Siebel database using SQL query - we're using BLOB to store comma delimited file - Compressing BLOB data using UTL_compress LN_COMPRESS - Sending data to REST API using HTTP post REST service is erroring out due to Oracle compression and can't read oracle compressed data. Btw, its fine when I zip file manually using winzip and post using POSTMAN. REST API owner would like to know Oracle UTL_compress COMPRESS specification. Also there are asking to send OS file (oracle compressed file) Need to know following... 1)what specification Oracle is using for UTL_COMPRESS 2) How can I create file using Oracle COMRESSED blob? Thanks in advance
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs