Re: alter tablespace fails but makes commit

From: Pablo Gomez <pgomeza_at_adinet.com.uy>
Date: 8 May 2002 15:00:17 -0700
Message-ID: <962f505f.0205081400.226c60f3@posting.google.com>

Thanks Tom. I'm a fan of your's articles published in "Ask Tom"

Now consider this trasaction:

SQL> select * from emp;
no rows selected

SQL> insert into emp (empno, deptno) values (100, 10); 1 row created.

SQL> alter tablespace 123 offline;
alter tablespace 123 offline

                 *

ERROR at line 1:
ORA-02140: invalid tablespace name

SQL> rollback;
Rollback complete.

SQL> select * from emp;
no rows selected

It's similar to the other one, but as I can see because the error is a syntax one there's no commit, so my question is when Oracle makes a commit.

Thanks again

Pablo Gomez Aguilera
Twins Informatica
Montevideo/Uruguay

Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<ab9sis09m8_at_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
Received on Wed May 08 2002 - 17:00:17 CDT