 |
 |
| |
Consulting :: We are experts in Oracle Database Solutions with the experience and knowledge to bring any project to completion, applying best practices.
Remote DBA Services :: Database Specialists company provides top of the line expertise in remote database administration backed up by over 15 years of experience.
Database Rx :: Our platform remotely track, monitor and manage client's databases. Our clients can access information and receive 24/7 live support anytime, anywhere. |
|
|
 |
 |
| |
| THE LIGHTER SIDE |
|
Hi Koo To You Too
A haiku is originally a short Japanese poem of three lines with five, seven, and five syllables respectively. A good haiku creates a vivid picture in the reader's mind. Here is an English haiku by Richard Wright.
Whitecaps on the bay:
A broken signboard banging
In the April wind.
Paul Drake from the New York Oracle Users Group imagined the Oracle database giving us haikus instead of error messages.
Instead of "ORA-00904: invalid column name," we might get:
The column you seek
In this schema exists not
Library too new.
Instead of "ORA-01034: Oracle not available," we might get:
The database is
Within itself currently
A patch, cold backup?
Enjoy!
|
| STUMP THE SPECIALISTS |
|
Making Your Database Secure
This month's question came to us from a friend:
"What should I do to make our databases more secure?"
Senior Staff Consultant Iggy Fernandez thoughtfully responds:
- Install only what is required.
- Lock and expire default user accounts.
- Change default user passwords.
- Change default passwords of administrative users.
- Change default passwords of all users.
- Enforce password management.
- Enable data dictionary protection.
- Practice the principle of least privilege.
- Grant necessary privileges only.
- Revoke unnecessary privileges from the PUBLIC user group.
- Grant a role to users only if they need all privileges of the role.
- Restrict permissions on run-time facilities.
- Enforce access controls effectively and authenticate clients stringently.
- Restrict operating system access.
- Restrict network access.
- Use a firewall.
- Never poke a hole through a firewall.
- Protect the Oracle listener.
- Monitor who accesses your systems.
- Check network IP addresses.
- Encrypt network traffic.
- Harden the operating system.
- Apply all security patches and workarounds.
- Contact Oracle Security Products if you come across a vulnerability in Oracle.
I hope this short answer helps you. Best of luck.
|
| SQL CORNER |
|
Fancy SQL Tricks Part I
Pivoting is well known to Excel power users; it converts rows of data into a two-dimensional matrix. For example, here are some rows of data from the STATS$SYSSTAT table:
|
Snap Id
|
Name
|
Value
|
|
41566
|
physical reads
|
57,151,567
|
|
41566
|
session logical reads
|
4,272,750,011
|
|
41566
|
user commits
|
1,006,064
|
|
41566
|
user rollbacks
|
54,483
|
|
41576
|
physical reads
|
61,038,074
|
|
41576
|
session logical reads
|
4,711,935,207
|
|
41576
|
user commits
|
1,082,359
|
|
41576
|
user rollbacks
|
58,524
|
|
41586
|
physical reads
|
65,144,663
|
|
41586
|
session logical reads
|
5,158,057,003
|
|
41586
|
user commits
|
1,159,213
|
|
41586
|
user rollbacks
|
62,607
|
You may want to convert the above data into the following two-dimensional format:
|
Snap ID
|
Logical Reads
|
Physical Reads
|
Transactions
|
|
41566
|
4,272,750,011
|
57,151,567
|
1,060,547
|
|
41576
|
4,711,935,207
|
61,038,074
|
1,140,883
|
|
41586
|
5,158,057,003
|
65,144,663
|
1,221,820
|
You can do this with the PIVOT operator, introduced in Oracle Database 11gR1, as follows:
SELECT
snap_id,
logical_reads AS logical_reads,
physical_reads AS physical_reads,
user_rollbacks + user_commits AS transactions
FROM
(
SELECT *
FROM sysstat
)
PIVOT
(
SUM(value)
FOR NAME IN
(
'session logical reads' AS logical_reads,
'physical reads' AS physical_reads,
'user rollbacks' AS user_rollbacks,
'user commits' AS user_commits
)
);
Here is the Oracle Database 10g version of the above code. It requires a very unintuitive use of the DECODE function.
SELECT
snap_id,
sum(decode(name,'session logical reads',value,0))
AS logical_reads,
sum(decode(name,'physical reads',value,0))
AS physical_reads,
sum(decode(name,'user rollbacks',value,'user commits',value,0))
AS transactions
FROM sysstat
GROUP BY snap_id;
More information on the PIVOT operator and its sister operator, UNPIVOT, can be found in article by Arup Nanda. |
| BULLETIN BOARD |
The Oracle conference season is in full swing.
|
Sincerely,
David Wolff
CEO, Database Specialists, Inc.
dwolff@dbspecialists.com
(415) 344-0500 x48
|
|
|
|
|
 |
|
 |
|
 |
