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>

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 28^th 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

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>

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>