Wednesday, February 20, 2013

CONNECT_TIME

I had to limit a SQL*Plus session’s connection time today while I was setting up a new environment so I decided to document how I did it. The example below was run on an Oracle 11.2.0.2.7 database. First I connected as SYS and limited CONNECT_TIME to 1 minute in the DEFAULT profile: 

SQL> conn / as sysdba
Connected.
SQL> alter profile default
  2  limit connect_time 1
  3  /
 
Profile altered.
 
SQL>
 
Then I set RESOURCE_LIMIT to TRUE so that limits would be enforced:
 
SQL> alter system set resource_limit = true
  2  /
 
System altered.
 
SQL>
 
I created a user and gave it the DEFAULT profile. Then I connected as that user, looked at CONNECT_TIME in USER_RESOURCE_LIMITS then checked the time at 10 second intervals:
 
SQL> create user andrew
  2  identified by reid
  3  profile default
  4  /
 
User created.
 
SQL> grant create session to andrew
  2  /
 
Grant succeeded.
 
SQL> conn andrew/reid
Connected.
SQL> select limit from user_resource_limits
  2  where resource_name = 'CONNECT_TIME'
  3  /
 
LIMIT
----------------------------------------
1
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
 
TIME_NOW
--------
17:31:40
 
SQL> host sleep 10
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
 
TIME_NOW
--------
17:31:50
 
SQL> host sleep 10
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
 
TIME_NOW
--------
17:32:00
 
SQL> host sleep 10
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
 
TIME_NOW
--------
17:32:10
 
SQL> host sleep 10
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
 
TIME_NOW
--------
17:32:20
 
SQL> host sleep 10
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
 
TIME_NOW
--------
17:32:30
 
SQL> host sleep 10
 
Once the session had been connected for 1 minute, the next SQL statement failed and the session was terminated with an ORA-02399:
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
select to_char(sysdate,'hh24:mi:ss')
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02399: exceeded maximum connect time, you are
being logged off
ORA-02399: exceeded maximum connect time, you are
being logged off
 
SQL>
 
… and a subsequent attempt to run the SQL showed that the session was no longer connected:
 
SQL> select to_char(sysdate,'hh24:mi:ss')
  2  time_now from dual
  3  /
select to_char(sysdate,'hh24:mi:ss')
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 884
Session ID: 61 Serial number: 2851
 
SQL>

Monday, February 18, 2013

Challenge Your Colleagues (No 1)

To set up this challenge, you need an Oracle test database. I used one running on Oracle 11.2.0.2.7 but this works on other versions too. First you need to create a user as follows:
 
SQL> create user scott identified by tiger
  2  /
 
User created.
 
SQL> grant create session,
  2  select any table,
  3  execute any procedure to scott
  4  /
 
Grant succeeded.
 
SQL>
 
Then you need to ensure that the database has the following initialization parameter set to TRUE:
 
SQL> l
  1  select value from v$parameter
  2* where name = 'O7_DICTIONARY_ACCESSIBILITY'
SQL> /
 
VALUE
------------------------------
TRUE
 
SQL>
 
Check that your colleagues will be able to connect to the database remotely using SQL*Plus:
 
SQL> conn scott/tiger@orcl
Connected.
SQL>
 
Then E-mail the challenge to them using the following text appropriately modified:
 
Dear Colleagues,
 
I would like to challenge you to reset the SYSTEM password in my test database, explain how you did it and tell me the new password. To solve this challenge, you must connect to the database from SQL*Plus as follows conn scott/tiger@orcl. Answers which involve connecting as any other user will not be allowed.
 
You have until 28th February (or some other date) to do this. All entries must be submitted by E-mail. A prize of a bar of chocolate / bottle of champagne (according to your budget) will be awarded to the first correct answer drawn at random.
 
Good luck
 
Andrew
 
Once the deadline has passed, send out the answer below, inserting the name of the actual winner at the end and award the prize:
 
Dear Colleagues,
 
User Scott had the SELECT ANY TABLE and EXECUTE ANY PROCEDURE system privileges:
 
SQL> conn scott/tiger@orcl
Connected.
SQL> select privilege from user_sys_privs
  2  /
 
PRIVILEGE
----------------------------------------
CREATE SESSION
SELECT ANY TABLE
EXECUTE ANY PROCEDURE
 
SQL>
 
The database had O7_DICTIONARY_ACCESSIBILITY set to TRUE:
 
SQL> conn scott/tiger@orcl
Connected.
SQL> l
  1  select value from v$parameter
  2* where name = 'O7_DICTIONARY_ACCESSIBILITY'
SQL> /
 
VALUE
------------------------------
TRUE
 
SQL>
 
This allowed him to reset the SYSTEM password as follows, which explains why we do not allow you to have the EXECUTE ANY PROCEDURE privilege:
 
SQL> CONN SCOTT/tiger@ORCL
Connected.
SQL> DECLARE
  2  USER_NO NUMBER;
  3  CHANGE_PASSWORD VARCHAR2(50) :=
  4  'ALTER USER SYSTEM IDENTIFIED BY NEW_PASSWORD';
  5  CURSOR_NO INTEGER;
  6  BEGIN
  7  SELECT USER_ID INTO USER_NO FROM ALL_USERS
  8  WHERE USERNAME = 'SYSTEM';
  9  CURSOR_NO:=SYS.DBMS_SYS_SQL.OPEN_CURSOR();
10  SYS.DBMS_SYS_SQL.PARSE_AS_USER
11  (CURSOR_NO,CHANGE_PASSWORD,DBMS_SQL.NATIVE,USER_NO);
12  SYS.DBMS_SYS_SQL.CLOSE_CURSOR(CURSOR_NO);
13  END;
14  /
 
PL/SQL procedure successfully completed.
 
SQL> CONN SYSTEM/NEW_PASSWORD@ORCL
Connected.
SQL> SHOW USER
USER is "SYSTEM"
SQL>
 
The winner of the challenge was John Smith.
 
Kind regards,
 
Andrew

Thursday, February 14, 2013

A Simple Test of V$SEGSTAT_NAME and V$SEGMENT_STATISTICS

This was tested on Oracle 11.2.0.2.7. V$SEGSTAT_NAME shows the names of various statistics which Oracle records. The SAMPLED column shows whether the figures are obtained by sampling or not: 

SQL> l
  1* select * from v$segstat_name
SQL> /
 
STATISTIC# NAME                           SAMPLED
---------- ------------------------------ -------
         0 logical reads                  YES
         1 buffer busy waits              NO
         2 gc buffer busy                 NO
         3 db block changes               YES
         4 physical reads                 NO
         5 physical writes                NO
         6 physical read requests         NO
         7 physical write requests        NO
         8 physical reads direct          NO
         9 physical writes direct         NO
        11 optimized physical reads       NO
        12 gc cr blocks received          NO
        13 gc current blocks received     NO
        14 ITL waits                      NO
        15 row lock waits                 NO
        17 space used                     NO
        18 space allocated                NO
        20 segment scans                  NO
 
18 rows selected.
 
SQL>
 
The statistics themselves are recorded in V$SEGMENT_STATISTICS:
 
SQL> desc v$segment_statistics
Name                       Null?    Type
-------------------------- -------- ------------------
OWNER                               VARCHAR2(30)
OBJECT_NAME                         VARCHAR2(30)
SUBOBJECT_NAME                      VARCHAR2(30)
TABLESPACE_NAME                     VARCHAR2(30)
TS#                                 NUMBER
OBJ#                                NUMBER
DATAOBJ#                            NUMBER
OBJECT_TYPE                         VARCHAR2(18)
STATISTIC_NAME                      VARCHAR2(64)
STATISTIC#                          NUMBER
VALUE                               NUMBER
 
SQL>
 
I thought the row lock waits statistic looked interesting so I decided to test it. First, in the red session below, I created a table, inserted a row into it, committed it then updated it:
 
SQL> conn andrew/reid
Connected.
SQL> create table lock_test
  2  (col1 varchar2(10))
  3  /
 
Table created.
 
SQL> insert into lock_test values ('ANDREW')
  2  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> update lock_test
  2  set col1 = 'BRIAN'
  3  /
 
1 row updated.
 
SQL>
 
Then, in a separate session, shown in blue, I checked if there had been any row lock waits on the table:
 
SQL> conn / as sysdba
Connected.
SQL> select value from v$segment_statistics
  2  where owner = 'ANDREW'
  3  and object_name = 'LOCK_TEST'
  4  and statistic_name = 'row lock waits'
  5  /
 
     VALUE
----------
         0
 
SQL>
 
I started a third session, in green, viewed the contents of the LOCK_TEST table then tried to update it. As you might expect, this session had to wait for the red session above:
 
SQL> conn andrew/reid
Connected.
SQL> select col1 from lock_test
  2  /
 
COL1
----------
ANDREW
 
SQL> update lock_test
  2  set col1 = 'COLIN'
  3  /
 
I returned to the blue session and verified that the row lock waits figure for the table had increased by 1:
 
SQL> l
  1  select value from v$segment_statistics
  2  where owner = 'ANDREW'
  3  and object_name = 'LOCK_TEST'
  4* and statistic_name = 'row lock waits'
SQL> /
 
     VALUE
----------
         1
 
SQL>

Problem with utlrp?

I looked at how you could use utlrp to compile invalid objects in an earlier post: 

SQL> @utlrp
 
TIMESTAMP
----------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2013-02-14 16:09:12
 
DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
 
PL/SQL procedure successfully completed.
 
 
TIMESTAMP
----------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2013-02-14 16:09:18
 
 
PL/SQL procedure successfully completed.
 
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
 
OBJECTS WITH ERRORS
-------------------
                  0
 
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
 
ERRORS DURING RECOMPILATION
---------------------------
                          2
 
 
PL/SQL procedure successfully completed.
 
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error
 
PL/SQL procedure successfully completed.

SQL>

However, I recently had a problem with utlrp where it failed to recompile a package body which had been invalidated:
 
SQL> select object_type, status
  2  from dba_objects
  3  where owner = 'JLS'
  4  and object_name = 'PK_JULES'
  5  /
 
OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             VALID
PACKAGE BODY        INVALID

SQL>

When I recompiled it manually, it had no errors. I will update this post once I understand the cause of the problem. In the meantime, if you have any idea what caused this to happen, please let me know:
 
SQL> alter package jls.pk_jules compile
  2  /
 
Package altered.
 
SQL> select object_type, status
  2  from dba_objects
  3  where owner = 'JLS'
  4  and object_name = 'PK_JULES'
  5  /
 
OBJECT_TYPE         STATUS
------------------- -------
PACKAGE             VALID
PACKAGE BODY        VALID
 
SQL>

Wednesday, February 13, 2013

Create View ... With Check Option

This post was tested on Oracle 11.2. It shows how to use the WITH CHECK OPTION clause of the CREATE VIEW command. The following two steps create a table and a view on a subset of that table i.e. rows where first_name begins with "A": 

SQL> create table t1
  2  (first_name varchar2(10))
  3  /
 
Table created.
 
SQL> create view v1
  2  as select first_name from t1
  3  where substr(first_name,1,1) = 'A'
  4  /
 
View created.
 
SQL>


Oracle allows you to use a view to insert rows which that view would then be unable to select. You can see this below where the view is used to insert a row for "BRIAN". This row can be selected from the table, which shows that it was successfully inserted. However, you cannot select it using the view because first_name does not begin with "A": 

SQL> insert into v1 select 'ANDREW' from dual
  2  /
 
1 row created.
 
SQL> insert into v1 select 'BRIAN' from dual
  2  /
 
1 row created.
 
SQL> select * from t1
  2  /
 
FIRST_NAME
----------
ANDREW
BRIAN
 
SQL> select * from v1
  2  /
 
FIRST_NAME
----------
ANDREW
 
SQL> 


This may not be what you want. For example, you would not expect an administrator in the IT department to be able to insert rows for the Sales department. Let's delete the rows from the table and try something different:

SQL> delete t1
  2  /

2 rows deleted.

SQL>

This time the WITH CHECK OPTION clause is added at the end of the CREATE VIEW statement:

SQL> create or replace view v1
  2  as select first_name from t1
  3  where substr(first_name,1,1) = 'A'
  4  with check option
  5  /

View created.

SQL>

This creates a constraint on the view, not the table. It has a system generated name:

SQL> select constraint_name
  2  from dba_constraints
  3  where table_name = 'V1'
  4  /
 
CONSTRAINT_NAME
------------------------------
SYS_C00121817
 
SQL>

With the constraint in place, you can still use the view to insert a first_name which begins with "A":


SQL> insert into v1 select 'ARTHUR' from dual
  2  /
 
1 row created.
 
SQL>

But if you try to use it to insert a first_name beginning with "B", the value is rejected:


SQL> insert into v1 select 'BARRY' from dual
  2  /
insert into v1 select 'BARRY' from dual
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
 
SQL>

... and only the valid value remains in the table:


SQL> select * from t1
  2  /
 
FIRST_NAME
----------
ARTHUR
 
SQL> select * from v1
  2  /
 
FIRST_NAME
----------
ARTHUR
 
SQL>

If you do not like the system generated name, you can choose a name for the constraint yourself like this:


SQL> create or replace view v1
  2  as select first_name from t1
  3  where substr(first_name,1,1) = 'A'
  4  with check option constraint c1
  5  /

View created.

SQL> select constraint_name
  2  from dba_constraints
  3  where table_name = 'V1'
  4  /

CONSTRAINT_NAME
------------------------------
C1

SQL>