When working on SSH sessions, you run into some environments that have weird prompts and colors associated with the prompt. Often I find I can’t even see the prompt where it is a dark blue on black. Maybe that is just a sign that I am getting older but it is really difficult to see.

So to change the prompt here are a list of options:

• \a : an ASCII bell character (07)
• \d : the date (Format: “Tue May 26”)
• \D{format} : the format is passed to strftime(3) and the result is inserted into the prompt string; an empty format results in a locale-specific time representation. The braces are required
• \e : an ASCII escape character (033)
• \h : the hostname up to the first ‘.’
• \H : the hostname
• \j : the number of jobs currently managed by the shell
• \l : the basename of the shell’s terminal device name
• \n : newline
• \r : carriage return
• \s : the name of the shell, the basename of $0 (the portion following the final slash)
• \t : the current time in 24-hour HH:MM:SS format
• \T : the current time in 12-hour HH:MM:SS format
• \@ : the current time in 12-hour am/pm format
• \A : the current time in 24-hour HH:MM format
• \u : the username of the current user
• \v : the version of bash (e.g., 2.00)
• \V : the release of bash, version + patch level (e.g., 2.00.0)
• \w : the current working directory, with $HOME abbreviated with a tilde
• \W : the basename of the current working directory, with $HOME abbreviated with a tilde
• \! : the history number of this command
• \# : the command number of this command
• \$ : if the effective UID is 0, a #, otherwise a $
• \nnn : the character corresponding to the octal number nnn
• \\ : a backslash
• \[ : begin a sequence of non-printing characters, which could be used to embed a terminal control sequence into the prompt
• \] : end a sequence of non-printing characters

In order to change the color you will need to add the escape sequence:  \e[x;ym where x = 0 or 1, zero is darker and one is lighter.

• y = 30 – Black, 31 – Red, 32 – Green, 33 – Brown, 34 – Blue, 35 – Purple, 36 – Cyan

So to change your prompt to be Red with user @ machine short name showing the directory in brackets and a mode at the end you would do the following, note that the \e[m at the end of the string effectively ends the color change otherwise the color change will impact everything that isn’t color controlled:

export PS1=’\e[1;31m[\u@\h \W] \$ \e[m’

I had a request the other day to strip out invalid characters from a series of strings.  The easiest way you can do this is to put a list of invalid characters into a string and then use the Translate function to set those characters to null.

In my code I have a Defined variable called null which = ”


Let $invalid_characters = '!!@#$%^&*()-_+=[]|\:;",.<>?''' || chr(123) || chr(125)
Let $newstring = Translate($oldstring,$invalid_characters,{null})


You will notice that there are two exclamation points in the invalid character string, and this is because one exclamation by itself denotes a comment. As well there is three end quotes which allows for a single quote to be put into the string and then end the string. The Chr function allows you to enter in a ascii specific character, which in this case 123 = { and 125 = } — If you try to put the curly bracket into the string it will try to look for a defined variable to insert and it will generate an error. You can use the ascii value to control other characters as well, here is a lookup table to reference from lookuptables.com

Ascii lookup table

This little SQL is a sweet little piece of code I found somewhere in my travels.  It will find duplicate rows of data based on a keyset, however, this only works in Oracle.  I will try to find a similar piece for SQL Server, but for now here we go:

select * from table_name
where rowid in (select min(rowid) from table_name group by key_values having count(*) > 1);

delete from table_name where rowid in (select min(rowid) from table_name group by key_values having count(*) > 1);

ORA-00257: archiver error. Connect internal only, until freed.

If you are loading large volumes of data and you have archive logging turned on sometimes you will run into the ORA-00257 error.  This error occurs when the upper limit of the archive log dedicated space is hit.  You need to clean up some/all of the logs in order to set the database free to work again.

A relatively straightforward way to do this is to use the rman tool:

. oraenv (set your sid/oracle_home)
rman target /
crosscheck archivelog all;  (you should see all the logs)
delete archivelog all; (removes all the logs)
crosscheck archivelog all; (you should see no logs)
exit;

Now you should be able to access the database again without the ORA-00257 error.  Sometimes it maybe necessary to turn the archive logging off during these large loads, see the post on turning off archive logging.

Note: When in ARCHIVELOG mode, the database will make copies of all online redo logs after they are filled. These copies are called archived redo logs. The archived redo logs are created via Oracle’s Archive Process (ARCH). This process copies the archived redo log files to one or more archive log destination directories. In order for this process to be used additional setup is required primarily the redo log destination directories. In non-production environments there can be some major down sides to running in ARCHIVELOG mode.  The most common error you will encounter with Archive Logging turned on is the ORA-00257 error.  It should also be noted that absolutely nothing will process within the system until the archive logs are freed.  This can be a serious issue to an environment when doing maintenance, upgrades, or large data loads.  In those scenarios it is recommended to make backups of the environment and turn archive logging off for the duration of the high traffic work, then again backup and turn archive logging back on if needed.

This is just one of those things that drive a person nuts. Depending on how your environment is configured in UNIX, when you hit the backspace key you will get a ^H key instead of removing the character you wanted to delete.

A quick fix is to issue the following command:

stty erase ^H