Web Site Copier

This post has nothing to do with Oracle but I thought you might like to know about it anyway.

The problem with a blog, or any web site for that matter, is that you cannot access it without an Internet connection. You cannot access it if it is blocked for any reason either.

I regularly buy PC Plus magazine. It is available from newsagents in the UK. For those of you from elsewhere in the world, you can go to www.pcplus.co.uk and click on the Subscribe tab to have it sent to you.

In the May 2010 issue, I read an article explaining how to download web sites. I then went to http://www.httrack.com/ and downloaded and installed the software. The software is easy to use and the site has documentation in case you get stuck. I used it to download a copy of this blog. It took around 10 minutes and used 120 megabytes of disk space.

The software seems to have done its job and the copy appears to contain all my blog posts. I will be checking it carefully and will update this post with my findings in due course. I then hope to repeat the process from time to time, possibly each time I have added 10 new posts to the blog. Then I can copy the blog onto a USB stick so that I can view it anywhere. The screen prints below show part of the downloaded blog (click to enlarge them and bring them into focus):

Constraints (Part 3)

Go to part 2

This example, tested on an Oracle 11 database, shows a different way to set up a unique constraint:

SQL> col constraint_name format a15

SQL> col constraint_type format a15

SQL> col index_name format a10

SQL> col owner format a10

SQL> col uniqueness format a10

As before, a table is created:

SQL> create table my_table

  2  (my_column number)

  3  /

Table created.

SQL>

Then a check is carried out to ensure there is no constraint or unique index called MY_UK:

SQL> select owner, constraint_name, constraint_type, status

  2  from dba_constraints

  3  where constraint_name = 'MY_UK'

  4  /

no rows selected

SQL> select owner, index_name, uniqueness

  2  from dba_indexes

  3  where index_name = 'MY_UK'

  4  /

no rows selected

SQL>

A constraint is added to the table:

SQL> alter table my_table

  2  add constraint my_uk

  3  unique (my_column)

  4  /

Table altered.

SQL>

This time the new constraint is included in DBA_CONSTRAINTS. Note the constraint_type of 'U' as this is a unique key constraint:

SQL> select owner, constraint_name, constraint_type, status

  2  from dba_constraints

  3  where constraint_name = 'MY_UK'

  4  /

OWNER      CONSTRAINT_NAME CONSTRAINT_TYPE STATUS

---------- --------------- --------------- --------

ANDREW     MY_UK           U               ENABLED

SQL>

There is also an index associated with the constraint:

SQL> select owner, index_name, uniqueness

  2  from dba_indexes

  3  where index_name = 'MY_UK'

  4  /

OWNER      INDEX_NAME UNIQUENESS

---------- ---------- ----------

ANDREW     MY_UK      UNIQUE

SQL>

A row is added to the table:

SQL> insert into my_table values (1)

  2  /

1 row created.

SQL>

And the unique constraint can then be checked by attempting to add the same row again:

SQL> insert into my_table values (1)

  2  /

insert into my_table values (1)

*

ERROR at line 1:

ORA-00001: unique constraint (ANDREW.MY_UK) violated

SQL> select * from my_table

  2  /

 MY_COLUMN

----------

         1

The constraint is disabled:

SQL> alter table my_table

  2  disable constraint my_uk

  3  /

Table altered.

SQL>

This changes its status:

SQL> select owner, constraint_name, constraint_type, status

  2  from dba_constraints

  3  where constraint_name = 'MY_UK'

  4  /

OWNER      CONSTRAINT_NAME CONSTRAINT_TYPE STATUS

---------- --------------- --------------- --------

ANDREW     MY_UK           U               DISABLED

SQL>

And the index disappears:

SQL> select owner, index_name, uniqueness

  2  from dba_indexes

  3  where index_name = 'MY_UK'

  4  /

no rows selected

SQL>

It is then possible to add duplicate data to the table:

SQL> insert into my_table values (1)

  2  /

1 row created.

SQL> select * from my_table

  2  /

 MY_COLUMN

----------

         1

         1

SQL>

Go to part 4 

Sorting (Part 2)

The SQL below shows how many sorts have been done in memory and how many have had to go out to disk since instance startup:

SQL> col name format a20

SQL> l

  1  select name, value

  2  from v$sysstat

  3* where name in ('sorts (memory)', 'sorts (disk)')

SQL> /

NAME                      VALUE

-------------------- ----------

sorts (memory)          1758574

sorts (disk)                  0

SQL>

Constraints (Part 2)

Go to part 1 

The example below was checked on Oracle 9, 10 and 11. First a table was created:

SQL> create table my_table

  2  (my_column number)

  3  /

Table created.

SQL>

The number of constraints was checked:

SQL> select count(*) from dba_constraints

  2  /

  COUNT(*)

----------

      7658

SQL>

An index was added to ensure that there were no duplicate rows in my_column:

SQL> create unique index my_uk

  2  on my_table (my_column)

  3  /

Index created.

SQL>

The number of constraints was checked again but it had not changed. This suggested that creating the unique index had not added an extra constraint to the database:

SQL> select count(*) from dba_constraints

  2  /

  COUNT(*)

----------

      7658

SQL>

An attempt was made to insert duplicate values into my_column:

SQL> insert into my_table values (1)

  2  /

1 row created.

SQL>

The attempt failed but the error message displayed suggested that creating the unique index had, in fact, added a constraint:

SQL> insert into my_table values (1)

  2  /

insert into my_table values (1)

*

ERROR at line 1:

ORA-00001: unique constraint (ANDREW.MY_UK) violated

SQL>

The table was queried to check that the unique index had worked as expected:

SQL> select * from my_table

  2  /

 MY_COLUMN

----------

         1

SQL>

If you genuinely needed to allow duplicates in my_column, how would you do this? You cannot disable the constraint because it does not exist:

SQL> alter table my_table

  2  disable constraint my_uk

  3  /

alter table my_table

*

ERROR at line 1:

ORA-02431: cannot disable constraint (MY_UK) - no

such constraint

SQL>

Although this constraint does not appear in DBA_CONSTRAINTS, you can see what is enforcing it by looking for an index with that name in DBA_INDEXES. It will have UNIQUENESS set to UNIQUE:

SQL> select uniqueness from dba_indexes

  2  where index_name = 'MY_UK'

  3  /

UNIQUENES

---------

UNIQUE

SQL>

Go to part 3 

CPU Used By This Instance

(Tested on an Oracle 10 database.)

The "CPU used by this session" statistic in V$SYSSTAT shows how much CPU time the database has used since it was started. It is recorded in hundredths of a second. First set timed_statistics to false. This stops Oracle incrementing the statistic:

SQL> alter system set timed_statistics = false

  2  /

System altered.

SQL> col value format a10

SQL> l

  1  select value from v$parameter

  2* where name = 'timed_statistics'

SQL> /

VALUE

----------

FALSE

SQL>

Check the starting value:

SQL> l

  1  select value/100 SECONDS from v$sysstat

  2* where name = 'CPU used by this session'

SQL> /

   SECONDS

----------

  14756.13

SQL>

Do some work:

SQL> select count(*) from dba_objects;

  COUNT(*)

----------

     44734

SQL>

Check the statistic again. It will not have changed:

SQL> select value/100 SECONDS from v$sysstat

  2  where name = 'CPU used by this session';

   SECONDS

----------

  14756.13

SQL>

Now set timed_statistics to true:

  1* alter system set timed_statistics = true

SQL> /

System altered.

SQL>

Check the starting value again. It has already gone up a little:

  1  select value/100 SECONDS from v$sysstat

  2* where name = 'CPU used by this session'

SQL> /

   SECONDS

----------

  14756.35

SQL>

Do some work again:

SQL> select count(*) from dba_objects;

  COUNT(*)

----------

     44734

SQL>

Check the statistic again. This time it will have increased:

SQL> select value/100 SECONDS from v$sysstat

  2  where name = 'CPU used by this session';

   SECONDS

----------

  14756.96

SQL>

CPU Used By This Session

Tested on an Oracle 10 database.

This statistic is in V$SESSTAT and can be checked as follows:

(1) Get the number of the statistic from V$SYSSTAT:

SQL> col name format a40

SQL> l

  1  select name, statistic# from v$sysstat

  2* where upper(name) like '%CPU%'

SQL> /

NAME                                     STATISTIC#

---------------------------------------- ----------

recursive cpu usage                               8

CPU used when call started                       11

CPU used by this session                         12

IPC CPU used by this session                     43

global enqueue CPU used by this session          46

gc CPU used by this session                     163

parse time cpu                                  336

OS User level CPU time                          355

OS System call CPU time                         356

OS Other system trap CPU time                   357

OS Wait-cpu (latency) time                      363

11 rows selected.

SQL>

The statistic we want is 12.

(2) Determine the SID you want to look at using the information at your disposal e.g:

  1  select sid from v$session

  2* where username = 'ORACLE'

SQL> /

       SID

----------

        90

SQL>

(3) Use the results from (1) and (2) above to interrogate V$SESSTAT. The value is in hundredths of a second:

SQL> l

  1  select value/100 SECONDS from v$sesstat

  2  where sid = 90

  3* and statistic# = 12

SQL> /

   SECONDS

----------

   1497.63

SQL>

(4) If the session is active, if you wait a while and run the query again, the value will have increased:

SQL> l

  1  select value/100 SECONDS from v$sesstat

  2  where sid = 90

  3* and statistic# = 12

SQL> /

   SECONDS

----------

    1510.9

SQL>

You Cannot Analyze a Table Through a Synonym

Tested on an Oracle 11 database. Note the use of set tab off. The example describes a table with the desc command. The first time I tried this, the output did not line up with the column headings. Adding set tab off beforehand cured the problem:

SQL> set lines 50

SQL> set tab off

SQL> create table andrew1 (one_col number)

  2  /

Table created.

SQL> analyze table andrew1 compute statistics

  2  /

Table analyzed.

SQL> create synonym andrew2 for andrew1

  2  /

Synonym created.

SQL>

You can describe the table via the synonym:

SQL> desc andrew2

 Name                    Null?    Type

 ----------------------- -------- ----------------

 ONE_COL                          NUMBER

SQL>

But the analyze command fails:

SQL> analyze table andrew2 compute statistics

  2  /

analyze table andrew2 compute statistics

              *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

Constraints (Part 1)

(Tested on an Oracle 9 database.)

Constraints are used to validate table data. You can add a constraint when you create a table:

SQL> CREATE TABLE MY_TABLE

  2  (MY_COLUMN NUMBER NOT NULL)

  3  /

Table created.

SQL>

Or you can add one to an existing table:

SQL> ALTER TABLE MY_TABLE

  2  ADD CONSTRAINT MY_CONSTRAINT

  3  CHECK (MY_COLUMN < 5)

  4  /

Table altered.

SQL>

Constraints can check INSERT statements:

 SQL> INSERT INTO MY_TABLE VALUES(1)

  2  /

1 row created.

SQL> INSERT INTO MY_TABLE VALUES(5)

  2  /

INSERT INTO MY_TABLE VALUES(5)

*

ERROR at line 1:

ORA-02290: check constraint

(ORACLE.MY_CONSTRAINT) violated

SQL> INSERT INTO MY_TABLE VALUES(2)

  2  /

1 row created.

SQL> INSERT INTO MY_TABLE VALUES(NULL)

  2  /

INSERT INTO MY_TABLE VALUES(NULL)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into

("ORACLE"."MY_TABLE"."MY_COLUMN")

SQL>

And they can also check updates:

SQL> UPDATE MY_TABLE

  2  SET MY_COLUMN = 6

  3  WHERE MY_COLUMN = 2

  4  /

UPDATE MY_TABLE

*

ERROR at line 1:

ORA-02290: check constraint

(ORACLE.MY_CONSTRAINT) violated

SQL>

When you have finished, only the valid data remains in the table:

SQL> SELECT * FROM MY_TABLE

  2  /

 MY_COLUMN

----------

         1

         2

SQL>

Go part 2 

Truncate (Part 2)

This post illustrates a couple of points made in part 1. First, create a table and remember the MINEXTENTS value:

SQL> create table truncate_test

  2  storage(initial 8k next 8k minextents 3)

  3  as select * from dba_tables

  4  /

Table created.

SQL>
  

Check that the number of extents is greater than the MINEXTENTS value so that we can see the effect of the TRUNCATE statement:

SQL> select count(*) from user_extents

  2  where segment_name = 'TRUNCATE_TEST'

  3  /

  COUNT(*)

----------

         9

SQL>
  

TRUNCATE the table but do not specify either the DROP STORAGE or REUSE STORAGE clauses:

SQL> truncate table truncate_test

  2  /

Table truncated.

SQL>
  

Count the number of extents again. This shows that extents have been deallocated as if the DROP STORAGE clause had been specified, proving that this is the default. Note that the number of extents remaining is determined by the table's MINEXTENTS value:

SQL> select count(*) from user_extents

  2  where segment_name = 'TRUNCATE_TEST'

  3  /

  COUNT(*)

----------

         3

SQL>

Restricted Session

(Tested on an Oracle 9 database.)

You can use the RESTRICTED SESSION facility to control logins to the database. It can be used to prevent normal users from logging into a database while a DBA is carrying out maintenance. There are two ways to do this:

You can do it when you open the database. In the example below, an instance is started twice, once in restricted mode. You can tell if the instance is in restricted mode by querying V$INSTANCE:

SQL> startup open

ORACLE instance started.

Total System Global Area   68423632 bytes

Fixed Size                   733136 bytes

Variable Size              46137344 bytes

Database Buffers           20971520 bytes

Redo Buffers                 581632 bytes

Database mounted.

Database opened.

SQL> select logins from v$instance;

LOGINS

----------

ALLOWED

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup restrict

ORACLE instance started.

Total System Global Area   68423632 bytes

Fixed Size                   733136 bytes

Variable Size              46137344 bytes

Database Buffers           20971520 bytes

Redo Buffers                 581632 bytes

Database mounted.

Database opened.

SQL> select logins from v$instance;

LOGINS

----------

RESTRICTED

SQL>

Or you can do it while the database is running:

SQL> alter system enable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS

----------

RESTRICTED

SQL> alter system disable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS

----------

ALLOWED

SQL>

Enabling restricted session stops new logins except by users with the RESTRICTED SESSION privilege. Existing connections are not terminated (although you can kill them yourself if necessary). In the next example, RESTRICTED SESSION is enabled. Andrew keeps the session he had beforehand but cannot start a new one:

SQL> alter system enable restricted session;

System altered.

SQL> select username from v$session

  2  where username is not null;

USERNAME

------------------------------

SYS

ANDREW

SQL>conn andrew/reid

ERROR:

ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Warning: You are no longer connected to ORACLE.

SQL>

The DBA role has the RESTRICTED SESSION privilege:

SQL> select grantee from dba_sys_privs

  2  where privilege = 'RESTRICTED SESSION';

GRANTEE

------------------------------

DBA

SQL>

I’m not sure why you would want to do this but you can grant the privilege directly to a user. This allows him to login even when the database is in restricted mode:

SQL> conn andrew/reid

ERROR:

ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Warning: You are no longer connected to ORACLE.

SQL> conn / as sysdba

Connected.

SQL> grant restricted session to andrew;

Grant succeeded.

SQL> conn andrew/reid

Connected.

SQL>

RESTRICTED SESSION is an instance level setting so it disappears when you bounce the database (unless you reopen it with the RESTRICT clause as shown above):

SQL> alter system enable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS

----------

RESTRICTED

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area   68423632 bytes

Fixed Size                   733136 bytes

Variable Size              46137344 bytes

Database Buffers           20971520 bytes

Redo Buffers                 581632 bytes

Database mounted.

Database opened.

SQL> select logins from v$instance;

LOGINS

----------

ALLOWED

SQL>