Data Warehousing and Data Science

23 December 2012

Bit and Pieces on Oracle

Filed under: Oracle — Vincent Rainardi @ 6:04 pm

Some things on Oracle proved to be very useful for me. I’ll share them below because I’m sure they are useful for some of you too.

1. Searching for a column name

select * from all_tab_columns where table_name like ‘C%’ and column_name like ‘%ST%’

2. Add/delete a column

alter table account add account_number varchar2(30)

alter table account add (account_number varchar2(30), balance decimal(20,4))

alter table account drop column account_number

3. Change column name / data type

alter table account rename column account_no to account_number

alter table account modify account varchar2(35)

4. Change table name

alter table account rename to customer_account

5. Update a column based on another table

update account set account_number =
( select account_number
from account_mapping
where account_mapping.customer_id = account.customer_id
) where account_number is null

6. Create a table from another table
(this is very useful for backing up before updating)

create table account_bak as
select * from account where 1=2

7. Escaping & (ampersand)

update table1 set column1 = ‘M’ || chr(38) || ‘S’


  1. Good day, Vincent!

    I think (or hope), these two examples will be useful for You

    WITH t AS
    SELECT id_contract
    ,COUNT (*) AS cnt
    ,SUM (COUNT (*)) OVER (ORDER BY id_contract) AS running_cnt
    FROM contract_balance
    GROUP BY id_contract
    SELECT MIN (id_contract) AS from_id
    ,MAX (id_contract) AS to_id
    ,SUM (cnt) AS cnt
    FROM t
    GROUP BY TRUNC(running_cnt/1e7)
    ORDER BY 1 ;

    SELECT a.range_number
    ,MIN(id_contract) AS min_id_contract
    ,MAX(id_contract) AS max_id_contract
    ,COUNT(1) AS rows_count
    FROM (
    SELECT id_contract, NTILE(8) OVER (ORDER BY id_contract) AS range_number
    FROM contract_balance
    ) a
    GROUP BY a.range_number ;

    Table contract_balance is Periodic Snapshot.
    Table contract_balance contains 200 million records.
    This sql queries tested on Oracle 9.2, 10.2g

    Comment by Alexander — 24 December 2012 @ 5:56 am | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

%d bloggers like this: