DBA Blogs

Adjust connections/sessions Oracle database

Tom Kyte - Fri, 2020-04-03 05:26
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

Tom Kyte - Fri, 2020-04-03 05:26
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

Tom Kyte - Fri, 2020-04-03 05:26
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.

Tom Kyte - Fri, 2020-04-03 05:26
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

Tom Kyte - Thu, 2020-04-02 11:06
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

Tom Kyte - Thu, 2020-04-02 11:06
I require password and username for oracle db developer virtual machine for training purpose
Categories: DBA Blogs

Generate version number

Tom Kyte - Thu, 2020-04-02 11:06
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

Tom Kyte - Thu, 2020-04-02 11:06
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

Tom Kyte - Thu, 2020-04-02 11:06
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?

Tom Kyte - Thu, 2020-04-02 11:06
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

Tom Kyte - Thu, 2020-04-02 11:06
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

Tom Kyte - Wed, 2020-04-01 16:46
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

Pakistan's First Oracle Blog - Tue, 2020-03-31 17:09
“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.
  1. 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

Tom Kyte - Tue, 2020-03-31 04:06
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

Tom Kyte - Tue, 2020-03-31 04:06
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

Tom Kyte - Tue, 2020-03-31 04:06
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?

Tom Kyte - Tue, 2020-03-31 04:06
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

Tom Kyte - Tue, 2020-03-31 04:06
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

Tom Kyte - Tue, 2020-03-31 04:06
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

Bobby Durrett's DBA Blog - Mon, 2020-03-30 10:58

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

Subscribe to Oracle FAQ aggregator - DBA Blogs