Oracle Notes

New to Oracle but familiar with MySQL?

Read on…

1. “Show create table” in Oracle?
sql> desc table
sql> select dbms_metadata.get_ddl( ‘TABLE’, ‘TABLE_NAME’, ‘OWNER’ ) from dual

note that, use table name and owner are case sensitive.

2. primary key with auto-increment
Oracle does not support primary key with auto-increment property. you need to do it with a sequence and trigger

create table table_name
(
table_nameid number(11,0) PRIMARY KEY NOT NULL,
...

);

— create sequence for primary key
create sequence table_name_seq
start with 1
increment by 1
nomaxvalue
NOCACHE;

— create trigger for primary key
CREATE OR REPLACE TRIGGER table_name_trg
BEFORE INSERT ON DBTABLE_OWNER.table_name
FOR EACH ROW
begin
select table_name_seq.nextval into :new.table_nameid from dual;
end;
/

COMMIT;

replace above table_name and DBTABLE_OWNER with the correct values.

3. Oracle Date column does not display time part/?
You need to turn the LongDate flag on.

4. Show tables in Oracle?
> SELECT owner, table_name FROM all_tables

5. select first N rows.
> select * from table where rownum < 10

6. List database links

SELECT owner,
db_link,
username,
host
FROM dba_db_links
ORDER BY owner, db_link

Leave a Reply

Your email address will not be published. Required fields are marked *

four × 3 =