solairis.com

Oracle Notes

This is a cheat sheet of Oracle stuff. It comes from the perspective of someone more accustomed to mysql.

Corrections and easier-ways-of-doing things are definitely welcome.

Select

Show Tables

SELECT TNAME FROM TAB ORDER BY TNAME;

Add Column

ALTER TABLE table_name ADD column_name VARCHAR2(255);

Drop Column

ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name DROP (column_name1, column_name2);

Large deletes can be time consuming. You can mark a column as UNUSED and then remove it after hours:

ALTER TABLE table_name SET UNUSED (column_name);
ALTER TABLE table_name DROP UNUSED COLUMNS;

Rename Column

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Modify Column Data Type

Fat chance. Only a column of all null's can be modified. Here is the only way I found to do it.

ALTER TABLE BADTBL ADD TMPNEWCOL NUMBER(10);

-- UPDATE: (copy data to the new column using piecemeal batch size
-- [not sure how to do this])

ALTER TABLE BADTBL DROP COLUMN OLDCOL;
ALTER TABLE BADTBL ADD OLDCOL NUMBER(10);

-- UPDATE: (copy data to the new column from the temp column using
-- piecemeal batch size [again, not sure how to do this])

ALTER TABLE BADTBL DROP COLUMN TMP TMPNEWCOL;
COMMIT;

Copy a Record

Its often advantageous to copy a record and just change the PRIMAY KEY and. You must create a tmp table, copy the record there, change it, then copy it back and drop the tmp table.

CREATE GLOBAL TEMPORARY TABLE TMPTBL AS SELECT * FROM TBL;
INSERT INTO TMPTBL (SELECT * FROM TBL WHERE PKID=42);
UPDATE TMPTBL SET PKID=86 WHERE PKID=42;
INSERT INTO TBL (SELECT * FROM TMPTBL WHERE PKID=86);
DROP TABLE TMPTBL;
COMMIT;

Comment

COMMENT ON TABLE YOUR_TABLE IS 'leave your comment on table.';
COMMENT ON COLUMN YOUR_TABLE.COLUMN_NAME IS 'leave comment on the column.';

Triggers

Enable Trigger

Turn a trigger on.

ALTER TRIGGER TRIGGER_NAME ENABLE;

Disable Trigger

Turn a trigger off.

ALTER TRIGGER TRIGGER_NAME DISABLE;