Export Oracle users to text file

This is achieved by using the sys.dba_users table, which means these queries must be done using an account with proper privileges. Beyond creation of users, the second SQL statement in the example below also generates account-locking commands for those users that are currently marked as locked. Finally, note the output file has the extension .sql, which suggests that it can be run directly from SQL*Plus as part of an automated script, if necessary.

set linesize 256;
spool c:oracleUsers.sql;

select 'create user '||username||' identified by values '||''''||password||''''||' 
default tablespace '||default_tablespace||' 
temporary tablespace '||temporary_tablespace||' 
quota unlimited on '||default_tablespace||' 
quota unlimited on '||temporary_tablespace||';' as "-- create_users"
from sys.dba_users 
where default_tablespace not in('SYSAUX','SYSTEM')
order by username;

select concat(concat('alter user ',du.username),' account lock;') as "--lock_users"
from sys.dba_users du
where du.account_status<>'OPEN'
and du.default_tablespace not in('SYSAUX','SYSTEM')
order by du.username;

spool off;

Manually deleting an Oracle 11g database

The following steps outlines how to manually delete a database within Oracle 11g, running in an Linux environment. I am currently running Oracle’s Enterprise Linux release 5 update 2.

  • Use SQL*Plus or your favorite SQL command line tool to log in to the target database; log in as sysdba
  • Issue the following commands to get the path to various files
    select name from v$datafile;
    select member from v$logfile;
    select name from v$controlfile;
    select name from v$tempfile;
    
  • Shut down the database within SQL*Plus, and then exit out of SQL*Plus
    shutdown immediate;
    quit;
    
  • Stop the listener service by issuing the following command in the terminal prompt:
    lsnrctl stop
  • Recall the file paths found earlier; use them as a check list as you delete each one of those files. Reminder: did you back up your database yet? When ready, go ahead delete your data files, redo log files, control files, and temp files.
  • Go to the $ORACLE_HOME/network/admin directory and delete the tnsnames.ora and listener.ora files
  • Go to the $ORACLE_HOME/dbs directory and delete the init[db_name].ora, orapw[db_name], and spfile[db_name].ora files
  • Go to the $ORACLE_HOME/admin/[db_name]/pfile directory and delete the init[db_name].ora file
  • Go to the $ORACLE_HOME/admin/[db_name]/adump directory and delete all files in that directory
  • Edit the file /etc/oratab by removing the line that starts with your database name

Get dates corresponding to calendar week days in Oracle

To do so, we will make use of Oracle’s next_day() function. The sample code is as follows:

select next_day(sysdate-14,'SUNDAY') as week_start, next_day(sysdate-7,'SATURDAY') as week_end from dual;

Assuming the current date is 20 Aug 2008, the above code will return 10 Aug 2008 and 16 Aug 2008, reflecting that the previous calendar week is between Sunday the 10th and Saturday the 16th. Thus, your query to get records inserted in the previous calendar week may look something like the following.

select record_id, record_stuff, entry_date
from bunch_of_records
where entry_date between 
	next_day(sysdate-14,'SUNDAY') and 
	next_day(sysdate-7,'SATURDAY');

Custom split() function in Oracle

The following function will take in a list, let’s say “AAA,BBB”, split them up to “AAA” and “BBB”, and allow the user to specify which one to return.

create or replace function split(input_list varchar2, ret_this_one number, delimiter varchar2)
return varchar2
is
	v_list varchar2(32767) := delimiter || input_list;
	start_position number;
	end_position number;
begin
	start_position := instr(v_list, delimiter, 1, ret_this_one);
	if start_position > 0 then
		end_position := instr( v_list, delimiter, 1, ret_this_one + 1);
		if end_position = 0 then
			end_position := length(v_list) + 1; 
		end if;
		return(substr(v_list, start_position + 1, end_position - start_position - 1));
	else
		return NULL;
	end if;
end split;
/
show errors;

In the previous listed example, we would run this function as follows.

select split('AAA,BBB',1,','); -- Returns AAA
select split('AAA,BBB',2,','); -- Returns BBB

Please note that the first index is 1, not 0.

This function is inspired and based on work done by Simon Baird and Francois Degrelle. Thanks Simon and Francois!

Drop columns in an Oracle table

The following example demonstrates how it could be done.

alter table schema1.my_table drop column unused_field;

There is also an alternative syntax that could be used to drop multiple fields at once.

alter table schema1.my_table drop (unused_field); -- Dropping one field only
alter table schema1.my_table drop (unused_field1, unused_field2); -- Dropping two fields

In the second example, if you need to drop more than two fields, just keep adding them within the parenthesis, separated by commas.

Custom is_number() and is_date() functions in Oracle

To do so, create this following two functions. The first function tests for numbers, and the second tests for dates. You should be sure to change the schema name to the correct value for your environment.

create or replace function schema1.is_number(in_var in varchar2)
return varchar2
is
  v_number number;
begin
  select to_number(in_var) into v_number from dual;
  return 'Y'; -- No exception, so is a number
exception
  when others then
  return 'N'; -- is not a number
end;

create or replace function schema1.is_date(in_var in varchar2, in_format in varchar2)
return varchar2
is
  v_date date;
begin
  select to_date(in_var, in_format) into v_date from dual;
  return 'Y'; -- No exception, so is a number
exception
  when others then
  return 'N'; -- is not a number
end;

Below are two samples on how to use our newly created is_number() function.

select schema1.is_number('pete') from dual;
select schema1.is_number(123) from dual;

The first query will result “N” because “pete” is not a number. The second query will return “Y”.

Below are four samples on how to use our newly created is_date() function.

select schema1.is_date('pete','mm/dd/yyyy') from dual;
select schema1.is_date('2-MAY-2008','mm/dd/yyyy') from dual;
select schema1.is_date('05/99/2008','mm/dd/yyyy') from dual;
select schema1.is_date('05/02/2008','mm/dd/yyyy') from dual;

The first query will result “N” because “pete” is not a date. The second also returns “N” because the date entered does not conform to the format specified. The third also will return “N” because the date, although formatted correctly, is not a valid date. Finally, we will have “Y” for the last test.

Oracle SQL case expression

The example below retrieves a sample list of employees, their job roles, and how many hours they have billed to a particular customer.

select e.emp_name, e.job_role, sum(b.hours) from employees e, billing b where e.emp_id=b.emp_id and b.customer_id=126 group by e.emp_name, e.job_role;

emp_name  job_role      sum(b.hours)
--------- ------------- ------------
Sandy     attorney      10
Bob       programmer    4
Jill      programmer    8
John      accountant    32
Barbara   secretary     20
Danny     proj manager  8

The company has a simple billing rule. Attorney hours are billed at $200 per hour, programmers at $100 per hour, accountants at $100 per hour, and all others at $25 per hour. We can use the case statement to help us derive how much to bill the customer.

select e.emp_name, e.job_role, sum(b.hours),  
case when job_role='attorney' then sum(b.hours)*200
  when job_role='programmer' then sum(b.hours)*100
  when job_role='accountant' then sum(b.hours)*100
  else charge=sum(b.hours)*25
end as charge
from employees e, billing b where e.emp_id=b.emp_id and b.customer_id=126 group by e.emp_name, e.job_role;

emp_name  job_role      sum(b.hours)  charge
--------- ------------- ------------- -------
Sandy     attorney      10            200
Bob       programmer    4             400
Jill      programmer    8             800
John      accountant    32            3200
Barbara   secretary     20            500
Danny     proj manager  8             200

Of course, this is just a hands-on illustration of the case expression in Oracle SQL. In real life, it is arguable that this type of business logic should probably be written elsewhere, perhaps with the base rates stored in a table, so that it can be easily modified if need be.

Pivot table with Oracle SQL

Often, we store data in a normalized way in our database, perhaps something that resembles key-value pairs. To illustrate, let us see the following table, which describes properties of computers.

select comp_id, comp_property, comp_property_value from schema1.comp_properties;


COMP_ID COMP_PROPERTY COMP_PROPERTY_VALUE
------- ------------- -------------------
1       hostname      comp1
1       ipaddress     192.168.0.1
1       os            Windows XP SP2
1       hdd1          60gb
1       hdd2          500gb
2       hostname      comp2
2       ipaddress     192.168.0.2
2       os            MacOS 10.5
2       hdd1          80gb
3       hostname      comp3
3       os            Ubuntu Linux 7.10

In this particular case, we can see that the three computers have different properties. While in this example of three computers we can see the information easily, we can also imagine that it will become cumbersome if we are managing 100 computers instead of only three. Thus, a pivot table type of presentation would be much more useful.

To rewrite the SQL statement to present the same data in a pivot table manner, we can do the following.

create or replace schema1.v_comp_properties
select comp_id, hostname, ipaddress, os, hdd1, hdd2
from (
	select comp_id,
	max(case when comp_property='hostname' then comp_property_value else null end) as hostname,
	max(case when comp_property='ipaddress' then comp_property_value else null end) as ipaddress,
	max(case when comp_property='os' then comp_property_value else null end) as os,
	max(case when comp_property='hdd1' then comp_property_value else null end) as hdd1,
	max(case when comp_property='hdd2' then comp_property_value else null end) as hdd2
	from schema1.comp_properties
	group by comp_id
);

The result should look like the following.

select comp_id, hostname, ipaddress, os, hdd1, hdd2 from schema1.v_comp_properties;

COMP_ID HOSTNAME IPADDRESS   OS                HDD1 HDD2
------- -------- ----------- ----------------- ---- -----
1       comp1    192.168.0.1 Windows XP SP2    60gb 500gb
2       comp2    192.168.0.2 MacOS 10.5        80gb
3       comp3    192.168.0.3 Ubuntu Linux 7.10

With this pivot table, we will be able to see all properties of each computer on each row easily, while still maintain a normalized database design.

Writing SQL output to CSV with VBScript

Enter your specific information in the “Configuration” section near the top of the script. For the “dbType” variable, the only accepted values are “oracle”, “sqlserver”, or “mysql”. Once this is done, just run the script and you should have your quote-delimited comma-separated CSV file!

The email-related variables are optional. To enable the emailing functionality (send the generated CSV file to the address as an attachment), enter the recipient email address in the box. If you do not wish to email, just leave that variable as empty string (“”), and the other email related variable such as smtp and smtpPort will be ignored.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Data Exporter                                                 '
'                                                               '
' Description: Allows the output of data to CSV file from a SQL '
'       statement to either Oracle, SQL Server, or MySQL        '
' Author: C. Peter Chen, http://dev-notes.com                   '
' Version Tracker:                                              '
'       1.0   20080414 Original version                         '
'	1.1   20080807 Added email functionality                '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
option explicit
dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, email, subj, body, smtp, smtpPort, sqlstr

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbType = "oracle"                 ' Valid values: "oracle", "sqlserver", "mysql"
dbHost = "dbhost"                 ' Hostname of the database server
dbName = "dbname"                 ' Name of the database/SID
dbUser = "username"               ' Name of the user
dbPass = "password"               ' Password of the above-named user
outputFile = "c:output.csv"      ' Path and file name of the output CSV file
email = "email@me.here"           ' Enter email here should you wish to email the CSV file (as attachment); if no email, leave it as empty string ""
  subj = "Email Subject"          ' The subject of your email; required only if you send the CSV over email
  body = "Put a message here!"    ' The body of your email; required only if you send the CSV over email
  smtp = "mail.server.com"        ' Name of your SMTP server; required only if you send the CSV over email
  smtpPort = 25                   ' SMTP port used by your server, usually 25; required only if you send the CSV over email
sqlStr = "select user from dual"  ' SQL statement you wish to execute
'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''



dim fso, conn

'Create filesystem object 
set fso = CreateObject("Scripting.FileSystemObject")

'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
if dbType = "oracle" then
	conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False")
elseif dbType = "sqlserver" then
	conn.open("Driver={SQL Server};Server=" & dbHost & ";Database=" & dbName & ";Uid=" & dbUser & ";Pwd=" & dbPass & ";")
elseif dbType = "mysql" then
	conn.open("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & dbHost & ";PORT=3306;DATABASE=" & dbName & "; UID=" & dbUser & "; PASSWORD=" & dbPass & "; OPTION=3")
end if

' Subprocedure to generate data.  Two parameters:
'   1. fPath=where to create the file
'   2. sqlstr=the database query
sub MakeDataFile(fPath, sqlstr)
	dim a, showList, intcount
	set a = fso.createtextfile(fPath)
	
	set showList = conn.execute(sqlstr)
	for intcount = 0 to showList.fields.count -1
		if intcount <> showList.fields.count-1 then
			a.write """" & showList.fields(intcount).name & ""","
		else
			a.write """" & showList.fields(intcount).name & """"
		end if
	next
	a.writeline ""
	
	do while not showList.eof
		for intcount = 0 to showList.fields.count - 1
			if intcount <> showList.fields.count - 1 then
				a.write """" & showList.fields(intcount).value & ""","
			else
				a.write """" & showList.fields(intcount).value & """"
			end if
		next
		a.writeline ""
		showList.movenext
	loop
	showList.close
	set showList = nothing

	set a = nothing
end sub

' Call the subprocedure
call MakeDataFile(outputFile,sqlstr)

' Close
set fso = nothing
conn.close
set conn = nothing

if email <> "" then
	dim objMessage
	Set objMessage = CreateObject("CDO.Message")
	objMessage.Subject = "Test Email from vbs"
	objMessage.From = email
	objMessage.To = email
	objMessage.TextBody = "Please see attached file."
	objMessage.AddAttachment outputFile
	
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtp
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = smtpPort
	
objMessage.Configuration.Fields.Update
	
	objMessage.Send
end if

'You're all done!!  Enjoy the file created.
msgbox("Data Writer Done!")

Creating database links

The following example assumes you have two databases, db1 and db2. You are currently logged into db1 and need a link to db2. We also assume the entry for db2 has already been established in the server’s TNSNAMES.ORA file.

-- Fixed user method
create database link db2
connect to scott
identified by tiger 
using 'db2';

-- Connected user method
create database link db2
using 'db2';

-- Current user method
create database link db2
connect to current_user 
using 'db2';

The difference between the three methods above is user authentication. In the fixed user example, regardless of who you are in db1, you will always access db2 as the user “scott”. With the connected user method, the link will always connect to db2 as whoever created the link; naturally, with this method, you will need to make sure you have a valid account in db2 as well. With the current user method, the link will always connect to db2 as whoever is using the link; this is arguably the most secure method.

If you need the link to be seen by every user, you may consider a public link:

create public database link db2
using 'db2';

With the database link established, you are now able to perform DML queries in db2 from db1. Here’s an example:

select a.name, b.telephone
from myschema.user_list a, scott.address_book@db2 b
where a.name=b.name;

Note the usage of “@db2” in the above example which signifies the usage of the database link.

One thing to keep in mind when establishing database links is security, thus, make sure you understand the potential risks of your design before implementing it. Fixed user method is the most commonly used one, and the fixed user typically ends up having more privileges in the remote database than necessary, therefore allowing users in db1 to do more things in db2 than necessary. Another concern is with how Oracle stores fixed user passwords — they are stored in the SYS.LINK$ table in clear text!