Re: How to update Oracle database from Access database ?

damorgan_at_exesolutions.com said...
> sean wrote:
>
> > I have huge amount of data in Access (on Win98) that I need to update
> > to Oracle 7.3.4 database. But first I need to clear /delete the
> > content of each tables in the Oracle. I've tried using "delete * from
> > my_table" under SQL*Plus or svrmgr23 but doesnt work.
> >
> > I know I can use Msaccess to delete and append data to oracle, via
> > linked tables. But its taking hours...
> >
> > Appreciate any help.
> > sean
>
> Use
>
> TRUNCATE TABLE <table_name>;
>
> The correct delete statement, which I would advise against using, is:
>
> DELETE FROM <table_name>;
>
> And get yourself out of server manager. You have no business doing
> anything there except DBA specific functions that can not be done as
> another user. Keep this up and you will likely trash the entire database.
>
> Daniel Morgan
>
>

One caveat on TRUNCATE TABLE:

If you have FK constraints enabled, you'll get "ORA-02266: unique/primary keys in table referenced by enabled foreign keys" when you try to truncate the table (even if the table is empty).

Just ran into that today when I suggested a developer use TRUNCATE since it moves the highwater mark to the beginning of the data area.

-- 
/Karsten
DBA > retired > DBA