I have also noticed this behavior while working on a fix for JIRA
TRAFODION-1789. IMHO, we should drop the schema without requiring cascade
if only system created objects exist. In fact, I was planning to deliver
this behavioral change as part of the fix for TRAFODION-1789.
Roberta
*From:* Dave Birdsall [mailto:[email protected]]
*Sent:* Tuesday, February 2, 2016 3:16 PM
*To:* [email protected]
*Subject:* RE: Dropping Schema blocked if histograms exist?
Hi,
I have run into this too. DROP SCHEMA treats the SB_HISTOGRAMS and
SB_HISTOGRAMS_INTERVALS as user tables. So when those exist, I either have
to explicitly drop them or use CASCADE on DROP SCHEMA.
I have no opinion on whether this is correct behavior or not.
Dave
*From:* Carol Pearson [mailto:[email protected]]
*Sent:* Tuesday, February 2, 2016 3:05 PM
*To:* [email protected]
*Subject:* Dropping Schema blocked if histograms exist?
I was trying to drop a schema after I updated statistics on the table. I'd
dropped all of the objects that I created in the schema (one table,
cjpjunk, because that's all I was testing...) but my drop schema failed:
>>drop schema test_sandbox_schema;
*** ERROR[1028] The schema must be empty. It contains at least one object
SB_HISTOGRAMS.
--- SQL operation failed with errors.
So I selected from the OBJECTS metadata table to see what's in the
test_sandbox_schema, and all I see are histograms tables, created when I
updated stats on good ol' cjpjunk:
>>select distinct object_name from "_MD_".objects where schema_name like
'TEST_SANDBOX_SCHEMA%';
OBJECT_NAME
-----------------------------------------------------------------------------------
SB_HISTOGRAMS
SB_HISTOGRAMS_PK
SB_HISTOGRAM_INTERVALS
SB_HISTOGRAM_INTERVALS_PK
__SCHEMA__
--- 5 row(s) selected.
Nothing but system-created tables, and the SB_HISTOGRAMS tables exist only
because I did an UPDATE STATISTICS command.
Now, I'm able to drop the schema with a drop schema cascade command:
>>drop schema test_sandbox_schema cascade;
--- SQL operation complete.
but why was cascade required? Shouldn't I be able to drop the schema
without cascade since I didn't physically create any of these objects, and
wouldn't generally need to be aware of them from a user perspective?
Thanks!
-Carol P.
---------------------------------------------------------------
Email: [email protected]
Twitter: @CarolP222
---------------------------------------------------------------
