Re: alter tablespace fails but makes commit

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 7 May 2002 17:47:24 -0700
Message-ID: <ab9sis09m8@drn.newsguy.com>

In article <962f505f.0205071456.6bf1cdc0_at_posting.google.com>, pgomeza_at_adinet.com.uy says...
>
>Consider this transaction:
>

DDL is conceptually done like this:

begin

   COMMIT;
   begin

        do your ddl
        COMMIT;
   exception
        when others then
            ROLLBACK;
            RAISE;

   end;
end;
/

all ddl commits before and maybe after it happens.

>
>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

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue May 07 2002 - 19:47:24 CDT