Posted by John Hallas on May 31, 2016
There is a standard for SQL code known as ANSI-92 which is adopted by many of the main RDBMS vendors. However each vendor has its own differences from the standard.
Oracle are currently taking votes on a proposal to add the CREATE ASSERTION statement to Oracle SQL on the OTN community page https://community.oracle.com/ideas/13028
Here are two examples of how the statement could be used
This SQL statement creates an assertion to demand that there’s no more than a single president among the employees:
create assertion AT_MOST_ONE_PRESIDENT as CHECK
from EMP e
where e.JOB = 'PRESIDENT') <= 1
This SQL statement creates an assertion to demand that Boston based departments do not employ trainers:
create assertion NO_TRAINERS_IN_BOSTON as CHECK
(select 'trainer in Boston'
from EMP e, DEPT d
where e.DEPTNO = d.DEPTNO
and e.JOB = 'TRAINER'
and d.LOC = 'BOSTON')
To implement the same functionality now you would use a combination of check constraints and/or triggers.
I have cast my vote in favour
Posted in 11g new features | Tagged: ansi, check constraints, create assertion, sql-92, triggers | Leave a Comment »
Posted by John Hallas on May 17, 2016
This isn’t directly Oracle related but it did come to light within an Oracle context
We had a problem with a password file in a RAC cluster and whilst they appeared to be the same on both nodes of a cluster there were issues. I knew we needed to check the size of the files and ensure they were the same but I could not remember the (l)Unix command.
I knew it was to do with checking the bytes in a file and for some reason I was thinking it was to do with md5. Our sysadmin told me of the apropos command.
The OED defines apropos as meaning “With reference to; concerning:“.
apropos is a wrapper for the man -k command and it lists all commands similar to the parameter passed in.
In my example I typed “apropos sum” and got the following list out and lo and behold there was cksum which I was looking for – along with md5sum
DES_cbc_cksum [des] (3ssl) - DES encryption
DES_quad_cksum [des] (3ssl) - DES encryption
Xmark (1x) - summarize x11perf results
assume_default_colors [default_colors] (3x) - use terminal's default colors
aureport (8) - a tool that produces summary reports of audit daemon logs
cksum (1) - checksum and count the bytes in a file
cksum (1p) - write file checksums and sizes
getrlimit (3p) - control maximum resource consumption
keyctl_assume_authority [keyctl_instantiate] (3) - Assume the authority to instantiate a key keyctl_instantiate - Instantiate a key keyctl_negate - Negatively instantiate a key
md5sum (1) - compute and check MD5 message digest
opreport (1) - produce symbol or binary image summaries
pamsumm (1) - Summarize the samples in a Netpbm image arithmetically
pamsummcol (1) - summarize (sum, average, etc) a Netpbm image by column
repquota (8) - summarize quotas for a filesystem
sa (8) - summarizes accounting information
setrlimit [getrlimit] (3p) - control maximum resource consumption
sha1sum (1) - compute and check SHA1 message digest
sha224sum (1) - compute and check SHA224 message digest
sha256sum (1) - compute and check SHA256 message digest
sha384sum (1) - compute and check SHA384 message digest
sha512sum (1) - compute and check SHA512 message digest
specspo (rpm) - Enterprise Linux package descriptions, summaries, and groups.
sum (1) - checksum and count the blocks in a file
xorg-x11-drv-summa (rpm) - Xorg X11 summa input driver
Posted in Oracle | Tagged: "man -k", apropos, cjsum, md5 | 1 Comment »
Posted by John Hallas on May 16, 2016
Security asked to determine when certain passwords were last changed and I used the ptime column in user$ table to find out.
set lines 120 set pages 60
col name form a30
col Last_Changed form a12
SELECT name,ptime "Last_Changed"
FROM sys.user$ a, dba_users b
order by 1;
Note that you need to join with DBA_USERS otherwise the USERS$ view shows all the roles as well, such as RECOVERY_CATALOG_ROLE and DBA.
You will see that the date shown for most users is 24-AUG-13 which is the date the password was created by the seed database provided by Oracle which is used as part of an install. The accounts with two asterisks have their passwords created as part of user interaction when running DBCA
select name,ctime "Created" , ptime "Last_Changed"
FROM sys.user$ a, dba_users b
and a.name = 'SYS'
order by 1
NAME Created Last_Changed
------------------------------ --------- ------------
SYS 24-AUG-13 13-MAY-16
Posted in Oracle, security | 3 Comments »
Posted by John Hallas on March 23, 2016
I am sure every DBA has used a kill session script before, although a lot probably use OEM as much now as individual scripts.
However I had not realised there was an option to add a 3rd parameter, the instance _id to the command
For example to kill any DBSNMP connections across all nodes of a RAC database
select 'alter system kill session ''' || sid || ', ' || serial# || ', @' || inst_id || ''';'
from gv$session where username = DBSNMP' order by logon_time desc;
alter system kill session ‘975, 57707, @2’;
alter system kill session ‘584, 2515, @1’;
alter system kill session ‘589, 4399, @2’;
alter system kill session ‘401, 2081, @1’;
alter system kill session ‘1168, 36153, @2’;
alter system kill session ‘1350, 21961, @1’;
This parameter is only available on RAC databases – which I suppose is quite obvious really.
There is also an option to use the IMMEDIATE keyword as well, this returns control straight back to the user without waiting for the command to complete, which can be quite useful when you have a few iterations of the command to run
alter system kill session '1350, 21961, @1' immediate;
Posted in Oracle | Tagged: alter system, kil session across instance, kill session | 4 Comments »
Posted by John Hallas on January 26, 2016
After upgrading to SQL Developer 184.108.40.206 a number of users noticed that some connections in the left hand pane were greyed out. I had the same experience for myself. There did not seem to be any pattern as to why specific connections were chosen and not others. Once greyed out they remained greyed out permanently – the selection did not change
I have not been able to find out an answer as to why it is happening, other than it is a bug/feature of that release.
However what I do know is that it is fixable Read the rest of this entry »
Posted in Oracle | Tagged: 4.1.2, color NONE, connection color, greyed out, save password, sql developer | Leave a Comment »
Posted by John Hallas on January 2, 2016
The WordPress.com stats helper monkeys prepared a 2015 annual report for this blog.
Here’s an excerpt:
The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 210,000 times in 2015. If it were an exhibit at the Louvre Museum, it would take about 9 days for that many people to see it.
Click here to see the complete report.
Posted in Oracle | Leave a Comment »
Posted by John Hallas on December 29, 2015
I have looked at the ORAchk tool before and thought that I must do something with it and I have now had a more detailed review of what it offers.
Firstly the history, it started off as RACchk and then morphed into ORAchk with a sibling called EXAchk. The recent release version is 220.127.116.11.5 which is available for MoS Doc_Id 1268927.2
It can perform an ever-growing list of checks against database, OS, specific applications (EBS and Peoplesoft being two)and various permutations of all that. It is very customisable, the usage command output runs to 281 lines !!
There is now the ability to run it as part of an Apex collection so that all reports can be centralised and you can then compare the differences between one run and the previous one.
Let me give an example of the ./orachk –b output. This shows the best practises and I have selected some of the output that seemed worthy of comment. Read the rest of this entry »
Posted in 11g new features, Oracle | Tagged: AWR_FLUSH_EMERGENCY_COUNT, exachk, hcve, health check verification engine, orachk, racchk, rda, session_cached_cursors | Leave a Comment »
Posted by John Hallas on December 24, 2015
I like the memory advisors that are available from the OEM GUI or the direct database tables themselves. If I want a quick overview of whether a memory area (shared_pool, cache or the overall memory target itself) needs reviewing look there first. Note that I did not include PGA here specifically, I am well aware that PGA is included with the MAX_MEMORY_TARGET value – I am not considering that view in my discussion here because it is so subjective to the application and what the users are doing and the code being run.
Here is an example of the OEM advisor for MAX_MEMORY_TARGET for a specific database (Performance/Advisors Home/ Memory Advisors and select advice)
The graph tell us that we have a MAX_MEMORY_TARGET of 4Gb and increasing it will not improve database time (the blue line continues on the same horizontal tangent right through to 8Gb). Read the rest of this entry »
Posted in Oracle | Tagged: amm, automatic memory management, max_memory_target, memory advisors, memory management, v$db_cache_advice, v$sga_resize_ops, v$sga_target_advice, v$shared_pool_advice | 5 Comments »
Posted by John Hallas on December 21, 2015
My team has vacancies for several permanent DBAs. We are a supermarket chain based in Bradford, West Yorkshire.
The estate is split between HPUX and Linux (mostly OEL but moving to RH). The oracle versions in production are primarily 11Gr1,11GR2 and 12c with a little 10g, all managed through OEM
The key requirements are knowledge of DataGuard, RMAN and RAC.
It is an interesting site and we do a lot of work looking at new technology alongside ensuring the production systems run as well as possible.
Any experience of managing MySQL would be an advantage
If interested please send your CV in to the address in this advert
Posted in Oracle, Uncategorized | 1 Comment »