Resolving Oracle large temp tablespace size problem

Temporary tablespaces are used by the system to sort out things that it cannot do in memory. For example, if you join two very large tables that exceed the memory capacity, Oracle will use the temp tablespace to perform the sorting operation in the temp tablespace. Now, imagine the case in which an user performs a Cartesian join by mistake; the temp tablespace may grow to be a very large size in order to cope with the data. Once the operation is completed, Oracle will not automatically reduce the file size, which what I observed with my Oracle 10g database.

To resolve the issue, I performed the following 6 steps. This solution does not require system restart, but it is recommend that you perform this during slower hours if possible. Temp tablespace does not hold any permanent objects, so I do not back up my temp tablespace, but as always, I recommend that you make certain you have a very recent backup set available to you before you start working, just in case something goes wrong.

1. Create a new temp tablespace

create temporary tablespace temp2
tempfile '/filepath/TEMP02.DBF'
size 1000m autoextend on next 100m maxsize 4000m;

2. Make the new temp tablespace the primary one

alter database default temporary tablespace temp2;

3. Drop the runaway tempfile of the original temp tablespace

alter database tempfile '/filepath/TEMP01.DBF' 
drop including datafiles;

4. Create a new tempfile for the original temp tablespace

alter tablespace temp
add tempfile '/filepath/TEMP01.DBF'
size 1000m autoextend on next 100m maxsize 4000m;

5. Make the original temp tablespace the primary one again

alter database default temporary tablespace temp;

6. Drop the new temp tablespace we created in #1 as we no longer need it

drop tablespace temp2 including contents and datafiles;

At this point, I saw that the disk space is recovered, and the tempfile is at the modest size of 1gb. All the file paths and file sizes are for demonstration purposes only; you should adjust the commands according to your needs.

Not sure where your temp files are? Try the following select statement:

select * from v$tempfile;

FILE# CREATION_CHANGE# CREATION_ TS#  RFILE# STATUS  ENABLED         BYTES  BLOCKS CREATE_BYTES BLOCK_SIZE NAME
----- ---------------- --------- --- ------- ------- ---------- ---------- ------- ------------ ---------- --------------------
    1                0             3       1 ONLINE  READ WRITE 1048576000  128000   1048576000       8192 /FILEPATH/TEMP01.DBF

Updating Oracle table with data from multiple tables using subquery

I ran into a situation today when I need to update 10,000 records in a master table totaling 1,000,000 rows, and the value that I’m going to use comes from the accounting department. Let us set up the scenario below.

Master Table
Job Number * Job Type Invoice Amount
101 Installation 1000
102 Installation 1000
103 Maintenance 500
104 Repair 400
105 Installation 3000
Data From Accounting
Job Number * Updated Invoice Amount
103 400
104 600

* Primary key

In sum, accounting wants to update job 103’s invoice amount to $400 and job 104’s invoice amount to $600, while leaving all other amounts the same. In this scenario, accounting only sent me two records, but in the real life situation I ran into, it was over 10,000 records. Also, this scenario’s master table has only 5 records; my real master table has close to 1,000,000 records. Thus, we need an update statement that is efficient.

Because Oracle does not allow multiple tables within the same update statement, I know at least one subquery will be needed. My first attempt, which was incorrect, was this:

-- Incorrect example!
update master_table m
set m.invoice_amount=(
	select a.updated_invoice_amount from data_from_accounting
	where m.job_number=a.job_number
);

The problem with this update statement was that I realized it would update all 5 records instead of just the 2 records accounting wanted to update; this would result in 103 and 104 being updated to the updated amounts, but all other amounts wiped out to null value. To remedy that, we could throw in a where clause to the update statement, as below:

-- Better example, but still inefficient
update master_table m
set m.invoice_amount=(
	select a.updated_invoice_amount from data_from_accounting a
	where m.job_number=a.job_number
)
where m.job_number in(
	select a2.job_number from data_from_accounting a2
	where m.job_number=a2.job_number
);

This would correctly update the records I wanted to update while leaving the others alone, but this query is a little inefficient as it needs to go through the data from accounting twice.

Finally, I came up with a solution that works very efficiently, even with my large 1,000,000-record table.

-- Best practice
update (
	select m.invoice_amount, a.updated_invoice_amount
	from master_table m, data_from_accounting a
	where m.job_number=a.job_number
)
set m.invoice_amount=a.updated_invoice_amount;

Note that the job number field in both tables in this example scenario are both primary keys. In order for the “best practice” update statement to work, they must be either primary keys or unique indexes, otherwise the query will fail with the error message “ORA-01779: Cannot modify a column which maps to a non key-preserved table”. As an aside, to create primary key or unique indexes, follow the examples below.

-- Primary key
alter table master_table add (
  constraint pk_master_table primary key (job_number)
);

-- Unique index
create unique index idx_data_from_accounting on data_from_accounting (job_number);

Creating an Oracle table pre-filled with random values

The code makes use of Oracle’s dbms_random package and some very light-weight PL/SQL. You may adjust the number of fields, field data types, total number of records, etc. to suit your needs.

create table schema.test_data (
  txt  varchar2(10), 
  numb  number(10), 
  dt  date
);
begin
  for i in 1..10000 loop
    insert into schema.test_data values (dbms_random.string('X',10), dbms_random.value(0,20), to_date(trunc(dbms_random.value(2422599, 2457085)),'j'));
  end loop;
  commit;
end;

When we are done, we will see that the table contains 10,000 records as we specified in the sample code above.

select count(*) from schema.test_data;

  COUNT(*)
----------
     10000

Below is what the data may look like.

select txt, numb, to_char(dt,'mm/dd/yyyy') as dt from schema.test_data where rownum<10;

TXT              NUMB DT
---------- ---------- ----------
35W6DQ986O          7 11/26/1943
8NOOSRH6R2          8 01/18/1993
7HPKA10GKQ         16 10/07/2012
I90Z9YVWHW          2 11/01/1939
WNNW1M7BNM         15 10/27/1982
R9OQF67QWP         13 08/28/1926
PD39YGY35D         10 02/04/1952
N8R8DKMAIO         15 07/06/1986
43MELQ9M0Q          3 06/19/1921

Generating random numbers, characters, and dates with Oracle SQL

Please review the following series of samples for how the dbms_random package works.

Random number:

select dbms_random.random as rand_numb from dual;

 RAND_NUMB
----------
-1.642E+09

Random number between 0 and 1:

select dbms_random.value as rand_numb_btwn_0_1 from dual;

RAND_NUMB_BTWN_0_1
------------------
        .950707301

Random number between x and y:

select dbms_random.value(10,20) as rand_numb_btwn_10_20 from dual;

RAND_NUMB_BTWN_10_20
--------------------
          14.6714295

Random upper case letters:

select dbms_random.string('U',10) as rand_upper from dual;

RAND_UPPER
----------
WVWZVYKNKP

Random lower case letters:

select dbms_random.string('L',10) as rand_lower from dual;

RAND_UPPER
----------
jdoyttefuw

Random mixed case letters:

select dbms_random.string('A',10) as rand_mixed from dual;

RAND_MIXED
----------
UBagEltDAr

Random upper case alphanumeric characters:

select dbms_random.string('X',10) as rand_upper_alphanumeric from dual;

RAND_UPPER_ALPHANUMERIC
-----------------------
JSANG89O8S

Random characters:

select dbms_random.string('P',10) as rand_chars from dual;

RAND_CHARS
----------
}![ NSOoo1

Random date between 1 Oct 1920 and 15 Mar 2015:
Note: The values used, 2422599 and 2457085, are Julian date numbers.

select to_date(trunc(dbms_random.value(2422599,2457085)),'j') as rand_date from dual;

RAND_DATE
---------
07-NOV-62

Building an AIM buddy list from your database employee table with VBScript

The code below can be used against Oracle, SQL Server, or MySQL database table to automatically create an AIM buddy list. The SQL statement should select out a “group_name” field (ideas: a department name such as “Accounting” or “Purchasing”, an office location such as “Taipei” or “New York”, etc.) and an “aim_name” field. The “group_name” will be used as AIM groups, while “aim_name” are the users’ actual registered AIM names. The output file is a flat text file in a format that can be imported into AIM.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' AIM Buddy List Builder                                        '
'                                                               '
' Description: Builds an AIM buddy list from your database      '
'      employee table.                                          '
' Author: C. Peter Chen                                         '
' Version Tracker:                                              '
'       1.0   20081021   Base version                           '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
option explicit
dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, sqlstr

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbType = "oracle"                ' Valid values: "oracle", "sqlserver", "mysql"
dbHost = "hostName"              ' Hostname of the database server
dbName = "dbName"                ' Name of the database/SID
dbUser = "user"                  ' Name of the user
dbPass = "password"              ' Password of the above-named user
outputFile = "c:buddylist.blt"  ' Path and file name of the output CSV file

' SQL statement below; be sure to select out "group_name" and "aim_name" in your SQL statement.
sqlStr = "select department_name as group_name, aim_name from employees_table where aim_name is not null order by department_name, aim_name"
'''''''''''''''''''''
' 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

dim a, showList, prevGroup

set a = fso.createtextfile(outputFile)
a.writeline ("Config {")
a.writeline (" version 1")
a.writeline ("}")
a.writeline ("User {")
a.writeline (" screenName dummyAimName")
a.writeline ("}")
a.writeline ("Buddy {")
a.writeline (" list {")

set showList = conn.execute(sqlstr)

prevGroup = "placeholder"
do while not showList.eof
	if (showList("group_name") <> prevGroup) then
		if (prevGroup <> "placeholder") then
			a.writeline ("  }")
		end if
		a.writeline ("  """ + showList("group_name") + """ {")
	end if
	a.writeline ("   " + showList("aim_name"))
	prevGroup = showList("group_name")
	showList.movenext
loop
showList.close
set showList = nothing

a.writeline ("  }")

a.writeline (" }")
a.writeline ("}")

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

'You're all done!!  Enjoy the file created.
msgbox("AIM Buddy List Created!")

Interested in obtaining a generic AIM buddy list import file format? Please see below for an illustration with fictional data.

Config {
 version 1
}
User {
 screenName dummyAimName
}
Buddy {
 list {
  "Accounting" {
   MrCFO_fictionalUser
   BobAtAccounting_fictionalUser
   JaneDoe_fictionalUser
  }
  "Purchasing" {
   LewisTheBuyer_fictionalUser
  }
  "Useless Employees" {
   PaulJohnson_fictionalUser
  }
 }
}

Oracle 11g database creation quick step-by-step guide

This is a simple step-by-step of how I created my main database in the Oracle 11g server running in a Linux environment. It is by no means comprehensive, but it may serve as a good starting point should you be looking for such a quick guide.

Increase Shared Memory Size

I am running Oracle Enterprise Linux, and on all Linux flavors, the total shared memory (shm) size is limited to 2gb. I am looking for a maximum size of 3gb, so I need to perform the following preparatory steps to set up a memory file system before setting up my database. You may or may not review this section.

[oracle@dbserver ~]$ su - root
Password:
[root@dbserver ~]# nano /etc/fstab

In nano (or your favorite editor), add or modify your entry for “/dev/shm” so that it looks like this:

[root@dbserver ~]# more /etc/fstab
...
tmpfs     /dev/shm     tmpfs     size=3g     0 0
...

At this point, you have the option of either running the following command to increase “/dev/shm” size to 3gb at runtime, or simply restart the server so that the change you have just made in fstab will take effect.

[root@dbserver ~]# mount -t shm tmpfs -o size=3g /dev/shm

Either after running “mount” or after restarting, “df -k” should reveal the following. If so, you are ready to set up the database. Note you should now use the “oracle” user instead of the “root” user.

[oracle@dbserver ~]$ dk -k
Filesystem   1K-blocks   Used   Available   Use%   Mounted on
...
tmpfs          3145728      0     3145728     0%   /dev/shm
...

Database Configuration Assistant (DBCA)

You may launch DBCA as follows.

[oracle@dbserver ~]$ dbca

Step 1: Operations — Choose “Create a Database”

Step 2: Database Templates — Choose the appropriate template. “General Purpose or Transaction Processing” is likely the one you will select.

Step 3: Database Identification — Set “Global Database Name” to “db_name.yorktel.com” and “SID” to “db_name”

Step 4: Management Options — Check “Configure Enterprise Manager”, check “Enable Alert Notifications”, and configure the SMTP server and email address.

Step 5: Database Credentials — Enter passwords for each system user, or choose “Use the Same Administrative Password for All Accounts” to use only one password.

Step 6: Storage Options — In my environment, I chose “File System”; your system might be different.

Step 7: Database File Locations — Since I chose “File System” in the previous step, in this current step I chose “Use Common Location for All Database Files” and set the path to “/u02/app/oracle/oradata”. Once the database is created, my database files will thus end up in “/u02/app/oracle/oradata/db_name”.

Step 8: Recovery Configuration — Check “Specify Flash Recovery Area” and set the “Flash Recovery Area Size”; in my environment, I use the very large value of “81920” (in mb) because I have the space available to waste. In this step, also check “Enable Archiving”.

Step 9: Database Content — No change necessary in my case

Step 10: Initialization Parameters — Under “Memory” tab, set “Memory Size (SGA and PGA)” to 3072mb (which is 3gb); if you cannot move beyond this step due to the 2gb limitation, please see the first section of this guide. Knowing my environment and the high number of individual user sessions, under “Sizing” tab, I changed the “Processes” parameter to 220 to suit my environment’s needs. I then clicked on “All Initialization Parameters” and “Show Advanced Parameters” to update/confirm the following; you may use the list for reference, but you may need to do different configurations for your environment.

  • db_flashback_retention_target: I set it to “5760” for setting up the flashback database later
  • db_unique_name: I set this to “unique_db_name”
  • memory_max_target: I set this to “3G”
  • processes: I confirmed this is “250”
  • standby_file_management: I set this to “AUTO”
  • undo_management: I set this to “AUTO” for setting up the flashback database later
  • undo_retention: I set this to “3600” for setting up the flashback database later

Step 11: Security Settings — No change necessary in my case

Step 12: Automatic Maintenance Tasks — No change necessary in my case

Step 13: Database Storage — You should confirm that the file locations are correct

Step 14: Creation Options — Make appropriate selections and “Finish” the DBCA process

The “Finish” button starts the database creation process.

Database Backup with Enterprise Manager

Once DBCA database creation is completed, the database will be set up and will be available for use. At this point, I logged on to Enterprise Manager to perform some final set up items regarding database backup. Again, this may or may not be fitting with your requirements, but it may present a good reference for you to review. To go into Enterprise Manager (EM), go to the URL “https://dbserver:1158/em/” and log on as sysdba.

Once I got into EM, I went to the “Availability” tab and clicked on “Recovery Settings”. I checked “ARCHIVELOG Mode” and changed the first archive log file destination to a path I prefer, such as “/u02/app/oracle/oradata/flash/db_name”. I checked “Enable Flashback Database” and restarted the database for this to take effect.

After the database was restarted, I logged back into EM (again as sysdba), I returned to “Availability” tab, and clicked on “Schedule Backup”. I clicked on the “Schedule Oracle-Suggested Backup” button and chose “Disk” in step 1 and configured the date and time in step 3. I set it up so that it runs a full database backup regularly during periods that typically sees low usage. The RMAN script hidden behind this GUI is the following.

run {
allocate channel oem_disk_backup device type disk;
recover copy of database with tag 'ORA$OEM_LEVEL_0';
backup incremental level 1 cumulative  copies=1 for recover of copy with tag

'ORA$OEM_LEVEL_0' database;
}

Next, again in “Availability”, I clicked on “Manage Current Backups” and scheduled “Crosscheck All”, “Delete All Obsolete”, and “Delete All Expired” jobs so that obsolete and expired backup sets will be removed regularly in free up space for current backup sets; note to schedule it to run regularly, you should choose the “repeating” radio button. The RMAN scripts hidden behind the GUI are as follows.

-- Crosscheck
CROSSCHECK BACKUPSET;
CROSSCHECK COPY;
-- Delete Obsolete
DELETE NOPROMPT OBSOLETE;
-- Delete Expired
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED COPY;

Oracle 11g Data Guard switchover/failover quick step-by-step guide

Assumptions

In this sample, it is assumed that you already have your Oracle 11g primary database functioning, and have already set up a Oracle 11g physical standby database, and can access them using the Data Guard Command-Line Interface (dgmgrl) utility. The current primary database will be referred to as “orcl1” and the current physical standby database will be referred to as “orcl2”. The SID for both primary and standby is “orclsid”. The current primary host is “host1” and the standby host is “host2”.

Switchover/Failover Procedures

First, launch the Data Guard Command-Line Interface and connect to the database.

[oracle@host1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.1.0.6.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@orcl1
Password:
Connected.

Then, issue switchover or failover command depending on your situation. Generally, a switchover is used to willingly pass the role of the primary database to the physical standby database (or, one of the physical standby databases), while a failover is typically done only when a major problem prevents you from normally using the primary database. Below is what you may see when you perform a switch over.

DGMGRL> switchover to orcl2
Performing switchover NOW. Please wait...
Operation requires shutdown of instance "orclsid" on database "orcl1".
Shutting down instance "orclsid"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "orclsid" on database "orcl2".
Shutting down instance "orclsid"...
database not mounted
ORACLE instance shut down.
Operation requires startup of instance "orclsid" on database "orcl1".
Starting instance "orclsid"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "orclsid" on database "orcl2".
Starting instance "orclsid"...
ORACLE instance started.
Database mounted.
Switchover succeeded. New primary is "orcl2"

And in the case of a failover…

DGMGRL> failover to orcl2
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcl2"

To confirm Data Guard switchover or failover has completed successfully, log in to Data Guard Command-Line Interface and issue the “show configuration” command.

[oracle@host2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.1.0.6.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@orcl2
Password:
Connected.
DGMGRL> show configuration

Configuration
  Name:                DRSolution
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    orcl2 - Primary database
    orcl1 - Physical standby database

Fast-Start Failover: DISABLED

Current status for "DRSolution":
SUCCESS

The above sample shows what the display would look like after a switchover. In the case of a failover, the physical standby database will show as “disabled” as below.

DGMGRL> show configuration

Configuration
  Name:                DRSolution
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    orcl2 - Primary database
    orcl1 - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Current status for "DRSolution":
Warning: ORA-16608: one or more databases have warnings

As an additional note, as the former physical standby database of orcl2 is now operating as your new primary database, you may wish to use Enterprise Manager to help you manage orcl2. If that is the case, you will need to perform the following to recreate the DBControl repository to enable Enterprise Manager on host2.

[oracle@host2 ~]$ emca -config dbcontrol db -repos recreate
STARTED EMCA at Sep 29, 2008 2:02:40 PM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: orclsid
Database Control is already configured for the database orclsid
You have chosen to configure Database Control for managing the database orclsid
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]:

At this point, you should have your former physical standby database orcl2 running fully as your primary database server.

After a failover, the physical standby database will not be present in your current setup. In the case that the failed database could be brought back, issue the following command in Data Guard Command-Line Interface to reinstate the failed database as a physical standby database.

DGMGRL> reinstate database orcl1
Reinstating database "orcl1", please wait...
Operation requires shutdown of instance "orclsid" on database "orcl1"
Shutting down instance "orclsid"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orclsid" on database "orcl1"
Starting instance "orclsid"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "orcl1" ...
Operation requires shutdown of instance "orclsid" on database "orcl1"
Shutting down instance "orclsid"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orclsid" on database "orcl1"
Starting instance "orclsid"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "orcl1" ...
Reinstatement of database "orcl1" succeeded

Display Oracle instance uptime

Please note that, this query makes use of a v$ table, thus keep in mind you must be logged in as a DBA.

select instance_name,to_char(startup_time,'mm/dd/yyyy hh24:mi:ss') as startup_time from v$instance;

Your output should look something like that following:

INSTANCE_NAME    STARTUP_TIME
---------------- -------------------
orcl01           08/03/2008 00:23:08

Remove unwanted line feeds in your data with Oracle SQL

To remove these line feeds that you do not need, you will need to make use of the chr() function built-in to Oracle, which allows you to find special ASCII characters. Consider the case that a product’s description may contain line feeds such as the following:

This is an amazing product.
It removes stains from your clothing, restoring crisp colors as if fresh from the store.
Buy it today!

select 
replace(
  replace(
    replace(
      product_description
    ,chr(10),' ') -- replace new lines with spaces
  ,chr(13),' ') -- replace carriage returns with spaces
,'  ',' ') as product_description -- cleans up double-spaces
from products
where product_id=1;

You should see that the line feeds are removed in the end result.

This is an amazing product. It removes stains from your clothing, restoring crisp colors as if fresh from the store. Buy it today!

Note that there is a replace() function that changes double-spaces to single-spaces. This may be needed because we may run into the case where there might be a trailing space at the end of a line, immediately before a line feed, which will become a double-space and may mess up your spacing slightly.

Export Oracle synonyms to text file

This is achieved by using the sys.dba_synonyms table, which means these queries must be done using an account with proper privileges. 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:oracleSynonyms.sql;

select 'create or replace '|| decode(owner,'PUBLIC','public ',null) ||  
'synonym ' || decode(owner,'PUBLIC',null, lower(owner) || '.') ||  
lower(synonym_name) || ' for ' || lower(table_owner) || '.' || lower(table_name) || decode(db_link,null,null,'@'||db_link) || ';'
from sys.dba_synonyms 
where table_owner not in('SI_INFORMTN_SCHEMA','SYS','SYSTEM','ORDSYS','XDB','CTXSYS','DMSYS','EXFSYS','MDSYS','SYSMAN','WKSYS','WMSYS')
order by owner, table_name;

spool off;