Re: Begin... END; gives odd error:

From: <fitzjarrell_at_cox.net>
Date: 13 Oct 2006 06:25:14 -0700
Message-ID: <1160745914.411089.39820@m73g2000cwd.googlegroups.com>

erich.keane_at_verizon.net wrote:
> The server is on my computer, and will never ever be used by anyone but
> me. These databases will NEVER even get into anything more than that.
> I am testing an Oracle interface layer, and created it as the System
> account. When it comes time to actually do something, a DBA will be
> designing the data structure.
>
> It is easier to just run the oracle as system, so that I don't have to
> deal with other things at the moment.
>
> If you look, the table is even named 'test', which should have given a
> hint.
>
> DA Morgan wrote:
> > erich.keane_at_verizon.net wrote:
> > > BTW, I forgot to mention, I am executing this through a ODP.Net app.
> > >
> > >> You cannot directly perform DDL within a PL/SQL block. Also, WHY is
> > >> this table owned by SYSTEM? Looking at the ALTER TABLE statements I
> > >> find:
> > >
> > > Table is owned by system for ease at the moment.
> >
> > Ease of what? Ease of trashing the system. If you are being paid ... you
> > are being paid to do a good job. Please reconsider.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damorgan_at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> > www.psoug.org

It doesn't matter if all this database stores is your grandmother's knitting patterns, using the SYSTEM user is an extremely bad idea as these objects are stored in, surprise of surprises, the SYSTEM tablespace, a tablespace reserved for SYS and SYSTEM owned objects necessary for the proper operation of your database. Please do not attempt to interchange 'easy' with 'lazy', as the latter is precisely your situation. Creating a TEST user and associated tablespace is not a difficult task:

SQL> create tablespace test datafile 'd:\my\this\is\easy\test.dbf' size 500M;

Tablespace created.

SQL> create user test identified by hokeypassword default tablespace test temporary tablespace temp quota unlimited on test;

User created.

SQL> grant create session, create table to test;

Grant succeeded.

SQL> connect test/hokeypassword
Connected.
SQL> create table "OpenBOXTest" ("ObxTestCol2" number default 0, "OtherStuffHere" varchar2(40));

Table created.

SQL>
SQL>
SQL> alter table "OpenBOXTest" add constraint "OpenBOXTest_PK" primary

key("ObxTestCol2");

Table altered.

SQL>
SQL>
SQL> alter table "OpenBOXTest" drop constraint "OpenBOXTest_PK";

Table altered.

SQL>
SQL>
SQL> ALTER TABLE "OpenBOXTest" DROP COLUMN "ObxTestCol2" ;

Table altered.

SQL>
SQL>
SQL> ALTER TABLE "OpenBOXTest"  ADD ("ObxTestCol3" integer  DEFAULT 0);

Table altered.

SQL>
SQL>
SQL> alter table "OpenBOXTest" add constraint "OpenBOXTest_PK" primary

key("ObxTestCol3");

Table altered.

SQL> The SYSTEM tablespace is never a proper location for user objects. Stop using it as a repository for every testbed object you could possibly think to create.

David Fitzjarrell Received on Fri Oct 13 2006 - 08:25:14 CDT