Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies.
When relevant, I also write about other technologies, like Linux or PostgreSQL.
Many of the posts starts with "how to" since they derive directly from my own personal experience.
My goal is to provide simple examples, so that they can be easily adapted to other situations.
Here is how to create an STS and populate it with SQL statements matching a particular SQL ID:
exec dbms_sqltune.create_sqlset(sqlset_name=>'mysts');
DECLARE
my_cur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN my_cur FOR
SELECT VALUE(x)
FROM TABLE( dbms_sqltune.select_cursor_cache(basic_filter=>'sql_id = ''45wmakdh9ak9s''')) x;
dbms_sqltune.load_sqlset(sqlset_name => 'mysts', populate_cursor => my_cur);
END;
/
Check the contents of the STS:
set lines 200
col name format a20
col owner format a20
col created format a30
col statement_count format 999999999999
select name,owner,created,statement_count
from dba_sqlset
where name = 'mysts';
And check the details of the statements packed in the STS, like this:
select sql_id,plan_timestamp,parsing_schema_name,plan_hash_value,round(elapsed_time/1000000) "duration in seconds",cpu_time,buffer_gets,disk_reads,executions
from dba_sqlset_statements
where sqlset_name = 'mysts'
fetch first 3 rows only;
A customer wanted me to look into why his two databases running the exact same application code were returning different time zones.
select name, created,(select dbtimezone from dual) "dbtimezone"
from v$database;
NAME
CREATED
dbtimezone
testdb1
25.04.2016 16:40:12
+02:00
NAME
CREATED
dbtimezone
testdb2
08.11.2017 14:07:51
+01:00
The os-command date +"%Z %z" returned CEST +0200 on both servers. Why the difference?
It didn't take long to find a good answer from Oracle about this topic.
Oracle states:
The database timezone is the time zone of the location where the database is installed
and
Use the SET TIME_ZONE clause to set the time zone of the database. If you do not specify the SET TIME_ZONE clause, then the database uses the operating system time zone of the server.
And also, from the document "Timestamps & time zones - Frequently Asked Questions (Doc ID 340512.1)":
If not specified with the CREATE DATABASE statement, the database time zone defaults to the server's O/S time zone offset. Note that if your server runs in a time zone affected by DST, then the database time zone would default to whatever the current offset is at the time of database creation, so you can have databases created with the same script on the same server with a different database time zone.
In Norway, we use DST, more commonly referred to as "summertime" and "wintertime". The first datbase was installed April 25th, after the server had switched to summertime. The second database was installed November 8th, after the server had switched to wintertime.
Oracle continues with the following about the database time zone:
The database time zone is not as important as it sounds. First of all it does not influence functions like sysdate, or systimestamp.
These function take their contents (date and time, and in the case of systimestamp also time zone) completely from the OS without any "Oracle" intervention.
and
A common misconception is that the database timezone needs to be "your" timezone. This is NOT true.
The database timezone has NO relation with "where" the server is located.
There is NO advantage whatsoever in using your timezone or a named timezone as database timezone.
The best setting for dbtimezone is simply +00:00 (or any other OFFSET like -09:00, +08:00, ...),
if your current dbtimezone value is an OFFSET then please leave it like it is.
In Version 11gR1 Oracle introduced the Active Diagnostic Repository (ADR), which serves as a common repository for all log files, tracefiles, incidents etc, that the database produces.
ADR has a "short" and a "long" purging policy. The default values are:
• 720 hours (30 days) for SHORTP_POLICY
• 8760 hours (356 days) for LONGP_POLICY
Each policy controls different types of content:
These files are controlled by the value of LONGP_POLICY:
ALERT
INCIDENT
SWEEP
STAGE
HM
These files are controlled by the value of SHORTP_POLICY:
TRACE
CDUMP
UTSCDMP
IPS
Sometimes it may be necessary to manually purge the ADR. The following example will delete all trace files older than 48 hours:
ORA-14402: updating partition key column would cause a partition change
occurs when you try to update the value of a partition key column.
This will lead to an implicit INSERT operation.
The ROWID of the row was assigned when the row was first inserted, and it doesn't change. Therefore, by default, Oracle will reject any DML that will cause a rowid to be changed.
Example:
update sales.quarterly_sales
set sold_month = '2016-02'
WHERE unique_sales_id = '3d6fb1ad-243d-4435-97d8-9ca4bfde3ab5';
Since the partition key is the column "sold_month", it will return ORA-14402.
The workaround is to allow row movement for the table:
alter table sales.quarterly_sales enable row movement;
You can now update the row.
You may want to revoke the permission for the rows in the table to change rowid when you are finished:
alter table sales.quarterly_sales disable row movement;
Further reading:
Doc ID 1518567.1 "FAQ: Row Movement Common Questions and Problems on Partitioned Tables"
Doc ID 236191.1: "Updating Partition Key Column Fails with ORA-14402"
The following query reveals that my FRA contains archived logs from the last 3 days only (indicated by an "A" - available).
-- Set your NLS_DATE_FORMAT in your session to avoid ORA-01830: date format picture ends before converting entire input string
alter session set NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';
select trunc(completion_time),status,count(*)
from v$archived_log
where completion_time > to_date('20.06.2018 00:00:00')
group by trunc(completion_time),status
order by 1 desc;
Output:
TRUNC(COMPLETION_TIME)
STATUS
COUNT(*)
29.06.2018
A
37
28.06.2018
A
57
27.06.2018
A
54
27.06.2018
D
1
26.06.2018
D
75
25.06.2018
D
75
24.06.2018
D
53
23.06.2018
D
55
22.06.2018
D
59
21.06.2018
D
58
20.06.2018
D
54
Archive log files prior to these, have been deleted (indicated by a "D") by rman after having been successfully backed up.
