Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: alter tablespace fails but makes commit

Re: alter tablespace fails but makes commit

From: Paul Brewer <paul_at_paul.brewers.org.uk>
Date: Wed, 8 May 2002 22:57:41 +0100
Message-ID: <3cdab999_2@mk-nntp-1.news.uk.worldonline.com>


"Pablo Gomez" <pgomeza_at_adinet.com.uy> wrote in message news:962f505f.0205071456.6bf1cdc0_at_posting.google.com...
> Consider this transaction:
>
>
> SQL> commit;
> Commit complete.
>
> SQL> select * from emp;
> no rows selected
>
> SQL> insert into emp (empno, deptno) values (100, 10);
> 1 row created.
>
> SQL> select * from emp;
> EMPNO ENAME JOB MGR HIREDATE SAL
> COMM
> ---------- ---------- --------- ---------- --------- ----------
> ----------
> DEPTNO
> ----------
> 100
> 10
>
> SQL> alter tablespace a123 offline;
> alter tablespace a123 offline
> *
> ERROR at line 1:
> ORA-00959: tablespace 'A123' does not exist
>
> SQL> rollback;
> Rollback complete.
>
> SQL> select * from emp;
> EMPNO ENAME JOB MGR HIREDATE SAL
> COMM
> ---------- ---------- --------- ---------- --------- ----------
> ----------
> DEPTNO
> ----------
> 100
> 10
>
>
> As I can see the "alter tablespace ..." command makes a commit, what
> it's ok because is a ddl sentence, but I didn't know that when this
> sentence fails it makes a commit, like in this example where the
> tablespace not exits.
>
> Is this ok? Are there other situations similars where happens the
> same?
>
> Pablo Gomez Aguilera
> Twins Informatica
> Montevideo-Uruguay

Think of it like this:

When you issue DDL, any previous DML is first committed. Thus it makes no difference to your previous transaction whether the DDL itself succeeds or fails.

BTW, I think DDL doesn't have transactional consistency in the way we mere mortals understand it; I think that internally it achieves the illusion of so doing by creating 'temporary' objects which are renamed if the 'transaction' succeeds, or destroyed if it doesn't.

Believe my first paragraph; be suspicious of my second...

HTH. Paul Received on Wed May 08 2002 - 16:57:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US