OracleObjects & schema
Recycle bin contents by owner
Dropped objects still taking up space. Oracle reclaims it under space pressure, but a large recycle bin can make tablespace reports look worse than they are.
Not yet verified. How scripts are tested
1CLEAR COLUMNS2SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN owner FORMAT A254COLUMN objects FORMAT 999,9905COLUMN mb HEADING "MB" FORMAT 9,999,9906 7SELECT owner,8 COUNT(*) AS objects,9 ROUND(SUM(space) * (SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'db_block_size') / 1048576) AS mb10FROM dba_recyclebin11GROUP BY owner12ORDER BY mb DESC;Save it as ora-recyclebin.sql and run it with SQL> @ora-recyclebin.
Part of these runbooks
More Oracle scripts: Objects & schema
- Generate DDL for a table and an indexUses DBMS_METADATA to write the CREATE statements for a table and one of its indexes into ddl_list.sql in your current directory. Prompts for the…
- Invalid objectsEvery invalid object by owner and type, typically left behind by a deployment or patch. The recompile script fixes most of them.
- Recompile invalid objectsRecompile one schema, or everything in the database with Oracle's utlrp script. Run the invalid objects check again afterwards.
- Unusable indexes and index partitionsIndexes marked UNUSABLE, typically after a partition operation or a direct-path load. Queries that need them will either fail or fall back to full…
- Foreign keys that are probably not indexedForeign keys whose columns aren't the leading columns of an index. Unindexed foreign keys cause table-level locks when the parent row is updated or…
- Disabled constraints and triggersConstraints and triggers someone switched off, often for a data load, and never switched back on.