Andrew Fraser DBA

Click for the Course notes with scripts for a 3 day performance tuning course I did recently.

Here’s an example of how to use partition exchange to move partitions or even entire unpartitioned tables from one schema to another. Is mean to be very fast and generate very little redo. Even more so if the partitions and tables are kept in the same tablespace.

Process for doing partition exchange is like this:

– first create the archive table, empty initially:
create table arch_owner.mytable .... [full create table spec goes in here, including partition clauses, but leave out the primary key/index ]

– give arch_owner (or alternatively whichever user runs this job) the required privileges
grant select, alter on live_owner.mytable to arch_owner ;

– create an empty temporary table, used later in the partition exchange
create table arch_owner.temp_table as select * from live_owner.mytable where 1=2 ;

– exchange the live partition with the temporary table
alter table live_owner.mytable exchange partition year2001 with table arch_owner.temp_table ;

– exchange that onwards to the archived table
alter table arch_owner.mytable exchange partition year2001 with table arch_owner.temp_table ;

– at the very end of the process, clean up the temporary table, add in any required primary keys or other indexes, and gather optimizer stats (=analyze)
drop table arch_owner.temp_table ;
alter table arch_owner.mytable add primary key (aud_id) using index tablespace ts_index1 ;
exec dbms_stats.gather_schema_stats('ARCH_OWNER', estimate_percent=>99.99, cascade => TRUE, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1')

> On a similar vein, is there an elegant way of copying the current data from the live_owner.mytable table to the arch_owner.mytable table? About 15Gb of data would normally go across via our scripts, but unfortunately the tables aren’t partitioned to let us do something along the lines of your last suggestion …

1) Yes, can do it in essentially the same way as before (h/t Pythian Blog):

– give arch_owner the required privileges on the live table:
grant select, alter on live_owner.mytable to arch_owner ;

– create an empty table and index in arch_owner
create table df_arch_owner.mytable .. [full create table spec here]
create index arch_owner.mytable_index1 on arch_owner.mytable (to_date(plan_date,'YYYY-MM-DD') ) tablespace ts_index1 ;

– also create a temporary table with a single dummy partition
create table arch_owner.temp_table
partition by range ( userid )
( partition dummy values less than ( maxvalue ) )
as select * from arch_owner.mytable where 1=2 ;

– again with an index (locally):
create index arch_owner.temp_index1 on arch_owner.temp_table (to_date(plan_date,'YYYY-MM-DD') ) tablespace ts_index1 local ;

– swap the live table and the temporary table with each other:
alter table arch_owner.temp_table exchange partition dummy with table live_owner.mytable
including indexes without validation ;

– then swap the temporary table and the arch_owner table with each other:
alter table arch_owner.temp_table exchange partition dummy with table arch_owner.mytable
including indexes without validation ;

– optimizer stats for both schemas should be re-gathered at the overall completion of the archiving work, and temporary tables dropped.

2) Or alternative method – but probably not so good because it doesn’t strictly move from one schema to the other, just renames:

– login as live_owner
conn live_owner/password

– rename the old table to have arch_ in front of its name
rename mytable to arch_mytable ;

– rename the old index to have arch_ in front of its name
alter index mytable_index1 rename to arch_mytable_index1 ;

– create a synonym in arch_owner that points to the arch_ table.
create synonym arch_owner.arch_mytable for live_owner.arch_mytable ;

– and also grant arch_owner privileges on the arch_ table
grant select on live_owner.arch_mytable to arch_owner ;

– create a new empty table in live_owner, complete with indexes, triggers, grants, and so on:
create table live_owner.mytable ....
create index mytable_index1 on live_owner.mytable ....
create trigger live_owner.del_mytable ....
grant select, insert, ....

– optionally can move the arch tables from one tablespace to the other (although I don’t see how that could be worth the substantial time and effort that it takes):
alter table arch_mytable move tablespace ts_arch_data ;
alter index arch_mytable_index1 rebuild tablespace ts_arch_index ;

– optimizer stats for both schemas should be re-gathered at the overall completion of the archiving work.

Changing Tablespaces in Partition Exchange

If you need objects to be in specific tablespaces, you should explicitly state that tablespace name, otherwise you can expect the users default tablespace will be used instead. That applies to all operations that alter indexes and tables – including exchange partition, enable constraint, create constraint, create index, alter index rebuild, create table, alter table add partition, and so on.

For partition exchanges, it is a bit more complex than that, because exchanged partitions take their tablespace with them during the exchange.

So imagine an initial setup where the live year2001 partition is in ts_data1, and the other two objects to be used are in tablespaces “X” and “Z”:

After we exchange “live year 2001″ with “temp table” their tablespaces swap:

Then exchange “archive year 2001″ with “temp table”, same thing happens:

Then we drop the “temp table”:

Now, assuming we want tablespace “X” to be ts_data1, checking back to the initial setup shows that that tablespace was the one defined by the temp table. So it is important to explicitly specify that tablespace, using code like:

– create an empty temporary table, used later in the partition exchange
create table arch_owner.temp_table tablespace ts_data1
as select * from live_owner.mytable where 1=2 ;

Also, assuming we want the “archive year2001” partition to be in ts_arch_data, we have a problem – it has ended up in ts_data1. There is no way to prevent that at the time, instead it has to be moved at the end, using:

alter table arch_owner.owner.mytable move tablespace ts_arch_data ;

That table move is unfortunately slow and generates redo.

ftp can be used in shell scripts by specifying the ftp password in a .netrc file.

On the source server create/edit this file:
$ vi $HOME/.netrc

Add in a line with the username password details:
machine targetservername login targetusername password targetpassword

Make that file secure (the ftp actually fails if you don’t):
$ chmod 600 $HOME/.netrc

If you don’t specify NLS_LANG in your shell when starting OAS, OAS goes and sets it for you.

Fix is to specify NLS_LANG in any OAS startup scripts you use, or edit these files:

$ grep -i NLS_LANG $ORACLE_HOME/Apache/Apache/bin/apachectl
NLS_LANG=${NLS_LANG="ENGLISH_UNITED KINGDOM.WE8ISO8859P1"}; export NLS_LANG
$ grep -i NLS_LANG $ORACLE_HOME/opmn/bin/opmnctl
NLS_LANG=${NLS_LANG="ENGLISH_UNITED KINGDOM.WE8ISO8859P1"}; export NLS_LANG
$

Reference 10gR2 Oracle Application Server Globalization Support Guide, Chapter 5.

You can see your own sessions nls settings

SQL> select * from nls_session_parameters ;

But for other users’ sessions, that information is stored in their own UGA, not accessible outside their session.

So if you need to know what their nls settings are, a logon trigger is needed to record that. Like this:

conn / as sysdba
drop table af_nls
/
create table af_nls (
  af_sid number ,
  af_program varchar2(48) ,
  af_nls_territory varchar2(40) )
tablespace users
/
create or replace trigger sys.logon_af_nls
after logon on database
when ( user != 'SYS' )
declare
  v_sid number ;
  v_program varchar2(48) ;
  v_nls_territory varchar2(40) ;
begin
  select m.sid, s.program into v_sid, v_program
    from v$session s , v$mystat m
    where m.sid = s.sid and rownum < 2 ;
  select value into v_nls_territory from nls_session_parameters
    where parameter = 'NLS_TERRITORY' ;
  insert into af_nls values
    ( v_sid , v_program , v_nls_territory  ) ;
  commit ;
end logon_af_nls ;
/
sho err

Handy if you’ve got this problem.

Here are online course notes, with code examples, for a simple introduction to Oracle’s PL/SQL language. Was originally aimed especially at Infrastructure DBAs, but would be of use to anyone learning PL/SQL.

For Apache webservers displaying normal html, adding these lines to file “.htaccess” is a good idea:

RewriteEngine On
RewriteCond %{HTTP_HOST} ^www\.(.+)$ [NC]
RewriteRule ^(.*)$ http://%1/$1 [R=301,L]

Because it will strip out the “www.” from URLs. As recommended at http://no-www.org/

But if your website runs Wordpress, a different (and simpler) method is required:

In the WordPress Site Admin screen, choose

• “Settings”, then
• “General”, and then
• edit the “WordPress Address” URL to remove the “www.” part.

You can do both changes – .htaccess and WordPress – if you want. That would be worthwhile if your site had a mix of wordpress and standard html pages. But make sure to do it in the order of (1) WordPress change first, then (2) .htaccess change afterwards. Otherwise the two changes will conflict and the WordPress pages will not be displayed at all.

Use this SQL to remove carriage return line feed from SQL server for displaying in excel:

select REPLACE(column_name, CHAR(13) + CHAR(10), ', ') from table_name ;

Based on posts by Aaron Bertrand and David Seruyang.

Oracle installer on Windows can fail to run, crashing out without a useful error message.

I found fix was to change the temp directories in a command window

set temp=d:\junk
set tmp=d:\junk

and then run the installer (setup.exe) from that same command window.

TEMP was set to use c:\documents and settings before, and the installer unpacks a lot of temporary files in there as it runs. Limitation in space quota each user can have in documents and settings I believe caused the installer to bomb out.

This happens with the 10g installer (including patchset 9208) which has more temporary files to unpack than earlier versions.

Windows ftp can fill up C: drive. This happens either putting or getting files, even if you try putting/getting them to a drive other than C:.

This happens because the ftp file is written to temporary area first by ftp, and moved to the final location at the very end.

The solution is to set temp directories to another drive which has a lot of free space, e.g.:

set TEMP=D:\junk
set TMP=D:\junk

If running ftp from command prompt and getting large files, then just run those commands in the command window before starting ftp.

Otherwise set those as control panel environment variables.

A similar issue can happen with the oracle installer in windows, where it uses TEMP and TMP to unpack a significant amount of temporary installer files. Same fix in that case.