The REPLACE function allows you to change a string of characters to another string of characters and can accept three parameters:
(1) Input column name.
(2) Old string value.
(3) New string value.
You can see what I mean in the example below, which I tested on Oracle 11.2:
SQL> create table directory_name
2 (location varchar2(30))
SQL> insert into directory_name
I tested this example in Oracle 12.1. First I created a table:
SQL> create table fred
2 as select * from user_synonyms
3 where 1 = 2
Then I checked its object_id for later:
SQL> select object_id
2 from user_objects
3 where object_name = 'FRED'
... and described it:
SQL> desc fred
This example shows how to deal with the enq: SQ – contention wait event. It was tested on Oracle 11.2. First I created a sequence and queried the amount of time already spent on this wait event:
SQL> create sequence seq1
SQL> select time_waited/100
2 from v$system_event
3 where event = 'enq: SQ - contention'
no rows selected
This parameter controls how much optimization Oracle does to PL/SQL code when it is compiled.
In Oracle 10, valid values were 0, 1 and 2:
Oracle 10: sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Apr 3 17:41:02 2014
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit (more...)
I discovered a new error today. I was using Data Pump to copy a database from Sun SPARC to X86 and from Oracle 18.104.22.168.0 to 22.214.171.124.0 for testing purposes. I extracted the tablespace creation SQL from the old database like this. (This is only some of it.):
SQL> set lines 32000
SQL> set pages 50
SQL> exec dbms_metadata.set_transform_param(-
A user ran an update statement and noticed that it did not seem to be doing anything:
SQL> conn gordon/bennett
SQL> update andrew.emp
2 set ename = 'COLIN'
3 where ename = 'BRIAN'
I looked up his SID in V$SESSION:
SQL> select sid from v$session
2 where username = 'GORDON'
I tested this on Oracle 11.2. If you try to use a column name more than once in an Oracle table, you get an ORA-00957:
SQL> create table tab1
2 (col1 number,
3 col1 number)
ERROR at line 3:
ORA-00957: duplicate column name
Statistics are important as they help the optimizer to work out the execution plan for a SQL statement. If you move a table, this deletes its statistics so you need to analyze it again afterwards. You can see this in the example below. First I created a table:
SQL> create table object_list
2 as select * from dba_objects
When you create a table it has no statistics so (more...)
This was tested on Oracle 11.2. If you define a column as NOT NULL then try to modify it to NOT NULL again, you get an ORA-01442:
SQL> create table tab1 (col1 number not null)
SQL> alter table tab1 modify (col1 number not null)
alter table tab1 modify (col1 number not null)
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is
already NOT NULL
In this example, Andrew grants access on a table to a role then grants this role to John. John creates a materialized view on Andrew’s table. Andrew updates his table, making John’s materialized view INVALID. John then tries to compile his materialized view, which gives it a COMPILE_STATE of COMPILATION_ERROR. Andrew then grants access to his table directly to John and the problem goes away. It was tested on Oracle 11.2:
This example shows how you can use a logon trigger to automatically trace all sessions for a given user. This can be useful where an application is launched from the desktop and then fails before the DBA has had time to identify the SID and SERIAL# and start tracing it. First create the user:
SQL> conn system/manager@adhoc Connected.
SQL> create user andrew identified by reid
SQL> grant create session,
This was tested on Oracle 11.2. I had an input or target database as follows:
SQL> select name from v$database
1 select tablespace_name, file_name
2* from dba_data_files order by 1
I wanted to create an output or auxiliary or clone database called (more...)
This example was tested on Oracle 11.2. It shows how an on demand materialized view becomes invalid following DML on the underlying table. First I created a table:SQL> create table tab1 (col1 number)
Then I inserted the value 1 into it:
SQL> insert into tab1 values (1)
1 row created.
I created an on demand materialized view on (more...)
This was tested on Oracle 126.96.36.199. If you try to create a database like this i.e. without specifying a SYSAUX datafile clause:
create database extent management local
size 100m autoextend on
group 1 ('/database/NBAPERF/nba_redo1/log1a.rdo',
'/database/NBAPERF/nba_redo2/log1b.rdo') size 50m,
group 2 ('/database/NBAPERF/nba_redo1/log2a.rdo',
'/database/NBAPERF/nba_redo2/log2b.rdo') size 50m
user sys identified by secret_password
user system identified by secret_password