Saturday, May 31, 2014

The USER Keyword

Here are a couple of examples with the USER keyword, which I tested on Oracle 11.2. You can use it after the SHOW command or in a SELECT statement as follows. It returns the name of the user running the current session: 

SQL> conn fred/bloggs
Connected.
SQL> show user
USER is "FRED"
SQL> select user from dual
  2  /
 
USER
------------------------------
FRED

SQL>

You can also include it in a WHERE clause to return rows which match the current username. To illustrate this, I created an employee table:

SQL> create table emp_table
  2  (emp_name varchar2(10),
  3   dept     varchar2(10),
  4   salary   number)
  5  /
 
Table created.

SQL>

... then I added 2 rows for the IT department and 2 for Sales:

SQL> insert into emp_table
  2  values('ANDREW','IT',10000)
  3  /
 
1 row created.
 
SQL> insert into emp_table
  2  values('BRIAN','SALES',20000)
  3  /
 
1 row created.
 
SQL> insert into emp_table
  2  values('COLIN','IT',30000)
  3  /
 
1 row created.
 
SQL> insert into emp_table
  2  values('DAVID','SALES',40000)
  3  /
 
1 row created.

SQL>

I created a view to return rows from the employee table in the same department as the user running the current session:

SQL> create view emp_view
  2  as select * from emp_table
  3  where dept =
  4  (select dept from emp_table
  5   where emp_name = user)
  6  /
 
View created.
 
SQL> grant select on emp_view to andrew, brian
  2  /
 
Grant succeeded.

SQL>

... so when Andrew used it, he only saw rows from the IT department where he worked:

SQL> conn andrew/andrew
Connected.
SQL> select * from fred.emp_view
  2  /
 
EMP_NAME   DEPT           SALARY
---------- ---------- ----------
ANDREW     IT              10000
COLIN      IT              30000

SQL>

... and when Brian used it, he only saw data for Sales:

SQL> conn brian/brian
Connected.
SQL> select * from fred.emp_view
  2  /
 
EMP_NAME   DEPT           SALARY
---------- ---------- ----------
BRIAN      SALES           20000
DAVID      SALES           40000
 
SQL>

Thursday, May 29, 2014

FAST Refresh of Materialized View Returns ORA-12004

I tried to do a FAST refresh of a materialized view in Oracle 11.2 but it failed with an ORA-12004:
 
SQL> begin
  2  dbms_mview.refresh('ebase.m_gridpoint2',method=>'F');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12004: REFRESH FAST cannot be used for materialized view
"EBASE"."M_GRIDPOINT2"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 2
 
SQL>
 
I checked the documentation provided by the software supplier. It said I could not do this as the materialized view in question contained a NOT EXISTS clause. I looked at the source of the materialized view and saw that it did:
 
SQL> l
  1  select query
  2  from dba_mviews
  3  where owner = 'EBASE'
  4* and mview_name = 'M_GRIDPOINT2'
SQL> /
 
QUERY
-------------------------------------------------------
SELECT GPT.NR GRIDPOINTNR,
       DECODE (GPT.PRODUCTTYPE,  0, 'E',  1, 'G',  'U')
PRODUCTTYPE,
       GPT.EANCODE EANCODE,
       NAR.EANCODE NETAREAEAN,
       MROL.EANCODE GRIDOPERATOREAN,
       GPT.ROWID GPTROWID,
       NAR.ROWID NARROWID,
       MROL.ROWID MROLROWID
  FROM D_GRIDPOINT GPT,
       D_NETAREA NAR,
       D_MARKETROLE MROL
WHERE GPT.GRIDOPERNR = MROL.NR(+)
AND   GPT.NETAREANR = NAR.NR(+)
AND NOT EXISTS (SELECT 1
                    FROM   D_GRIDPOINTCONNECTION GPC
                    WHERE  GPT.NR = GPC.GRIDPOINTNR)
 
SQL>
 
… so I tried a COMPLETE refresh and it worked:
 
SQL> begin
  2  dbms_mview.refresh('ebase.m_gridpoint2',method=>'C');
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
SQL>

Wednesday, May 28, 2014

GROUP BY and ORDER BY

In 1997, I was sent on a 1 day Introduction to Oracle course. The lecturer said that if you used a GROUP BY, you could not assume that the results would be returned in order. If you wanted to make sure, you should include an ORDER BY as well. From a theoretical point of view, he was correct as an RDBMS is not obliged to return results in any particular order unless you tell it to do so. But from a practical point of view, a simple way to group data is to sort it first. Up to and including version 9, Oracle seems to have used this method:
 
TEST9 > sqlplus /
 
SQL*Plus: Release 9.2.0.7.0 - Production on Fri Sep 2 18:04:16 2011
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
 
SQL> @group_by
SQL> set pages 100
SQL> col first_letter format a12
SQL> select substr(segment_name,1,1) first_letter,
  2  count(*)
  3  from dba_segments
  4  group by substr(segment_name,1,1)
  5  /
 
FIRST_LETTER   COUNT(*)
------------ ----------
1                     1
A                   118
B                    92
C                    98
D                    43
E                    17
F                    51
G                    22
H                    39
I                   293
J                     5
K                     7
L                   115
M                   116
N                     2
O                    29
P                    52
Q                    49
R                   170
S                   319
T                   121
U                    54
V                    16
W                    11
X                    14
_                   119
 
26 rows selected.
 
SQL>
 
This is no longer the case and, starting with Oracle 10, if you want to see the output of a GROUP BY in order, you have to include an ORDER BY as well:
 
TEST10 > sqlplus /
 
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Sep 2 18:15:01 2011
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
 
SQL> @group_by
SQL> set pages 100
SQL> col first_letter format a12
SQL> select substr(segment_name,1,1) first_letter,
  2  count(*)
  3  from dba_segments
  4  group by substr(segment_name,1,1)
  5  /
 
FIRST_LETTER   COUNT(*)
------------ ----------
P                   161
O                    37
V                    83
D                   157
_                    68
M                   448
N                    98
W                   779
Q                    40
Y                     3
C                   135
I                   550
B                   120
F                    55
U                    20
S                   717
T                   250
A                   316
J                    21
E                   143
L                   178
R                   303
H                   662
K                     9
b                     1
G                     7
X                    23
 
27 rows selected.
 
SQL> select substr(segment_name,1,1) first_letter,
  2  count(*)
  3  from dba_segments
  4  group by substr(segment_name,1,1)
  5  order by 1
  6  /
 
FIRST_LETTER   COUNT(*)
------------ ----------
A                   316
B                   120
C                   135
D                   157
E                   143
F                    55
G                     7
H                   662
I                   550
J                    21
K                     9
L                   178
M                   448
N                    98
O                    37
P                   161
Q                    40
R                   303
S                   717
T                   250
U                    20
V                    83
W                   779
X                    23
Y                     3
_                    68
b                     1
 
27 rows selected.
 
SQL>

Tuesday, May 27, 2014

ORA-00361

This was tested on an Oracle 9 database. 

One way to relocate redo log groups is to drop members in one location and recreate them in another. However, if your redo log group only has one member and you want to relocate it, you need to create the new member first. If you don’t do this, you get an ORA-00361 as a redo log group must always have at least one member:
 
SQL> select member from v$logfile
  2  where group# = 4
  3  /
 
MEMBER
----------------------------------------
/cisdpt/misdpt1/mis_redo1/redo4_1.dbf
 
SQL> alter database drop logfile member
  2  '/cisdpt/misdpt1/mis_redo1/redo4_1.dbf'
  3  /
alter database drop logfile member
*
ERROR at line 1:
ORA-00361: cannot remove last log member
/cisdpt/misdpt1/mis_redo1/redo4_1.dbf for group 4
 
SQL>

Monday, May 26, 2014

How I Copied Triggers from one Database to Another

(This happened on Oracle 11.2.) In an earlier example, I started to look at doing a full expdp and impdp. I'm not sure why, but the Data Pump import produced a number of errors. There were well over 200 like this: 

ORA-39112: Dependent object type TRIGGER:"SRCE"."ADDRESS_BIU" skipped, base object type TABLE:"SRCE"."ADDRESS" creation failed 

This message suggested that the SRCE.ADDRESS table had not been created in the output database but, in fact, it had: 

SQL> desc srce.address
Name                       Null?    Type
-------------------------- -------- ------------------
UPDATE_COUNT               NOT NULL NUMBER
ADDRESS_ID                 NOT NULL NUMBER
SUB_PREMISES                        VARCHAR2(30)
PREMISES_NAME                       VARCHAR2(30)
PO_BOX                              VARCHAR2(30)
PREMISES_NUMBER                     VARCHAR2(10)
DEP_THOROUGHFARE                    VARCHAR2(30)
THOROUGHFARE                        VARCHAR2(30)
DOUB_DEP_LOCALITY                   VARCHAR2(30)
DEP_LOCALITY                        VARCHAR2(30)
POST_TOWN                           VARCHAR2(30)
COUNTY                              VARCHAR2(30)
POSTCODE                            VARCHAR2(8)
USES                                NUMBER
LAST_UPD_BY                         VARCHAR2(8)
LAST_UPD_TIME                       DATE
COUNTRY                             VARCHAR2(30)
BUILDING_GROUP_NAME                 VARCHAR2(30)
TCN                                 NUMBER 

However, none of the triggers had been created so my output database was over 200 triggers short, which, as you might expect, stopped it working properly.
 
I tried to extract DDL from the input database to create the triggers as follows but this failed too:

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM -
> (DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
 
PL/SQL procedure successfully completed.
 
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM -
> (DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
 
PL/SQL procedure successfully completed.
 
SQL> select dbms_metadata.get_ddl
  2  ('TRIGGER','ADDRESS_BIU','SRCE') from dual
  3  /
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 13899
Session ID: 95 Serial number: 10069
 
no rows selected
 
SQL> conn / as sysdba
Connected.
SQL> select open_mode from v$database
  2  /
 
OPEN_MODE
--------------------
READ WRITE
 
1 row selected.
 
SQL>
 
I wrote the following script and ran it against the input database:
 
col trigger_body newline
col slash newline
set feedback off
set pages 0
set lines 200
set long 100000
set longchunksize 200
set trimspool on
spool create_triggers.sql
select 'create or replace trigger '||
description,
trigger_body,
'/' slash
from dba_triggers
where owner = 'SRCE'
/
spool off
 
This is part of the SPOOL file:
 
create or replace trigger srce.ptr_product_pk_bi
  BEFORE INSERT ON ptr_product
  FOR EACH ROW
DECLARE
  -- local variables here
BEGIN
  IF :new.product_id IS NULL THEN
    SELECT ptr_product_id_seq.nextval
    INTO   :new.product_id
    FROM   DUAL;
  END IF;
END ptr_product;
/
 
… and when I ran it against the output database, it worked like this:
 
SQL> l
  1  create or replace trigger srce.ptr_product_pk_bi
  2    BEFORE INSERT ON ptr_product
  3    FOR EACH ROW
  4  DECLARE
  5    -- local variables here
  6  BEGIN
  7    IF :new.product_id IS NULL THEN
  8      SELECT ptr_product_id_seq.nextval
  9      INTO   :new.product_id
 10      FROM   DUAL;
 11    END IF;
 12* END ptr_product;
SQL> /
 
Trigger created.
 
SQL>

The SQL in the SPOOL file created the other 200+ missing triggers in the same way. When I get a moment, I will try to understand what caused the problems I had with impdp and dbms_metadata.get_ddl.

Friday, May 23, 2014

sqlplus -prelim

If all the available processes in a database are used up, you will get an ORA-00020 when you try to login. The best way round this is to get some people to log out. However, this may not be possible e.g. if people have logged in from some application then killed it, leaving their sessions alive in the database but with no way to get back to them and end them tidily.
 
Oracle 11: sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 13 15:56:40 2013
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
ERROR:
ORA-00020: maximum number of processes (100) exceeded
 
Enter user-name: ^C
Oracle 11:
 
An alternative is to login with the –prelim option. This does not allow you to run any SQL but it does let you close the database:
 
Oracle 11: sqlplus -prelim / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 13 15:56:59 2013
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
 
SQL> shutdown abort
ORACLE instance shut down.
SQL>

Before opening the database again, you might consider increasing the processes parameter to reduce the chance of this happening again.

Thursday, May 22, 2014

Soundex

I found a booklet called Introduction to SQL Version 1.0, printed in 1985 and 1989. It was written by a certain Lawrence Ellison and described a function called SOUNDEX, which I had never heard of before, so I tried it out on an Oracle 9 database.

SOUNDEX represents the sound of a word by a 4 character alphanumeric code. Words which sound alike should have the same code. This allows you to select rows containing data with a particular sound. Sometimes it works:
 
SQL> select soundex('STEWART') from dual;
 
SOUN
----
S363
 
SQL> select soundex('STUART') from dual;
 
SOUN
----
S363
 
SQL> select soundex('BORED') from dual;
 
SOUN
----
B630
 
SQL> select soundex('BOARD') from dual;
 
SOUN
----
B630
 
SQL>
 
But sometimes it gives words, which sound the same, different codes:
 
SQL> select soundex('WITCH') from dual;
 
SOUN
----
W320
 
SQL> select soundex('WHICH') from dual;
 
SOUN
----
W200
 
SQL>
 
And sometimes it gives words, which sound different, the same code:
 
SQL> select soundex('FLOOR') from dual;
 
SOUN
----
F460
 
SQL> select soundex('FLOUR') from dual;
 
SOUN
----
F460
 
SQL>