Sometimes interesting problems lead to shock or dismay at the suppositions of why they occur. Why an ORA-22979 is raised is one of those, and the error is typically:
ERROR at line 1:
ORA-22979: cannot INSERT object VIEW REF OR user-defined REF |
This error (more...)
Somebody was trying to create a striped view based on a table’s start_date and end_date temporal columns. They asked for some help, so here are the steps.
Basically, you create a user-defined data type, or structure:
1
2
3
4
| CREATE OR REPLACE TYPE item_structure IS OBJECT
( id NUMBER
(more...) |
It’s funny but Oracle doesn’t want you to enter a trivial password, and about every week I get asked what the standards are for Oracle Database passwords. That’s funny too because it’s in the documentation, the one most experienced and new users never read – Oracle Database Installation Guide (available by platform, the link is for the Windows platform).
Anyway, let me quote the rules:
Oracle recommends that the password you specify:
- Contains at least one lowercase letter.
- Contains at least one uppercase letter.
- Contains at least one digit.
- Is at least 8 characters in length.
- Uses the database character (more...)
Last October I posted an example and description of a common_lookup table. It was intended to show how common_lookup tables support drop down selections in web forms. However, it wasn’t adequate to show how they work with existing data, and the function only supported fresh queries.
This post goes to the next level, and shows how to use foreign keys to preselect values for display in web forms. It also rewrites the prior function so that it supports querying existing data and inserting new data.
Let’s start with data stored in join between two tables – the member and contact (more...)
Last week I reviewed “Oracle Database XE 11gR2 Jump Start Guide” (Packt Publishing, 2012) in this post. Packt guys did like the review, and today I am pleased to announce that I will be giving away two free e-copies of the book. All you need to do is just comment below the post and win [...]
About a year ago Packt Publishing approached me and offered to write a book on (one of the technologies in) Oracle 11g. After careful consideration I had to decline the offer. Which was the right decision as I have hardly had any spare time in the past 12 months. However, Packt guys have kept in [...]
Finally I had some time to upgrade my Oracle XE database from version 10 to version 11, something I wanted to try out but could never find the time to do it. I wanted to try it out, to see if it all worked as advertised, usually it does. This time however I ran into some problems with the export and import of the APEX applications, good thing it was just on my sandbox database.
The first step in the documentation (link at the bottom of this blogpost) is to log in as SYS and run the gen_inst.sql script
(more...)
Hi folks!
I have extended the Undo usage scripts to include two additional indicators:
1) undo change vector size statistics
2) Used undo records/blocks
and support for RAC infrastructure, so you can spot the hungriest UNDO eaters for any given instance.
Then the script for Oracle 11g is as follows:
set pagesize 400
set linesize 140
col name for a25
col program for a50
col username for a12
col osuser for a12
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname (more...)
Yesterday I had the pleasure of attending a session by Connor McDonald. I heard a lot about him, his presentation style, and I
even did a book review back in 2005. Everybody I talk to who attended a session by Connor - ever - is always very positive on his presentation style. And it is very impressive, humor, technical knowledge, all the ingredients are there to keep you focused on the content. The opening slide, he put on as you walked in the room invited you to move forward as the presentation has code sample in a fontsize which he
(more...)
Riddle me this: how long is the following string:
to_char (10, '0999')
Did you answer four? Then, sorry to say, you are wrong. The correct answer is five.
SQL> select '['||to_char (10, '0999') ||']'
2 from dual
3 /
'['||TO
-------
[ 0010]
As you can see, there is a space at the beginning of the string. This space is reserved for the sign (either a plus "+" or a minus "-" ). If you don't want this in your output, you can do something like this
SQL> select '['||to_char (10, 'fm0999') ||']'
2 from dual
3 /
'['||TO
-------
(more...)
Some time ago, if you said you had installed Linux and, heavens forbid, Oracle on Linux, you’d be considered an utter geek. Fast forward 15 year. These days, when people (who have experience with other databases and operating systems) ask me about Oracle/Linux, I feel somewhat embarrassed. Let me illustrate what I mean. Install Oracle [...]
When you have two Oracle databases and want to exchange data between them, it is easily done via database links. So for your local database you would go select * from local_table and for the remote one select * from remote_table@link_to_remote_db But what if the remote database isn’t Oracle, what if it’s (for example) Microsoft [...]
Ver este articulo en EspañolSorry I’ve been having a lot of work installing, maintaining and migrating to Database Machines all over Latinamerica for the latest 10 months; that means lot of travels abroad, all of them very exciting, but the real excitement comes from my job working with this wonderful Machine.
Was on the last project when realized how important is this investment for our customers, having this big caribbean Telco installed their first Exadata and later migrated their databases meant for them dramatic performance improvements and the ability to get more up-to-date analytical information and compete (more...)
Last week’s article on 1Z0-058 questions was quite popular, more popular than I expected. So if you enjoyed the questions, now it’s time to get some answers! (1) Kevin is a database administrator at Fictional Real Estate Co. All he hears everyday is location, location, location. He is asked by one of the Sales team [...]
If you get emails from Oracle University, you might have received a notification that 1Z1-058 (1Z0-058 beta) “Oracle 11g R2 RAC Administration” exam is now available. It earns you an 11g R2 RAC Oracle Certified Expert (OCE), but you need to be an 11g DBA Oracle Certified Professional (OCP) first to get that. If you [...]
A couple of weeks ago we talked about Flash on Android via Oracle APEX. Today let’s check the competitor’s offer. Unfortunately Apple products don’t support Flash. The good news however is that they do support SVG, as do most web browsers (IE7/8 is pluginable and IE9 is going to support it properly). I first came [...]
RSS