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
Ethiopian calendar support
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
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
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?
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
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
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
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
My Posts on Basic Replication
My series of Blog Posts on Basic Replication :
1. 1. Introduction (Aug-19)
2. 2a. Elements for creating a Materialized View (Aug-19)
3. 2b. Elements for creating a Materialized View (Sep-19)
4. 3. Multiple Materialized Views (Sep-19)
5. 4. Data Dictionary Queries (Sep-19)
6. 5. Refresh Method : FAST or FORCE ? (Sep-19)
7. 6. COMPLETE and ATOMIC Refresh (Sep-19)
8. 7. Refresh Groups (Oct-19)
9. 8. REFRESH_MODE ON COMMIT (Oct-19)
10. 9. BUILD DEFERRED (Oct-19)
11. 10. ON PREBUILT TABLE (Oct-19)
12. 11. Indexes on a Materialized View (Nov-19)
13. 12. MV Refresh Captured in AWR (Feb-20)
14. 13. Some Interesting SYS Tables (Feb-20)
1. 1. Introduction (Aug-19)
2. 2a. Elements for creating a Materialized View (Aug-19)
3. 2b. Elements for creating a Materialized View (Sep-19)
4. 3. Multiple Materialized Views (Sep-19)
5. 4. Data Dictionary Queries (Sep-19)
6. 5. Refresh Method : FAST or FORCE ? (Sep-19)
7. 6. COMPLETE and ATOMIC Refresh (Sep-19)
8. 7. Refresh Groups (Oct-19)
9. 8. REFRESH_MODE ON COMMIT (Oct-19)
10. 9. BUILD DEFERRED (Oct-19)
11. 10. ON PREBUILT TABLE (Oct-19)
12. 11. Indexes on a Materialized View (Nov-19)
13. 12. MV Refresh Captured in AWR (Feb-20)
14. 13. Some Interesting SYS Tables (Feb-20)
Categories: DBA Blogs
Tuning query with function calls in select
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
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
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
