DBA Blogs
Adjust connections/sessions Oracle database
Hello, Ask Tom Team.
I have a production database running for several months. These are my connection parameters in the client to connect to the database (RAC 18c). I am using a .NET app.
Min Pool Size=1;Max Pool Size=60;Connection Lifetime=18...
Categories: DBA Blogs
Regarding Oracle Error ORA-26002 table has index defined upon it
Hi There,
This is regarding the error ora-26002 which we are facing since few days.
We have one table T1 which is having an index on one of the column.
This table T1 is being loaded through informatica using bulk mode option. (Without droppi...
Categories: DBA Blogs
Oracle 18c SQL Query Pagination
Hello, Ask Tom Team.
I am building a report using Microsoft SQL Server Reporting Services connecting to an Oracle 18c database.
This report will show lots of data. I need to paginate the results, showing 50 rows per page.
1. How can I do i...
Categories: DBA Blogs
Online Table move to different table space.
Team,
we are on 18c and trying to move a table to a different table space, using the online move it got failed like this.
<code>c##rajesh@PDB1> alter session set ddl_lock_timeout =300;
Session altered.
c##rajesh@PDB1> show parameter ddl_l...
Categories: DBA Blogs
Database Link Issues: Adding schema name raises ORA-02002: error while writing to audit trail ORA-00942
I have a database link that connects to a databases that is running 11g (11.2.0.4.0).
The database it is created in was recently upgraded to 19c. After the upgrade, I can no longer access remote tables using fully qualified table names.
Prior ...
Categories: DBA Blogs
VM username and password
I require password and username for oracle db developer virtual machine for training purpose
Categories: DBA Blogs
Generate version number
How to Generate version Number using oracle SQL like rownum??
Need Result Like this <b>0.7,0.8,0.9,1.0,1.1,1.2</b> upto 4.5
Categories: DBA Blogs
Second run of calling webservice using UTL_HTTP fails with ORA-12535: TNS:operation timed out
Hi Tom,
Please help me in addressing this issue.
I am using the below procedure to call HTTPS wsdl, this works fine for the first time when calling this procedure on SQL prompt logging into DB server(AIX) via Putty.
But fails when I try to ex...
Categories: DBA Blogs
Timestamp validation against current timestamp
Hello,
I am a newbie developing an app on APEX 19.2. I have a table with a field <b>START_DATE</b> of data type <i>TIMESTAMP WITH LOCAL TIME ZONE</i>.
I also have a form with this field, for which I have specified <b>Identification Type</b> as Da...
Categories: DBA Blogs
How "ONLINE" is an "alter table move ONLINE" in 12.2 and beyond?
Good Morning,
It seems like Oracle has made some significant improvements to the "alter table move ONLINE" command in 12.2. For a basic heap table that is made up of varchar, char and number data types with a few b-tree indexes, it seems like we ...
Categories: DBA Blogs
Oracle 11g select query with Json field
I want to fetch data from below table.
Table Name: Insight
<code>Id | Name | JSON_DATA
----+------------+-------------------------------------------------------------
1 | Sitansu. | {"country_codes": ["IND"], "regions": ["ASIA"]...
Categories: DBA Blogs
Plain SQL text on cloud platform - SQL Injection and Data security standards
Hello TOM,
I need to know if query through plain SQL text is accepted on cloud platform from data security standards perspective.
How about SQL injection bugs/security risk for on cloud platform?
Plain sql (like exec reporting_view_generato or s...
Categories: DBA Blogs
How To: Reduce Cloud Bill in Half for Any Cloud Provider
“But you said, we would be only paying what we use then how come my Cloud bill is way more than my on-prem cost?”
Managements are now asking above question from Cloud Operations team all across the globe as recession looms, revenues drop, businesses almost to a standstill, budgets shrunk and economic outlook becoming grimmer by the day.
Above statement about Pay-as-you-go is still true about the cloud but it doesn’t happen just by migrating to cloud. It has to be configured, monitored, managed and audited. If you start a virtual machine in the cloud, use it for 9 to 5, leave it running until next morning, then you will be charged for 24 hours instead of just from 9 to 5 where you were supposedly “using” it. Running in cloud means using, and you pay for it. Same is also true for managed database services in the cloud.
Following are top 3 things you can do right now to significantly reduce your cloud bill.
- Control Resource Creation:
Resource Creation privileges must be given to a handful team in your organization, and this team should be doing it through pipeline after proper approvals. So revoke resource creation rights from everyone else, and let one team be doing it through pipeline with a single click.
2. Tag Resources
Tag your resources, and no resource in cloud should live without following tags:
StartTime, StopTime, LifeSpan
These tags are self-explanatory. StartTime will tell, when this resource will start, when it will stop, and how long this will remain in cloud. You can either write your own serverless functions to enforce these timings, or use any third party or cloud-native solution to implement it. (Drop me a line, if you want further guidance on this). This point is a game-changer for your bill especially for VMs and managed databases.
3. Remove the Clutter
No matter how little it costs; if its not needed or you cannot find the owner, just delete it. Orphan persistent disk volumes, ownerless compute images, dangling object stores, and all stray services must be stopped and deleted.
If followed, these 3 points can reduce your cloud bill to half or even more in any cloud provider setup. Cloud’s honeymoon period is over, and with all this belt tightening around, its time to save.
Categories: DBA Blogs
Distributed and in-doubt transactions
Hello Ask Tom, Team.
I am struggling with distributed transactions. Oracle RAC 18c (18.6) and .NET app.
I want to know a few things:
The RECO background process of an Oracle Database instance automatically resolves failures involving distri...
Categories: DBA Blogs
Uploading files in a Rest webservice using PlSql UTL_HTTP
Hi,
I'm calling REST web services using PLSQL UTL_HTTP package, in JSON, with no problems.
However, now i need to invoke a WS but attaching a file.
I can do it with Postman in ?form-data? mode.
In PLSQL, I've tried the same thing in the...
Categories: DBA Blogs
OGG-02030 Failed to set logmining server parameters back to default val
I`ve try to start integrated Extract:
<code>REGISTER EXTRACT EXTR_R DATABASE
ADD EXTRACT EXTR_R, INTEGRATED TRANLOG, begin now
ADD EXTTRAIL ./dirdat/lt, EXTRACT EXTR_R
START EXTRACT EXTR_R</code>
with param file:
<code>Extract EXTR_R
USERIDA...
Categories: DBA Blogs
What happens when there is no space in 1 diskgroup in ASM?
Hi,
We have DB that is using ASM with normal redundancy. The space consumption is really high. There was an occasion when almost 1 TB space was consumed in a month.
We have been adding disks when there the remaining space gets really low. Now, we ...
Categories: DBA Blogs
500 internal server error
We have an application in Oracle APEX where we do not have a large database and the number of pages used in the application would be max 250. However, there is a 500 Internal Server error that we are encountering since the last 3-4 days and we have n...
Categories: DBA Blogs
Analytic Functions Drawbacks
Hi Tom,
Analytic functions are really cool, and I can see great benefits from using it. Despite its benefits, does Analytics Functions has any drawbacks, like performance issues, or any details one should be aware of when choosing to use analytics...
Categories: DBA Blogs
January 2020 Patches Not In Recommended Patch Advisor
Every quarter I download the latest patch set from Oracle’s support site by using the Recommended Patch Advisor. But the January 2020 patch sets did not show up. The RPA still recommends the October 2019 patch sets. The Recommended Patch Advisor is one of the options under the Patches and Updates tab in Oracle’s support site. I tried searching Oracle’s support site and Googling but did not find any answers. I am sure that the information is out there somewhere but the way I was searching for it did not find it. So, I thought I would post a quick note about it. The short answer is that you should use this Oracle support document to find the latest patch sets and not the Recommended Patch Advisor:
Master Note for Database Proactive Patch Program (Doc ID 756671.1)
To find this out I had to open an SR with Oracle Support. They quickly identified this document which explained the situation:
My Oracle Support: Patch and Health Recommendations No Longer Supported (Doc ID 2644769.1)
Note 2644769.1 seems to say that the Recommended Patch Advisor will continue to refer to the October 2019 patches until Oracle removes the RPA from their support site in May.
The funny thing is that I knew we had an internal document that a coworker and I had both contributed to that describes how to download the latest patch set and apply it to a database to create a tar or zip that we could use to deploy a new database. I decided to modify that document to refer to note 756671.1 and remove references to the RPA. To my surprise my coworker had already included note 756671.1 months ago in our instructions and not the RPA. I think that Oracle support must have recommended the note some time ago, but I was still using the advisor. I guess you learn something new every day.
Anyway, note to self: don’t use the Recommended Patch Advisor to find the quarterly patch updates. Use note 756671.1.
Bobby
Categories: DBA Blogs
Pages
