Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
There may be a slowdown when you query dba_free_space or tablespaces via Toad on 12C databases. The slowness is due to the fact that the sys schema, data dictionary and fixed object statistics are out of date. In addition, the presence of too many objects in the recyclebin can cause slowdown. In this case, the statistics should be updated and the objects in the recyclebin should be cleared.
You can update the statistics as below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [oracle@db01 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu May 11 11:48:34 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> EXEC dbms_stats.gather_schema_stats('SYS', cascade=>TRUE); PL/SQL procedure successfully completed. SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; PL/SQL procedure successfully completed. SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NO_INVALIDATE => FALSE); PL/SQL procedure successfully completed. |
You can delete objects within the recyclebin as follows at database basis:
1 2 3 4 5 6 7 8 9 10 11 12 13 | [oracle@db01 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu May 11 11:48:34 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> purge dba_recyclebin; DBA Recyclebin purged. |
