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>

Friday, May 30, 2014

ORA-00031, ORA-03113 and ORA-03114

This was tested on Oracle 11.2. I had a session in the middle of a long-running operation: 

SQL> conn user1/user1
Connected.
SQL> exec dbms_lock.sleep(3600);
 
I tried to kill it like this but Oracle gave me an ORA-00031 instead:
 
SQL> select sid, serial# from v$session
  2  where username = 'USER1'
  3  /
 
       SID    SERIAL#
---------- ----------
         7         62
 
SQL> alter system kill session '7,62'
  2  /
alter system kill session '7,62'
*
ERROR at line 1:
ORA-00031: session marked for kill
 
SQL>
 
… and the session carried on regardless:
 
SQL> conn user1/user1
Connected.
SQL> exec dbms_lock.sleep(3600);
 
I really wanted to kill it straight away so I did it like this:
 
SQL> alter system disconnect session '7,62'
  2  immediate
  3  /
 
System altered.
 
SQL>
 
… and the session died there and then:
 
SQL> exec dbms_lock.sleep(3600);
BEGIN dbms_lock.sleep(3600); END;
 
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 15294
Session ID: 7 Serial number: 62
 
SQL>
 
When I tried to carry on using it, Oracle told me I had been disconnected:
 
SQL> select sysdate from dual
  2  /
ERROR:
ORA-03114: not connected to ORACLE
 
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>

20th October 2016:
Checked for relevance.
Shared on LinkedIn

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>

20th October 2016:
Checked for relevance.
Shared on LinkedIn.

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.

The book advertised below is apparently free if you have a connected Android device: