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 ---------------------------------------------------------------
