Posts
Set options for SQLPlus
The following options are useful in SQLplus when spooling to a file. They make sure there are no headers or other stuff in the output that does not execute in SQLPlus. -- Do not print the number of records selected, updated, .. set feedback off -- Do not print column headings set heading off -- Do not print old and new when substituting set show off -- Removes blanks at the end of spooled lines set trimspool on -- surpress output of scripts run with @, @@ or start set termout off -- Setting pagesize to 0 suppresses all headings, page breaks, -- titles, the initial blank line, and other formatting information.
Using while loops with read
If you want to loop over values in a file, the read command in Linux/Unix is very useful. It reads a line from a file, and assigns as many variables as you specify as parameters to the values in that line. Combined with a while loop, you can read the values and process them. Here’s an example : while read customernr do <some_script> $customernr done < customers.txt This loops over all the lines in the customers.
Showing special characters in filename
Sometimes, especially if your terminal is not set correctly, you accidentally create files that have backspace in their name. To see the full name, you can use the -b option to ls : > ls -b gjdhjd\177\177.txt This file has 2 backspace characters in the name, they are shown as \177. That will give you the full name, which you can use to delete or rename the file. If you want to delete these files, you can use the -i option to ls.
Removing all objects for a user
From this post, here’s a way to purge all the objects for a user. You can use this if you don’t have SYS access. select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';') from user_objects You should spool this to a file and then run that file. Then to really remove them, use : purge recyclebin; This works on Oracle 10g2.
