11g new features

The presentation of Bryn Llewellyn was a good one! He talked about the 11g new features. Well, he said it is maybe not named 11g and maybe none of the features will be available. But since the beta is already available, I guess all of the features he talked will be available. It is very rich, and went fast, so I apologize for not relating all of them, or any mistakes that I include.

The result set caching. My favorite. This sounds like a huge performance improvement for many applications. I have an application which is doing a select count(*) at every screen. It is the bottleneck. A possible workaround is to use query rewrite and materialized view. Now in 11g, you just add a /*+result_cache*/ hint, so the result of the query will be cached. Justin Cave asked about the integrity. The answer is : there is no compromise of the integrity. There is an aggressive cache invalidation, so whatever DML happens on one dependent table, the cache result is invalidated. As for the materialized view rewrite in 9iR1, I guess there will be quite a few P1 bug for that hint in 11.1.0, but still it is a great announcement. This hint also works for PL/SQL functions, the declaration of the function can include result_cache relies on (emp)

Fine grained dependency tracking. Currently, if you have a view or a function based on a table, and if you modify that table, the view is getting invalid. This is what Bryn called : aggressive object invalidation. Now in 11g it has improved, so if you modify an element of the table which is not included in that view (ex: you add a column), the view is not invalidated. This is wise.

Regular expression improvements. This is rather a SQL improvement, glad Bryn did presented it too. One of this is a new function called regexp_count. All of the existing functions have been improved. I love that kind of new features, because they offer new way of solving queries. I hope I will be able to reanswer Re: Can any one retrive Nth column from a table with one less function in 11g.

Using the => syntax in sql. In 11g you can use select f(x=>1) from dual, which is a goodie.

Fine grained access control to the TCP packages family (utl_tcp, utl_smtp, …). This is a good one. You can grant execute on the package only for specific IP addresses.

Read-only table. Ok, it reminds me one thread how-to-set-a-table-in-read-only-mode. Glad to see this one too!

Fast triggers. Nothing to do about it, triggers are just faster in 11g.

Intra-Unit inlining. This is the 3rd level of PLSQL_optimize_level.

Trigger order. You have many triggers on one table, you can now specify in which order they will be fired.

Compound trigger. A new powerfull type of trigger, which enables you to specify a declarative section (and to have a kind of package global variable), a before procedure, an after each row procedure, and an after procedure. All this in a single trigger.

Create disabled trigger. It is now possible to create a disabled trigger. You can create an invalid trigger, and compile it later, but if not used appropriately by the developers, it may create a new kind of invalid objects in the databases. Good for the developer. Not necessarily pleasant for the DBA.

Using sequence in PL/SQL without DML. It is possible to use x:=s.nextval in plsql, instead of selecting from dual.

PLSQL_Warning. You can compile a procedure with PLSQL_WARNING=enable all, to get errors when others exceptions are caught and not raised. Tom Kyte does appreciate this one 😉

A special mention to the super keyword. I am enthusiastic but somehow skeptic about the implementation of inheritance in PL/SQL. Well, I am looking forward from reading more about this.

Faster and real native compilation. Improvement have been done to bypass the filesystem and the external C compiler in this release.

DBMS_SQL improvement. Ways to bypass the 32k limitation. I have not understand very well this change.

The continue keyword. You can use the continue keyword in your plsql loops, as you would do in another languages.

disclaimer: the post was based on OOW 2006 announcement and may not reflect the production release of 11g

not enough time to do all

I updated my calendar for Oracle Open World. By rechecking the location, I figured out I did book to much. Unfortunately, I will not be able to go to steven feuerstein session S281918 on monday, because it starts at the same time as tom session ends.

I had a difficult choice for tuesday :
– S281213 (now full) Developing a Successful Database Backup and Recovery Strategy: Customer Experiences with Jonathan Intner, oracle specialist at Novartis
– S283505 Battle-Tested Best Practices for Provisioning, Managing, and Monitoring Oracle Real Application Clusters Environments with Arup Nanda, Oracle Ace
– S283870 Flexframe for Oracle: Adaptive Infrastructure Solution for Oracle Database and Oracle Application Server 10g

I chose the third one, because I am managing a workshop with RAC on Egenera BladeFrame one week later in Zürich, so it will be very useful material.

I also managed to add three expert sessions at the otn lounge to my schedule :

Steven Feuerstein, Arup Nanda and Tom Kyte with APC

apply CPUOct2006

I downloaded the CPUOct2006 patch for 10.2.0.2 and applied it successfully to my database.

SQL> select action,id,comments from dba_registry_history;
ACTION         ID COMMENTS
------ ---------- ----------
CPU       5490848 CPUOct2006

in the logfile, apart from a few ignorable ORA-02303: cannot drop or replace a type with type or table dependents errors, everything went fine.

Applying the security patch shortly after they are released is in my opinion a good practice, because a fix could sometimes reverse-engineered to reveal the bug.

Tomorrow afternoon I flight via London to SF for OOW2006. I am quite unhappy about not taking my notebook, my phone, not even a book or a SuDoKu in the plane 🙁 Well, I hope BA will not refuse to serve me wine this time as Delta did last year

Migration of tnsnames.ora to LDAP (Sun Java System Directory Server)

In this post, I did show how easy it is to use OID to resolve your network service names.

Apart OID, AD (Microsoft Active Directory) is also supported.

However, I do not want to use such products, as my customer already have a Sun Java System Directory Server running.

It is quite easy. Here are the steps with the SunOne Console.

1) expand the schema
login to the Directory Server as cn=directory manager
click schema in the configuration tab
In the Attributes subtab, click create, and type it orclnetdescstring as attribute name, and select OctetString as Syntax, and uncheck multi-valued, click OK.
In the Object Classes subtab, Create a class named OrclService , add cn as required attribute and orclnetdescstring as allowed attribute. Click OK

2) start adding services
either with your prefered ldap GUI (like Siemens DirX Manager) or with command line
lsc01.diff
dn: ou=intranet, dc=lcsys, dc=ch
ou: intranet
objectClass: top
objectClass: organizationalunit

dn: ou=applications, ou=intranet, dc=lcsys, dc=ch
ou: applications
objectClass: top
objectClass: organizationalunit

dn: ou=TNSnames, ou=applications, ou=intranet, dc=lcsys,dc=ch
ou: TNSnames
objectClass: top
objectClass: organizationalunit

dn: cn=OracleContext, ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch
cn: OracleContext
objectClass: top
objectClass: orclservice

dn: cn=lsc01, cn=OracleContext, ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch
cn: lsc01
objectClass: top
objectClass: orclservice
orclnetdescstring: (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=blade01.lcsys.ch)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = lsc01.lcsys.ch)))

which I can add with

ldapadd -h blade01 -p 34001 -D “cn=Directory Manager” -w *** -f lsc01.ldif

adding new entry ou=intranet, dc=lcsys, dc=ch

adding new entry ou=applications, ou=intranet, dc=lcsys, dc=ch

adding new entry ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch

adding new entry cn=OracleContext,ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch

adding new entry cn=lsc01, cn=OracleContext, ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch

Configuring sqlnet.ora and ldap.ora is the last step :
sqlnet.ora
TNSPING.TRACE_LEVEL = SUPPORT
TNSPING.TRACE_DIRECTORY = /tmp
NAMES.DIRECTORY_PATH= (LDAP)

ldap.ora
DIRECTORY_SERVERS= (blade01:34001)
DEFAULT_ADMIN_CONTEXT = "ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch"

try to tnsping, it should work. If it does not, check /tmp/tnsping.trc

$ tnsping LSC01

TNS Ping Utility for Solaris: Version 9.2.0.8.0 - Production on 09-OCT-2006 15:50:42

Copyright (c) 1997, 2006, Oracle Corporation. All rights reserved.

Used parameter files:
/export/home/schnela1/tmp/sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = blade01)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = lsc01.lcsys.ch)))
OK (0 msec)

note that using something else than OID or AD is not supported

SOUG last week

The documents from last SOUG special interest group with Tom Kyte are now available for downloadable on the soug.ch homepage (under history).

The day started with Sven Vetter, who talked about SLA management pack for Enterprise Manager Grid Control 10gR2. We saw also how to define a custom shell script and let OEM generate a graphic over time. He talked about “beacons”, a kind of interface to define application interaction with the grid.

Than came an interesting presentation about Performance Tuning from Patrick Schwanke. He talked about views like DBA_HIST_SQLTEXT and DBA_HIST_SQLBIND. I also learnt the virtual index trick : There is a parameter called _use_nosegment_indexes with allow you to generate EXPLAIN PLAN for a segment without segment. There is a magic keyword called NOSEGMENT. This mechanism is used internally by Oracle Tuning Pack, and by other tools like the ones from Quest.


SQL> create table t as select * from all_objects;

Table created.

SQL> create index i on t(object_id) NOSEGMENT;

Index created.

SQL> alter session set "_use_nosegment_indexes"=true;

Session altered.

SQL> select * from t where object_id=1;

Execution Plan
---------------------------
Plan hash value: 1984501315

--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | I |
--------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=1)

SQL> alter session set "_use_nosegment_indexes"=false;

Session altered.

SQL> select * from t where object_id=1;

Execution Plan
---------------------------
Plan hash value: 2153619298

----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=1)

Note
-----
- dynamic sampling used for this statement

Later, Tim Polland talked about Texas Memory and the RAMdisks. It is a piece of hardware which use memory instead of disk to store the datas. The hardware has a kind of RAID mechanism for consistency, and it is not supposed to lose data (!). It is quite different from a typical disk cache, because it does not “cache” the datas, it just saves them in memory. Well, you are not supposed to use this to stores a terabyte of datas, but you could store only your most time-critical datas there, for example move a table to a different tablespace, and store that table on the RAMDISK, also the redo logs, and eventually the undo/system tablespaces too. The RAMDisk hardware is not specific for Oracle. Some “entry-level” ramdisk can offer something like 400 Megabytes/seconds data with nanoseconds access time. Well, it is the speed of memory, so it is also the price of memory. So do not expect to have this at home to play your favorite games !

In the afternoon, we started with Oracle Benchmarks with Manfred Drozd. Very interesting presentation, which shows that there is no “quick” benchmark, but a benchmark should consider I/O, PL/SQL performance, amount of disks, volume manager/ASM, cpu types/count, OS, architecture, Oracle Version, etc. The performance for select, for insert, for update, all those may differ from one system to another.

Real world example just followed with the presentation of a study case in the swiss post. The comparison was mainly between Sun Solaris on Sparc and Linux on x86_64. Well, there was no Better/Worst answer, you know, like is real world…

Last but not least, Tom Kyte, the “Tom” behind asktom, did talk about Instrumentation and the advantage -the need- of using debugging info, with DBMS_APPLICATION_INFO, with your own debug procedure, with Log4Plsql/Log4J packages. The tools we show were DBMS_MONITOR (10g) and trcsess tool (located in $ORACLE_HOME/bin), DBMS_TRACE and PL/SQL conditional compilation. DBMS_TRACE is usefull for detecting catched exception :


SQL> @?/rdbms/admin/dbmspbt
SQL> @?/rdbms/admin/prvtpbt.plb
SQL> @?/rdbms/admin/tracetab
SQL> exec dbms_trace.set_plsql_trace
(dbms_trace.trace_all_exceptions)

PL/SQL procedure successfully completed.

SQL> begin dbms_output.put_line(1/0);
exception when others then null;
end;
/

PL/SQL procedure successfully completed.

SQL> select EXCP,EVENT_UNIT_KIND,ERRORSTACK
from plsql_trace_events
where ERRORSTACK is not null;
EXCP EVENT_UNIT_KIND
---------- --------------------
ERRORSTACK
----------------------------------------
1476 ANONYMOUS BLOCK
ORA-01476: divisor is equal to zero

NLS_LANG=german_switzerland

There is a bug about NLS_LANG and SWITZERLAND in 10g.

This is because the tausend separator has changed from 9i (.) to 10g (‘).

Check Note 4598613.8

The workaround is to not use NLS_LANG=german_switzerland, but if you have different NLS parameters on the client and on the server, than you will have other problems, ex: questionable statistics by exp/imp.

OK, here is the bug :


$ export NLS_LANG=german_switzerland.we8iso8859p1
$ sqlplus -L scott/tiger

SQL*Plus: Release 10.2.0.2.0 - Production on Do Sep 21 13:50:05 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

ERROR:
ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-02248: Ungültige Option für ALTER SESSION

SP2-0751: Anmeldung bei Oracle nicht möglich. SQL*Plus wird beendet

and my workaround


$ export NLS_LANG="" NLS_TERRITORY=switzerland NLS_LANGUAGE=german
$ sqlplus -L scott/tiger
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Sep 21 13:51:30 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
With the Partitioning and Data Mining options

SQL> select * from NLS_SESSION_PARAMETERS ;
PARAMETER VALUE
---------------------- -----------
NLS_LANGUAGE GERMAN
NLS_TERRITORY SWITZERLAND
NLS_CURRENCY SFr.
NLS_ISO_CURRENCY SWITZERLAND
NLS_NUMERIC_CHARACTERS .'
NLS_CALENDAR GREGORIAN
NLS_DATE_LANGUAGE GERMAN
NLS_SORT GERMAN
NLS_DUAL_CURRENCY SF

17 Zeilen ausgewahlt.

SQL> select to_char(1000,'9G999') from dual;

TO_CHA
------
1'000

SQL> quit
Verbindung zu Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
With the Partitioning and Data Mining options beendet

So it is possible to use german_switzerland. Not sure if this workaround is bullet-proof. Metalink says it is fixed in 11g
[edit]fixed in 10.2.0.3 and later [/edit]

KEEP DENSE_RANK versus ROW_NUMBER()

I often see questions like

How do you get the row of each department with the highest salary

In case you only want 1 row, you have two modern solutions :
Analytics, which is trend, and KEEP, which is not very known

The old fashion would be something like where s in (select max())

Ok, let’s start with analytics


SQL> select ename,deptno,sal
2 from (select ename,deptno,sal,
3 row_number() over (partition by deptno
4 order by sal desc,empno) r from emp)
5 where r=1;
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
BLAKE 30 2850

and the KEEP method, which is a special aggregation


SQL> select max(ename) keep (dense_rank first
2 order by sal desc,empno) ename,
3 deptno,max(sal) sal
4 from emp group by deptno;
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
BLAKE 30 2850

the second one should be more performant


SQL> select count(*) from emp2;
COUNT(*)
----------
917504

SQL> select max(ename) keep (dense_rank first
2 order by sal desc,empno) ename,
3 deptno,max(sal) sal
4 from emp group by deptno;
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
BLAKE 30 2850

Elapsed: 00:00:01.00
SQL> select ename,deptno,sal
2 from (select ename,deptno,sal,
3 row_number() over (partition by deptno
4 order by sal desc,empno) r from emp)
5 where r=1;
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
BLAKE 30 2850

Elapsed: 00:00:01.43

wget from otn

wget is a well-known command line utility for downloading files from the internet/intranet.

I like to use the limit-rate, so that my coworkers still can surf. Until today, I never succeeded to download from otn, because wgetting software required me to log in and accept the export restriction

How to automate login is actually extremly easy :

1) open your mozilla browser, go to the page, login, accept the export, download the file, click properties, copy paste the “Saving From”, Cancel
2) find out where is located your cookies.txt file

$ find $HOME -name "cookies.txt"
/home/lsc/.mozilla/default/86iy2n5j.slt/cookies.txt

3) wget

$ wget –load-cookies /home/lsc/.mozilla/default/86iy2n5j.slt/cookies.txt –limit-rate 250k http://download-uk.oracle.com/otn/compaq/oracle10g/10202/10202_database_HP-Tru64.zip
100%[++++++++++++++++++++++++++++======>] 1,081,211,566 254.98K/s ETA 00:00

16:48:05 (249.99 KB/s) – `10202_database_HP-Tru64.zip.1′ saved [1,081,211,566/1,081,211,566]

It works!

generate dml

I just discovered that cool new function in SQL/Developer

just right click on the table and chose export sql insert

-- INSERTING into EMP
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7369
,'SMITH','CLERK',7902,to_date('1980-12-17',
'DD-MON-RR'),800,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7499
,'ALLEN','SALESMAN',7698,to_date('1981-02-20',
'DD-MON-RR'),1600,300,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7521
,'WARD','SALESMAN',7698,to_date('1981-02-22',
'DD-MON-RR'),1250,500,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7566
,'JONES','MANAGER',7839,to_date('1981-04-02',
'DD-MON-RR'),2975,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7654
,'MARTIN','SALESMAN',7698,to_date('1981-09-28',
'DD-MON-RR'),1250,1400,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7698
,'BLAKE','MANAGER',7839,to_date('1981-05-01',
'DD-MON-RR'),2850,null,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7782
,'CLARK','MANAGER',7839,to_date('1981-06-09',
'DD-MON-RR'),2450,null,10);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7788
,'SCOTT','ANALYST',7566,to_date('1987-04-19',
'DD-MON-RR'),3000,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7839
,'KING','PRESIDENT',null,to_date('1981-11-17',
'DD-MON-RR'),5000,null,10);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7844
,'TURNER','SALESMAN',7698,to_date('1981-09-08',
'DD-MON-RR'),1500,0,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7876
,'ADAMS','CLERK',7788,to_date('1987-05-23',
'DD-MON-RR'),1100,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7900
,'JAMES','CLERK',7698,to_date('1981-12-03',
'DD-MON-RR'),950,null,30);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7902
,'FORD','ANALYST',7566,to_date('1981-12-03',
'DD-MON-RR'),3000,null,20);
Insert into "EMP" ("EMPNO","ENAME","JOB","MGR",
"HIREDATE","SAL","COMM","DEPTNO") values (7934
,'MILLER','CLERK',7782,to_date('1982-01-23',
'DD-MON-RR'),1300,null,10);

It is quite handy to post insert statements on forums, or move a table from one database to another just with copy paste, or export only a subset of the columns, because you can export a view too

TUNED_UNDORETENTION

How often I did meet ORA-01555: snapshot too old: rollback segment too small ?
I cannot count. On one of the database I am currently administrating it is about once a day.

Back to Oracle 7 and 8, the solution was usually to size the rollback segments properly. Using big rollback segments for big jobs, and many small segments for OLTP.

Oracle 9i introduced the automatic undo management. When you meet ORA-01555, just increase the UNDO tablespace (or/and set it autoextensible). Well, how did I read complains from users which used to have tiny rollback segments, and did not understand why the undo tablespace grows to gigabytes in 9i. There is also a parameter, called UNDO_RETENTION, which prevents Oracle from rewritting old extents before it really needs them.

In 10g, you can also force oracle to keep old undo extents up to the UNDO_RETENTION by using

alter tablespace UNDOTBS1 retention guarantee;

What I learned today is the TUNED_UNDORETENTION mechanism. By setting UNDO_RETENTION to 0, you enable auto tuned retention. That is, as long as your tablespace can autoextend, you will less probably get an ORA-01555. A recommended lecture is Metalink Note 240746.1

Back to my 9i database with ORA-01555, I will try to increase undo_retention to something bigger than

SQL> select max(maxquerylen) from v$undostat;

MAX(MAXQUERYLEN)
----------------
105047

And see how long I will survive until the next ORA-01555…

oow2k6 sessions

the sessions I registered with for oracle open world 2006 are the following :

Monday 10:45 : Bryn LLewellyn, PLSQL Manager, will talk about PLSQL future in the next release (11g) : session S281172

Monday 12:30 : Andrew Clarke, Oracle ACE, will talk about unit testing in PL/SQL : session 282112

Monday 15:15 : Thomas Kyte, Oracle ACE and asktom owner, will talk about sql worst practice : session 281206

Monday 16:15 : Steven Feuerstein, Oracle ACE and PL/SQL author, will talk about 10 Things You Should NEVER Do in PL/SQL : session 281918

Tuesday 13:15 : Sue Harper, SQL Developer Product Manager, and Kris Rice, Director of Database Tools Research, will talk about Advanced Database Development with Oracle SQL Developer : session 281142

Tuesday 16:30 : Wim Coekaerts, Oracle ACE and Linux Principal, will talk about Securing Linux for Oracle : session 281224

Thursday 09:30 : Werner Puschitz, Oracle ACE, will talk about Automated Oracle Real Application Clusters Deployment : Session 283479

I have booked a few more sessions, about Virtual Directory, Application Server and Grid Control.

I will be at the OTN lounge to play chess or othello at any time 🙂

cu@oow2k6@sf

tablespace maintenance tasks

I just had to guide one of our DBA this morning.

How to get free of segments in SYSTEM tablespace :

  1. tables

    SQL> select owner, table_name from dba_tables where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME
    —– ———-
    SCOTT T

    SQL> alter table scott.t move tablespace users;

    Table altered.

    SQL> select owner, table_name from dba_tables where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

    If the table has a LONG datatype, than I need to exp/drop/create/imp the table


    SQL> select owner, table_name from dba_tables where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME
    —– ———-
    SCOTT T

    SQL> alter table scott.t move tablespace users;
    alter table scott.t move tablespace users
    *
    ERROR at line 1:
    ORA-00997: illegal use of LONG datatype

    SQL> ! exp file=t.dmp tables=t userid=scott/tiger

    Export: Release 10.2.0.2.0 – Production on Mon Aug 14 14:24:29 2006

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

    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
    With the Partitioning, OLAP and Data Mining options
    Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

    About to export specified tables via Conventional Path …
    . . exporting table T 3 rows exported
    Export terminated successfully without warnings.

    SQL> select dbms_metadata.get_ddl(‘TABLE’,’T’,’SCOTT’) from dual;
    DBMS_METADATA.GET_DDL(‘TABLE’,’T’,’SCOTT’)
    —————————————————————————————————————————————
    CREATE TABLE “SCOTT”.”T”
    ( “L” LONG
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE “SYSTEM”

    SQL> drop table scott.t;

    Table dropped.

    SQL> CREATE TABLE “SCOTT”.”T”
    2 ( “L” LONG
    3 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    4 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    5 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    6 TABLESPACE “USERS”;

    Table created.

    SQL> ! imp file=t.dmp tables=t userid=scott/tiger ignore=y

    Import: Release 10.2.0.2.0 – Production on Mon Aug 14 14:27:05 2006

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

    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
    With the Partitioning, OLAP and Data Mining options

    Export file created by EXPORT:V10.02.01 via conventional path
    import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
    . importing SCOTT’s objects into SCOTT
    . importing SCOTT’s objects into SCOTT
    . . importing table “T” 3 rows imported
    Import terminated successfully without warnings.

  2. Indexes

    SQL> select owner, index_name from dba_indexes where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER INDEX_NAME
    —– ———-
    SCOTT I

    SQL> alter index scott.i rebuild tablespace users;

    Index altered.

    SQL> select owner, index_name from dba_indexes where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  3. lob
    warning: in 9i you must join with dba_tables to get tablespace_name

    SQL> select owner, table_name, column_name from dba_lobs where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME COLUMN_NAME
    —– ———- ———–
    SCOTT T          C

    SQL> alter table scott.t move lob(c) store as (tablespace users);

    Table altered.

    SQL> select owner, table_name, column_name from dba_lobs where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  4. table partition

    SQL> select table_owner, table_name, partition_name from dba_tab_partitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    TABLE_OWNER TABLE_NAME PARTITION_NAME
    ———– ———- ————–
    SCOTT       T          P1

    SQL> alter table scott.t move partition p1 tablespace users;

    Table altered.

    SQL> select table_owner, table_name, partition_name from dba_tab_partitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  5. index partition

    SQL> select index_owner, index_name, partition_name from dba_ind_partitions where tablespace_name=’SYSTEM’ and index_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    INDEX_OWNER INDEX_NAME PARTITION_NAME
    ———– ———- ————–
    SCOTT       I          SYS_P32

    SQL> alter index scott.i rebuild partition SYS_P32 tablespace users;

    Index altered.

    SQL> select index_owner, index_name, partition_name from dba_ind_partitions where tablespace_name=’SYSTEM’ and index_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  6. table subpartition

    SQL> select table_owner, table_name, subpartition_name from dba_tab_subpartitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    TABLE_OWNER TABLE_NAME SUBPARTITION_NAME
    ———– ———- ——————————
    SCOTT       T          SYS_SUBP30

    SQL> alter table scott.t move subpartition SYS_SUBP30 tablespace users;

    Table altered.

    SQL> select table_owner, table_name, subpartition_name from dba_tab_subpartitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  7. index subpartition

    SQL> select index_owner, index_name, subpartition_name from dba_ind_subpartitions where tablespace_name=’SYSTEM’ and index_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    INDEX_OWNER INDEX_NAME SUBPARTITION_NAME
    ———– ———- ——————————
    SCOTT       I          SYS_SUBP31

    SQL> alter index scott.i rebuild subpartition SYS_SUBP31 tablespace users;

    Index altered.

    SQL> select index_owner, index_name, subpartition_name from dba_ind_subpartitions where tablespace_name=’SYSTEM’ and index_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  8. IOT

    SQL> select owner, table_name, index_name, index_type from dba_indexes where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME INDEX_NAME INDEX_TYPE
    —– ———- —————– ———-
    SCOTT T          SYS_IOT_TOP_10406 IOT – TOP

    SQL> alter table scott.t move tablespace users;

    Table altered.

    SQL> select owner, table_name, index_name, index_type from dba_indexes where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  9. IOT Overflow

    SCOTTSQL> select owner, table_name, iot_name, iot_type from dba_tables where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME IOT_NAME IOT_TYPE
    —– —————— ——– ————
    SCOTT SYS_IOT_OVER_10406 T        IOT_OVERFLOW

    SQL> alter table scott.t move overflow tablespace users;

    Table altered.

    SQL> select owner, table_name, iot_name, iot_type from dba_tables where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  10. cluster
    I did not find out yet 🙁 Probably not possible, so use exp+drop+create+imp as for long

  11. nested tables
    warning: according to the sql reference manual (not tested), in 9i, the nested table was moved with the parent table automatically

    SQL> select owner, table_name from dba_tables where nested=’YES’ and tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME
    —– ———-
    SCOTT CTT

    SQL> alter table scott.ctt move tablespace users;

    Table altered.

    SQL> select owner, table_name from dba_tables where nested=’YES’ and tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

  12. Lob partitions and subpartitions
    This is getting tricky… I did not achieve to change the default tablespace specification for lob, nor to change a HASH partition. Again, when all failed, use exp/drop/create/imp

    SQL> select owner, table_name, column_name from dba_lobs where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME COLUMN_NAME
    —– ———- —————–
    SCOTT T         C

    SQL> alter table scott.t move lob(c) store as (tablespace users);
    alter table scott.t move lob(c) store as (tablespace users)
    *
    ERROR at line 1:
    ORA-14511: cannot perform operation on a partitioned object

    SQL> select table_owner,table_name,column_name,partition_name from dba_lob_partitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    TABLE_OWNER TABLE_NAME COLUMN_NAME PARTITION_NAME
    ———– ———- ———– ————–
    SCOTT       T         C           P1

    SQL> alter table scott.t move partition p1 lob(c) store as (tablespace users);

    Table altered.

    SQL> select table_owner,table_name,column_name,partition_name from dba_lob_partitions where tablespace_name=’SYSTEM’ and table_owner not in (‘SYS’,’SYSTEM’,’OUTLN’);

    no rows selected

    SQL> select owner, table_name, column_name from dba_lobs where tablespace_name=’SYSTEM’ and owner not in (‘SYS’,’SYSTEM’,’OUTLN’);
    OWNER TABLE_NAME COLUMN_NAME
    —– ———- —————–
    SCOTT T          C

    the table was ranged partitioned. With a hash partition, i would get an ORA-22877

    SQL> alter table scott.t move partition sys_p47 lob(c) store as (tablespace users);
    alter table scott.t move partition sys_p47 lob(c) store as (tablespace users)
    *
    ERROR at line 1:
    ORA-22877: invalid option specified for a HASH partition or subpartition of a LOB column

The DBA also wanted to separate indexes and tables for tuning IOs. I had to redirect him to the doc :
Performance Tuning Guide
One popular approach to manual I/O distribution suggests separating a frequently used table from its index. This is not correct.
well, he shows me his Oracle 9i Tuning Course, Chapter 3, Slice 6, put table and indexes on different tablespaces… Ok, this is not a new thema, just google to find explanations !

latest patchset

Huge downtime on forums.oracle.com today. After landing on an Error-500 page, I discovered a quick and neat overview of oracle patchsets, accessible without metalink account :

http://www.oracle.com/technology/support/patches.htm

Still waiting for 9.2.0.8 Solaris by the way, announced for 2006Q3, whatever this mean