Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: alter tablespace fails but makes commit
"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