I will start this post off stating that I know there are better and more efficient methods of doing this but this is the best method I could come up with.
I had a csv file with a number of columns, one of which was data in a timestamp with timezone format.
A few rows looked like this – note I have stripped the other columns out for ease of display.
7941 11/9/2014 (more...)
I received an email from a team member yesterday
Have you seen this? https://docs.oracle.com/database/121/TDPPT/tdppt_awr_warehouse.htm
Interesting idea – ever thought of implementing it?
Well of course I have implemented a AWR data repository and I thought I would catch-up on where I am with it and how it is being used.
The database started out on HPUX on 126.96.36.199 about 2 years ago and is now on Linux on 11. (more...)
In the (good) old days there was only one optimizer and plans were developed based on the precedence of the rules. For some reason I have always thought there were 11 rules but looking at the manual it tells me there are 15 rules
Nowadays the vast majority of databases will be using the CBO and gathering statistics either by using the overnight maintenance functions or manually gathering as required. I suppose there is always (more...)
It is normal for bloggers including myself to post about the great things they have done – however in this case I am blogging about why I was surprised that adding a specific index had the substantial benefits it did have.
The table contains around 32M rows, is not partitioned and is on Linux 188.8.131.52
Name Null? Type
----------------------------------------- -------- ----------------------------
SELL_UNIT_ID NOT NULL VARCHAR2(20)
PRD_CD NOT NULL VARCHAR2(75)
PRD_TYP_CD NOT (more...)
It is long been a concern of mine that it is relatively easy to open a standby database inadvertently and then be potentially liable to a licensing cost as usage is shown in the
In fact we logged an SR on the subject based on experiences on failing over using DataGuard Broker
On dgmgrl switchover the Oracle restart database ‘start option’ property is not modified to ‘mount’ database even though dgmgrl modifies the (more...)
A well configured OEM 12c (or any previous version of your choice) is a fantastic enterprise tool and the reporting capabilities are a major asset.
I am going to share three examples of reports we use and find valuable and if anybody has any other good ideas I am happy to hear about them.
I don’t think I wrote any of them but I almost certainly identified the requirement and instigated the creation of each (more...)
I was recently reading a blog entry by Dominic Brooks regarding auditing and I was intrigued by the line referring to the audit_trail parameter being set to DB, EXTENDED
Behaves the same as AUDIT_TRAIL=DB, but also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available.
Nothing wrong with that and straight from the manual but I was surprised that they were CLOBS. However on looking at them they (more...)
This post runs in parallel with a post I made 5 years ago and which is still the most read one on this blog
It will show how to reduce space taken up in the SYSAUX tablespace by because you have many more AWR snapshots being retained than you think.
Firstly lets take an example database and we can see that we are using 92Gb of space
set linesize 120
Performing an rm -rf operation is normally a simple operation albeit risky if you are in the wrong folder. However within ASMCMD there are a couple of bugs associated with it.
Here are two examples and workrounds
FRA full - warning in the alert log - action needs to be taken - this is an 184.108.40.206 database on OEL (5.8)
ORA-19815: WARNING: db_recovery_file_dest_size of 10484711424 bytes is 100.00% used, and (more...)
Something nice and simple
It is easy to get the current SCN from the database and to map between SCN and timestamp because from 10g onwards there are 2 functions to convert between SCN and TIMESTAMP
In 9i there was also a means of mapping an SCN to a TIMESTAMP. This involves the system view SYS.SMON_SCN_TIME. The view no longer exists in 12c. One limitation is that it only holds 1440 rows (well it (more...)