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

Ethiopian calendar support

Tom Kyte - Mon, 2020-03-30 09:46
In oracle databse 12c it says it suppirt ethiopian calander.in my database I have one employee table in that I have one column hiredate which date data type until i want to insert ethiopian calander date type data how it is possible?
Categories: DBA Blogs

ADD_MONTHS Function returning last day of the month

Tom Kyte - Mon, 2020-03-30 09:46
The question I have is regarding the ADD_MONTHS function. The live sql link for the test case is as below:- https://livesql.oracle.com/apex/livesql/s/JT1FDGOHXMRWN3LG94GEYLMPJ If we have a look at this link it has 3 components:- 1. A function ...
Categories: DBA Blogs

UTL_FILE write and User permission in Unix directory

Tom Kyte - Mon, 2020-03-30 09:46
I am using UTL_FILE.FOPEN('Unix_Directory_path','FileName.txt','w') in my Pl/Sql package and the file is getting written successfully but the User and permission are different than the file generated using SPOOL, both are using the same batch file in...
Categories: DBA Blogs

Combine create and insert with if-then or case-when in sql?

Tom Kyte - Mon, 2020-03-30 09:46
Hello people, I would like to ask for your help with a simple issue. I am srry if this is too common, but for me it is not easy at all: I have a table like this - p.sometable: <code> ID AMOUNT YEAR cusomter1 42476281.61 ...
Categories: DBA Blogs

No data found extracting information from xml

Tom Kyte - Mon, 2020-03-30 09:46
Why can't you find data in an XML when I have proof of its existence?
Categories: DBA Blogs

Index Rebuild for just 1 partition

Tom Kyte - Mon, 2020-03-30 09:46
Hi, We have partitions in our table. We want to build index for just one partition. If we do that, then does it affects the application? One time we got an error like index in unusable state. First we create index in unusable state like: <code>C...
Categories: DBA Blogs

Paginated queries

Tom Kyte - Mon, 2020-03-30 09:46
Bello, Ask Tom Team. I need to build a report that connects to a 18c Oracle database. There are big tables and I need to paginate the results. I want to show 50 rows per page in the report. I have read about Fetch, Offset features since Ora...
Categories: DBA Blogs

Tuning query with function calls in select

Tom Kyte - Sat, 2020-03-28 02:46
I have 7M rows from legacy system. <code> insert /*+ append */ into TGT_DATA ( LST_NAM, FRST_NAM, MDL_NAM ) SELECT /*+ parallel(10)*/ func_text_clean(LST_NAM), func_text_clean( FRST_NAM), func_text_clean( MDL_NAM) FROM P_ONST; </...
Categories: DBA Blogs

Pass string values to stored procedure

Tom Kyte - Sat, 2020-03-28 02:46
Hello, Ask Tom Team. I have several DELETE statements and I want them all within a stored procedure to simplify execution. All these statements would use different string values in the where condition. DELETE FROM user1.table4 t4 WHERE t4.id...
Categories: DBA Blogs

Basic DynamoDB Data Modelling

Pakistan's First Oracle Blog - Sat, 2020-03-28 01:58



The key differentiator between a NoSQL database like AWS Dynamodb and a relational database like Oracle is of the way data is modelled. In a relational model; data is normalized, de-duplicated and relationships are established between entities. In a NoSQL database like Dynamodb, data can be duplicated and entities can be fused together. This is done to facilitate scalability which is hallmark of today's hyper-scale cloud based applications.


One of the most common relationship within data modelling is One-to-Many. For example, we have 2 entities; Painting and Gallery. A painting can only reside in one gallery, whereas a gallery can have multiple paintings. This is one to many relationship. In relational modelling world, we would have 2 tables; Painting and Gallery, such as:


Museum
Painting

MuseumId
MuseumName
PaintingId
PaintingName
MuseumId
M1
Sofía
P1
Guernica
M1
M2
NSW Gallery
P2
The Great Masturbator
M1
M3
Louvre
P3
Sofala
M2
P4
Mona Lisa
M3


In above One-to-Many relationship, we have joined both Museum and Painting with primary key and foreign key relationship. So e.g. if want to check all paintings in Museum M1, then the query would be:

select p.Title, m.MuseumName from Painting p, Museum m where p.MuseumId=m.MuseumId and m.MuseumId=’M1’;

Now joins are expensive, and they become more expensive as the data grows. In huge cloud scale databases this hampers scalability and at times become impractical to have these joins, hence the NoSQL databases like Dynamodb.

So how do you model One-to-Many relationship in a Dynamodb table when we cannot have joins and aggregations?

We know what the entities are, and we have an access pattern, so now let’s design our table.


MuseumPaintingTable
Primary Key
Attributes
PK
SK
MuseumName
Title
Artist
Museum#M1
Painting#P1
Sofía
Guernica
Picasso
Painting#P2
Sofía
The Great Masturbator
Salvador Dali
Museum#M2
Painting#P3
NSW Gallery
Sofala
Russel Drysdale
Museum#M3
Painting#P4
Louvre
Mona Lisa
Leonardo


If you are coming from a relational world, the above table may seem like blasphemy even though I haven’t yet used different attributes for each item to keep it simple. This is where you need to appreciate the flexibility of NoSQL databases to facilitate scalability.

In above table, we have define a composite primary key, consisting of a partition key and a sort key. A primary key in Dynamodb table defines a unique item, where item corresponds to a row. Remember that all access patterns are driven through the primary key, which means that the entry point for your queries must be the primary key. Every museum and painting has its own unique id, so we have combined them together to generate a unique item.

Note that how we have defined a one-to-many relationship in above table using partition key and sort key within the primary key. For one Museum#M1, we have 2 paintings; Painting#P1 and Painting#P2, and then both of these paintings have their own attributes, which can even differ in structure. We have fused both Painting and Museum entities together.

Now how would you write the previous relational query in DynamoDB, where we wanted to check all paintings in the Museum M1? It would be like this:

aws dynamodb query \
    --table-name MuseumPaintingTable \
    --key-condition-expression "PK = :MID" \
    --expression-attribute-values  '{":MID":{"S":"M1"}}'
               --projection-expression 'Title,MuseumName'

The above command is a RESTful API call to Dynamodb table.
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs