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;