Hi Roberta,
Thanks for including this change along with the existing JIRA as it would complete the scenario.. “Create the histogram tables when schema is created”… “Drop the histogram tables when schema is dropped”… Thanks Venkat *From:* Roberta Marton [mailto:[email protected]] *Sent:* Tuesday, February 02, 2016 3:21 PM *To:* [email protected] *Subject:* RE: Dropping Schema blocked if histograms exist? 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 ---------------------------------------------------------------
