Oracle: How to reorganize data in a tablespace

There is actually no easy way to reorganize the data in a tablespace in Oracle11g.
You can't just use a command like ALTER TABLESPACE REORGANIZE or REORGANIZE TABLESPACE because there is no.

TNS-12508: TNS:listener could not resolve the COMMAND given

TNS-12508: TNS:listener could not resolve the COMMAND given

this error could indicate that you have typed the wrong command in lsnrctl or it could indicate that the parameter:

 

ADMIN_RESTRICTIONS_YOURLISTENERNAME = ON

is set in listener.ora (located in $ORACLE_HOME/network/admin) and you don't have permission to adminster the listener. With this parameter set it is only the user oracle that is allowed to run commands against the listener.

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