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

• Select
  • Show Tables
• Add Column
• Drop Column
• Rename Column
• Modify Column Data Type
• Copy a Record
• Comment
• Triggers
  • Enable Trigger
  • Disable Trigger

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;