Andrew Fraser DBA

Replace SYSTEM with the name of the schema you are interested in:

set serverout on size 999999
declare
cnt number ;
begin
for c1 in (select owner, table_name from all_tables where owner = 'SYSTEM')
loop
execute immediate 'select count(1) from '||c1.owner||'.'||c1.table_name into cnt ;
dbms_output.put_line(c1.table_name||','||cnt) ;
end loop ;
end ;
/

check it is on with:

SQL> select state, last_start_date from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB' ;

Switch it on and off with:

SQL> exec dbms_scheduler.disable(’GATHER_STATS_JOB’)
SQL> exec dbms_scheduler.enable(’GATHER_STATS_JOB’)

Files in /etc/cron.d only work if they are not executable files (at least that is the case on newer version of unix/linux).

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.