Drop tables using a script

[Former Member]

Hi all,

I would like to drop all tables belonging to one schema. Is this possible using a script without deleting the schema?

I have built a system using export/import syscopy procedure and would like to reimport everything.

Creating the whole database including data volumes took several hours.

I am not sure if I can just drop the schema and re-create it since I do not know if anything else gets deleted (user, roles, whatever) and if I would have to re-assign something else.

Thanks for your input.

Regards

[markus_doehr2 · Accepted solution]

> I have built a system using export/import syscopy procedure and would like to reimport everything.

> Creating the whole database including data volumes took several hours.

If you are talking about a SAP system:

In the past I was testing database imports with various options and used the following script to delete the database:

#!/bin/bash
# 
# reset database
# 
# 

dbmcli -U c db_admin
dbmcli -U c db_activate <dbauser,dbapassword>
dbmcli -U c load_systab -u <dbauser,dbapassword> -ud <domain>
dbmcli -U c db_admin
dbmcli -U c -uUTL -c util_execute SET LOG WRITER OFF
dbmcli -U c db_online

Activating the database will "just" format the log device. Since the volumes are already created activation is much faster than building the system from scratch.

You could also drop the user but you will need to wait until the Garbage Collectors have finished their work and the space is free'ed.

Markus

[lbreddemann · Accepted solution]

> dbmcli -U c -uUTL -c util_execute SET LOG WRITER OFF

DON'T GO FOR THAT!

If you don't want to care about the logs - just put them to overwrite mode.

Switching off the logwriting alltogether effectively disables the automatic savepoints, that usually occur at least every 10 minutes.

Imagine that your database crashes - it starts from the last savepoint. This may be a week old.

And since no log had been written since then: all the changes are lost.

So, do yourself a favour and skip that.

regards,

Lars

[markus_doehr2 · Accepted solution]

> > dbmcli -U c -uUTL -c util_execute SET LOG WRITER OFF

>

> DON'T GO FOR THAT!

>

> If you don't want to care about the logs - just put them to overwrite mode.

> Switching off the logwriting alltogether effectively disables the automatic savepoints, that usually occur at least every 10 minutes.

I agree.

But usual SAP-Installations (so bulk load) do exactly that when you install or copy a system using R3load

Markus

[lbreddemann · Accepted solution]

> But usual SAP-Installations (so bulk load) do exactly that when you install or copy a system using R3load

sure - but, although I hate to write that - in this case SAPINST knows better and 'normal' users should never think about this.

It's wrong that there is a GUI for that and it's wrong that this dangerous functionality is not documented/warned about well enough.

regards,

Lars

[markus_doehr2 · Accepted solution]

> sure - but, although I hate to write that - in this case SAPINST knows better and 'normal' users should never think about this.

> It's wrong that there is a GUI for that and it's wrong that this dangerous functionality is not documented/warned about well enough.

I agree - and I'm aware of the implications.

Another tip if you do database loads:

set

CLUSTER_WRITE_THRESHOLD = 0

before you start loading the database.

Markus

[lbreddemann]

HI Christian,

I guess my blog will show you how to do this:

[Questions to SAP Support: "How to generate a SQL Script via SQL on MaxDB?"|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/10588] [original link is broken] [original link is broken] [original link is broken];

Anyhow, if you fear that your scripts don't create everything you need, than you better review this and fix it.

regards,

Lars

[Former Member]

Hi,

thanks for your response.

So to drop all tables belonging to schema SAPR3 I would use following statement to create an appropriate SQL script?

SELECT TEXT FROM (

select rowno as line, 'DROP TABLE"' & table_name & '" ' as TEXT from ALL_TABLES

where owner = 'SAPR3'

union all

select rowno as line, '//' as TEXT from ALL_TABLES

where table_name is not null

) order by line, text desc

Thanks.

[lbreddemann]

> So to drop all tables belonging to schema SAPR3 I would use following statement to create an appropriate SQL script?

>

Yep - this should do it.

Anyhow, if it's about the SAPR3 - just drop the damn schema/user and recreate it.

There are no further special grants to be made.

Of course - in general it's not a very bright idea to perform database development for the SAP schema and bypass the ABAP dictionary...

If you want to implement something like an interface etc. better create a new schema/user + views (or procedures) and work in this one.

For that there is a blog available as well...

[Questions to SAP Support: Is ODBC access to the database a good idea?|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/9742] [original link is broken] [original link is broken] [original link is broken];

regards,

Lars

[Former Member]

Thanks Markus and Lars.

I have built my system using SAPInst (System Copy) until the point where SAPInst stopped for "Import Monitor", so all "SAP Inst Post Processing" is still to be done... Since I would like to import several times using different parameters I was looking for an easy way to clear the database of all tables/views which came by the import. This way I could re-import as often as necessary until I am satisfied with the result/runtime.

After that SAP Inst would continue doing the post processing steps.

Using Oracle it is more trouble than just dropping the schema/user and re-creating it so I was quite uncertain if this would be an option here.

I will try both methods. Thanks again.

Regards

[lbreddemann]

> Since I would like to import several times using different parameters I was looking for an easy way to clear the database of all tables/views which came by the import.

Ok, I almost forgot... this scenario is exactly what you can use SNAPSHOTS for.

Simply create a snapshot BEFORE you start the import and you can go back to it over and over again.

Simple, fast, safe. The tool of choice.

regards,

Lars

[Former Member]

Thanks again for the input.