CONNECT_TIME

Database Management and Performance
| Feb 20, 2013
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 = (more...)

Challenge Your Colleagues (No 1)

Database Management and Performance
| Feb 18, 2013
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  (more...)

A Simple Test of V$SEGSTAT_NAME and V$SEGMENT_STATISTICS

Database Management and Performance
| Feb 14, 2013
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 (more...)

Problem with utlrp?

Database Management and Performance
| Feb 14, 2013
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
(more...)

Create View … With Check Option

Database Management and Performance
| Feb 13, 2013
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 (more...)

SQL*Loader and ORA-01480

Database Management and Performance
| Feb 7, 2013
This was tested on Oracle 11.2.0.2.7 on Solaris. I created a SQL*Loader control file with a table name which was more than 30 characters long: 

ORCL > cat andrew.ctl
load data
infile 'andrew.dat'
into table andrew7890123456789012345678901
fields terminated by ','
(col1, col2, col3)
ORCL >

Then when I tried to use SQL*Loader, I got an ORA-01480: 

ORCL > sqlldr / control=andrew.ctl
 
SQL*Loader: Release 11.2.0.2.0 - Production on Thu Feb 7 16:06:23 2013
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.