Oracle practical tips

Oracle practical tips

1. Flashback

Note: delete the "T_SPC_ROOM" table, but you can use the following command to restore the table without commit.

 

flashback table MW_APP.T_SPC_ROOM to before drop

2. Handling special characters

 

NOTE: If special characters such as & are inserted, the Oracle prompt is a variable. There are two methods:

 

select 'abc'||chr(38)||'efg' from dual;select 'abc'||'&'||'efg' from dual;

 

3. Import or export

Description: imports or exports a table.

 

imp system/system@sgtms file=e:\datatable\t_pub_tables.dmp fromuser=(mw_app) touser=(mw_app) exp system/system@sgtms tables=mw_app.T_MAINTAIN_UNIT file=e:\T_MAINTAIN_UNIT121101.dmp log=e:\T_MAINTAIN_UNIT121105.log

4. Delete objects of a user in the current year

--delete all tablesselect 'drop table ' || table_name ||';'||chr(13)||chr(10) from user_tables;   --delete all views select 'drop view ' || view_name||';'||chr(13)||chr(10) from user_views;   --delete all seqs select 'drop sequence ' || sequence_name||';'||chr(13)||chr(10) from user_sequences;  --delete all functions select 'drop function ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='FUNCTION';   --delete all procedure select 'drop procedure ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='PROCEDURE';   --delete all package select 'drop package ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='PACKAGE';