Oracle RAC how to start or stop cluser database with srvctl command

These commands are useful when handling the Oracle RAC databases. They can be executed from any of the nodes in the cluster:

$ srvctl start database -d dbName
$ srvctl stop database -d dbName

$ srvctl start instance -d dbName -i dbName1

$ srvctl start instance -d dbName -i dbName2

$ srvctl stop instance -d dbName -i dbName1

$ srvctl stop instance -d dbName -i dbName2

Oracle how to create table with "invisible" name

In Oracle it is possible to create a table with no name (just space):

create table " " 
   (col1 int,
     col2 varchar2(10));


Then you can use the following to select data:


select * from " ";

Oracle: How to create random test data multiple rows easy

To create a table with 100,000 rows with random values just type:

create table tableName (columnName1 number, columnName2 number);

insert into tableName (select rownum, dbms_random.value(1,100)

                        from dual

                      connect by level <= 100000);

commit;

Oracle: how to page / paging result in select

Paging in Oracle is not as easy as one could think.
If you would like your query to return lets say 100 rows with the start at row 1300:

select * from (
  select rowum as rn, b.*
    from (select a.columnName1, a.columnName2
            from tableName a
           order by a.columnName1) b
        ) c
    where c.rn between 1300 and 1300 + 100

Oracle: How to avoid duplicate rows in query result while selecting from table

If you select data from one column in a table like this:

select columnName
  from tableName;


columnName
=========
A
A
A
B
B

you would get that columns data from all rows. If you would like to avoid / skip duplicates (and only get the distinct values) you could type:

select distinct columnName

  from tableName;

columnName

=========

A

B

Another approach is:

select columnName, count(*)

  from tableName

  group by columnName;

columnName   count(*)

=========    ========

A            3

B            2

oracle sql skip rows

What is it really you want to do? Skip rows? Do you mean delete?

Or skipping rows while selecting?

select * from tablename where columnName != 'Peter';

Oracle: how to alter quota for a user

You can use the alter user-statement to change the amount of space that a user can utilize in a tablespace:

alter user userName quota 100M on temp;


alter user userName quota 0M on system;


If the user has the system privilege "unlimited tablespace" it overrides the quota on each tablespace and gives unlimited quota.

Oracle: How to check quota on tablespace for users

To check the quota on tablespace for users you can use the following sql:

select username,

       tablespace_name,bytes/(1024*1024) as used_mb,

       max_bytes/(1024*1024) as max_mb

   from dba_ts_quotas;

How to set unlimited quota on tablespace to oracle user

You can set the quota when you create the user like this:

create user username identified by password

  default tablespace tablespaceName

  temporary tablespace temp

  quota unlimited on tablespaceName;

Or You can alter the user after creation like this:

alter user username
   quota unlimited on tablespaceName; 

How to connect Oracle SQL Developer to mySql

Download the drivers you need to connect
mySql :                      mysql-connector-java-5.0.8-bin.jar
Sybase, SQL Server: jtds-1.2.jar


In the menu of SQL Developer, select Tools -> Preferences -> Database -> Third Party JDBC Drivers.
Click Add Entry, and point out your specific JAR file. 


Now you are ready to connect to your databases.

Oracle SQL Developer even known as Raptor

Oracle SQL Developer is Oracle´s free tool for developers and dba´s that need a quick and easy way to connec t to their databases. Oracle SQL Developer, also known as Raptor, can connect to other databases to: Microsoft SQL Server, mySql and Sybase.

The link to download this product: http://www.oracle.com/technology/software/products/sql/index.html

Oracle: How to avoid duplicate rows in table while inserting and updating data

There are several ways to avoid storing duplicate rows in an Oracle database when inserting and updating data. In the examples I use a table with two columns that should be unique:

1. Unique keys

ALTER TABLE tableName
    ADD CONSTRAINT uniqueKeyName UNIQUE(columnName1, columnName2);

2. First select and then insert or update

procedure insertRow(p_key1 in number, p_key2 in number, p_value in number)
is
  cursor c1(v1 in number, v2 in number) is
     select '1' from tableName
       where columnName1 = v1
            and columnName2 = v2;
  dummy varchar2(1);
begin
  open c1(p_key1, p_key2);
  fetch c1 into dummy;
  if sql%found then
    update tableName
       set valueColumn = p_value
       where columnName1 = p_key1
            and columnName2 = p_key2;
   else
      insert into tableName (columnName1, columnName2, valueColumn)
             values (p_key1, p_key2, p_value);
   end if;
end;
/

3. The MERGE-statement

MERGE INTO tableName tn
  USING dual on (dual.dummy is not null
                 and tn.columnName1 = p_key1
                 and tn.columnName2 = p_key2)
WHEN MATCHED THEN
  UPDATE set tn.valueColumn = p_value
WHEN NOT MATCHED THEN
  insert (columnaName1, columnName2, valueColumn)
     values (p_key1, p_key2, p_value);

4. update and then insert

procedure insertRow(p_key1 in number, p_key2 in number, p_value in number)

is

begin

  update tableName
     set valueColumn = p_value
    where columnName1 = p_key1
        and columnName2 = p_key2;
  if sql%rowcount = 0 then
    insert into tableName (columnName1, columnName2, valueColumn)
        values (p_key1, p_key2, p_value);
  end if;
end;
/

or

update tableName

     set valueColumn = p_value

    where columnName1 = p_key1

        and columnName2 = p_key2;

insert into tableName (columnName1, columnName2, valueColumn)
      select p_key1, p_key2, p_value
         from dual
        where not exists (select '1'
                          from tableName
                         where columnName1 = p_key1
                           and columnName2 = p_key2);

5. first delete and then insert

procedure insertRow(p_key1 in number, p_key2 in number, p_value in number)
is
begin
  delete from tableName
     where columnName1 = p_key1
         and columnName2 = p_key2;

   insert into tableName (columnName1, columnName2, valueColumn)
        values (p_key1, p_key2, p_value);

end;
/

Oracle: How to remove duplicate keys found in table

If you have been trying to add a unique constraint or a primary key to an Oracle table and you receive the annoying error:

ORA-01452:	cannot CREATE UNIQUE INDEX; duplicate keys found
Cause:	A CREATE UNIQUE INDEX statement specified one or more columns that currently contain duplicate values. All values in the indexed columns must be unique by row to create a UNIQUE INDEX.
Action:	If the entries need not be unique, remove the keyword UNIQUE from the CREATE INDEX statement, then re-execute the statement. If the entries must be unique, as in a primary key, then remove duplicate values before creating the UNIQUE index.

Now you have the following to consider:

1. Maybe you have to redesign the datamodel and tables to allow duplicate keys in the table. Maybe they are there for a reason...

2. Create the primary/unique key without enforcing validation of the old existing data in the table with:

     CREATE UNIQUE INDEX indexName
      ON tableName (columnName)
      NOVALIDATE;

   This makes the unique constraint created but the existing data in the table is not validate according to the rule of being unique.

3. Find a way to remove the duplicates

If you select to remove the duplicate rows you have several ways to do this:

3.1. single sql-statement

    delete from tableName t1
      where t1.rowid != ( select min(t2.rowid) 
                            from tableName t2
                           where t2.columnName = t1.columnName );

3.2. plsql-program


3.3. create index statement exceptions into


The exceptions table can be created with the following script:
$ORACLE_HOME/rdbms/admin/utlexcpt.sql


Now try to add the unique constraint:

   alter table tableName
      add constraint constraintName unique (colummName1, columnName2)
      exceptions into exceptions;


The rowid's for the duplicate rows will be stored in the exceptions table and they can be used with an join to delete the dupplicate rows in the source table.


Take a "backup" of the rows which are to be deleted:


   create table tableNameCopy as select * from tableName
        where rowid in (select row_id from exceptions);


Delete the duplicate rows:


   delete tableName where rowid in (select row_id from exceptions);

ORA-22856: Cannot add columns to object tables

If you get the error ORA-22856: Cannot add columns to object tables when trying to add columns to a table in Oracle9i, you propably have hit a bug.

The bug makes it impossible to add columns to a table in compressed state.

The solution is to alter the table to nocompress and rebuild it with the ALTER TABLE MOVE-statement before adding the columns.