Sokrates on Oracle

Archive for January, 2012

Workaround for “ORA-00998: must name this expression with a column alias”

Posted by Matthias Rogel on 27. January 2012

Did you ever encounter

ORA-00998: must name this expression with a column alias

and wondered why ?

 

Doc says


Error: ORA 998
Text: must name this expression with a column alias
-------------------------------------------------------------------------------
Cause: An expression or function was used in a CREATE VIEW statement, but no
corresponding column name was specified.
When expressions or functions are used in a view, all column names for
the view must be explicitly specified in the CREATE VIEW statement.
Action: Enter a column name for each column in the view in parentheses after
the view name.

but that is (at most) only half the truth.

Here is my story about ORA-00998:

sokrates@11.2 > create table temp as select count(*) from dual;
create table temp as select count(*) from dual
*
ERROR at line 1:
ORA-00998: must name this expression with a column alias
sokrates@11.2 > REM must ?
sokrates@11.2 > REM I don't think so ...
sokrates@11.2 > REM let's see ...
sokrates@11.2 > create table temp as select * from (select count(*) from dual);

Table created.

sokrates@11.2 > desc temp
Name Null? Type
----------------------------------------- -------- ----------------------------
COUNT(*) NUMBER

sokrates@11.2 > REM hehe !
sokrates@11.2 > select "COUNT(*)" from temp;

COUNT(*)
----------
1

sokrates@11.2 > CREATE VIEW test_v AS
2 SELECT owner || '.' || table_name
3 FROM all_tables
4 WHERE owner = USER;
SELECT owner || '.' || table_name
*
ERROR at line 2:
ORA-00998: must name this expression with a column alias
sokrates@11.2 > create view test_v as
2 select * from (
3 SELECT owner || '.' || table_name
4 FROM all_tables
5 WHERE owner = USER
6 );

View created.

sokrates@11.2 > desc test_v
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER||'.'||TABLE_NAME VARCHAR2(61)

sokrates@11.2 > select "OWNER||'.'||TABLE_NAME" from test_v where rownum=1;

OWNER||'.'||TABLE_NAME
-------------------------------------------------------------
SOKRATES.T

🙂

Advertisements

Posted in sql | 1 Comment »

If you want to sleep well …

Posted by Matthias Rogel on 25. January 2012

… and you are a DBA and you use ASM and you happen to run 11.2 ( but not yet  11.2.0.3 ) …

then, you probably want to apply patch 10422126  ( see http://sve.to/2011/12/22/database-11-2-bug-causes-huge-number-of-alert-log-entries/ and MOS Doc ID 1274852.1 )

I ( and our storage admin ) didn’t sleep well last night 😦

The huge number of logging entries Svetoslav is talking about started for us at 6 pm yesterday, generating about 500 MB / minute.

....
WARNING: Read Failed. group:2 disk:1 AU:98945 offset:1474560 size:8192
path:/dev/oracleasm/disks/RAID5_SPB
 incarnation:0xe9508f84 synchronous result:'I/O error'
 subsys:System iop:0x2b54bc0f8390 bufp:0x2f41b4000 osderr:0x434c5344 osderr1:0x0
....

At midnight, around 180 GB was written, so our partition holding the alert log was full. New Connections received ORA-03113.

No signs of any error in the alert of ASM, no signs of any error from the storage, no signs of weird behaviour of the database (beside extremely logging the same error over and over again)

We cleaned all log- and alert-directories, restarted the database and everything looks ok again (like the last 5 months)

Tomorrow, we will apply patch 10422126.

Posted in 11.2, bugfighting, dba | Leave a Comment »

Small Read Consistency Oddity

Posted by Matthias Rogel on 20. January 2012

Here is a small oddity concerning read consistency.

I doubt it is relevant at all, however, it’s quite amusing.

Statement level read consistency guarantees that every statement sees exactly the data as of the point in time when the statement was opened.

Consider following example:

sokrates@11.2 > select table_name from user_tables where table_name = 'NOT_YET_EXISTENT';

no rows selected

Hence, we expect, that the following CTAS , which I call (T)

sokrates@11.2 > create table NOT_YET_EXISTENT
as
select table_name from user_tables where table_name = 'NOT_YET_EXISTENT';

Table created.

creates a table with no rows in it, since at the point in time when (T) started, there was no table in our schema named NOT_YET_EXISTENT.

sokrates@11.2 > select count(*) from NOT_YET_EXISTENT;

COUNT(*)
----------
0

As expected, (T) respected read consistency.

Now, let’s repeat the same experiment with one small variation.

sokrates@11.2 > drop table NOT_YET_EXISTENT;

Table dropped.

sokrates@11.2 > select object_name from user_objects where object_type = 'TABLE' and object_name = 'NOT_YET_EXISTENT';

no rows selected

So, the following CTAS called (O)

sokrates@11.2 > create table NOT_YET_EXISTENT
as
select object_name from user_objects where object_type = 'TABLE' and object_name = 'NOT_YET_EXISTENT';

Table created.

should also create an empty table.

sokrates@11.2 > select count(*) from NOT_YET_EXISTENT;

COUNT(*)
----------
1

!

Amusing, when (O) started, NOT_YET_EXISTENT was not yet existent, however, its select-part falsely returned one row (which only existed at the point in time when this SQL was finished !)

Obviously, read consistency was violated by (O).

Posted in humour, philosophy | 2 Comments »

V$BLOCKING_QUIESCE and the difference between killing and disconnecting

Posted by Matthias Rogel on 5. January 2012

Testing how to quiesce an instance today:

SID 94 > alter system quiesce restricted;
(15 minutes ago on test-db 11.2 )
Ok, it waits for several sessions to finish, right ?
( see Doc
Oracle Database waits for all existing transactions in all instances that were initiated by a user other than SYS or SYSTEM to finish (either commit or abort). Oracle Database also waits for all running queries, fetches, and PL/SQL procedures in all instances that were initiated by users other than SYS or SYSTEM and that are not inside transactions to finish. … )

So, we queried V$BLOCKING_QUIESCE and killed all sessions listed there via
alter system kill session ....
and saw them disappear in V$BLOCKING_QUIESCE thereafter.

However, after killing the last one we expected SID 94 to finish the quiesce – command, but: it didn’t.


SQL > select * from v$blocking_quiesce;

no rows selected

SQL > select sw.event
2 from v$session_wait sw
3 where sw.sid=94;

EVENT
——————————–
wait for possible quiesce finish

… for several minutes.

bouncing and restarting the instance and repeating the same experiment but using

alter system disconnect session .... immediate

rather than

alter system kill session ....

gave the expected result.

So, when we want to quiesce our instance we’ll use this algorithm.

(We’ll do that only on a  weekend maintenance windows when no interactive user is connected, all batch jobs running will be restartet automatically by our scheduling system when system is unquiesced again.)

But: seems to be a bug to me that V$BLOCKING_QUIESCE showed no entry though quiescing was apparently blocked for minutes.

Posted in 11.2, Bug, dba | Leave a Comment »