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;