Oracle Schemea Objects are taking up most space

How much space does the Oracle Schema take in Database?  How many objects are in each Oracle Schema?

Show Schema Object per Database and total size in megabytes 

set lines 200
set pages 999
col "Owner" format a15
col "MB's" format 999,999,999
col "Obj" format 999,999,999
select    obj.owner "Owner"
,    obj_cnt "Objects"
,    decode(seg_size, NULL, 0, seg_size) "MB's"
from     (select owner, count(*) obj_cnt from dba_objects group by owner) obj
,    (select owner, ceil(sum(bytes)/1024/1024) seg_size
    from dba_segments group by owner) seg
where     obj.owner  = seg.owner(+)
order    by 3 desc ,2 desc, 1
/

Show Schema Object per Database and total size in megabytes 

set lines 200
set pages 999
col "Owner" format a15
col "GB's" format 999,999,999
col "Obj" format 999,999,999
select    obj.owner "Owner"
,    obj_cnt "Objects"
,    decode(seg_size, NULL, 0, seg_size) "GB's"
from     (select owner, count(*) obj_cnt from dba_objects group by owner) obj
,    (select owner, ceil(sum(bytes)/1024/1024/1024) seg_size
    from dba_segments group by owner) seg
where     obj.owner  = seg.owner(+)
order    by 3 desc ,2 desc, 1
/

Check Oracle Database Size Megabytes or Gigabytes

Query to Check the Oracle Database Size in Megabytes or Gigaybtes?

Database Size in Megabytes 

set lines 200
set pages 999
col "DBSize" format a10
col "Free" format a10
col "Used" format a10
select    round(sum(used.bytes) / 1024 / 1024  ) || ' MB' "DBSize"
,    round(sum(used.bytes) / 1024 / 1024  ) - 
    round(free.p / 1024 / 1024 ) || ' MB' "Used"
,    round(free.p / 1024 / 1024 ) || ' MB' "Free"
from    (select    bytes
    from    v$datafile
    union    all
    select    bytes
    from     v$tempfile
    union     all
    select     bytes
    from     v$log) used
,    (select sum(bytes) as p
    from dba_free_space) free
group by free.p
/

 

Database Size in Gigabytes 

set lines 200
set pages 999
col "DBSize" format a10
col "Free" format a10
col "Used" format a10
select    round(sum(used.bytes) / 1024 / 1024 / 1024  ) || ' GB' "DBSize"
,    round(sum(used.bytes) / 1024 / 1024 / 1024  ) - 
    round(free.p / 1024 / 1024 / 1024 ) || ' GB' "Used"
,    round(free.p / 1024 / 1024 / 1024 ) || ' GB' "Free"
from    (select    bytes
    from    v$datafile
    union    all
    select    bytes
    from     v$tempfile
    union     all
    select     bytes
    from     v$log) used
,    (select sum(bytes) as p
    from dba_free_space) free
group by free.p
/

Display all the Oracle Database Features usage Statistics

Display all the Oracle Database Features usage Statistics 

set lines 200 
set pages 999
select  name ,
        detected_usages
from    dba_feature_usage_statistics
where    detected_usages > 0;

 

Some to the features available are:

Active Data Guard – Real-Time Query on Physical Standby

Active Data Guard, a set of services, is being used to enhance Data Guard

ADDM

ADDM has been used.

ADVANCED Index Compression

ADVANCED Index Compression is used.

Advanced Replication

Advanced Replication has been enabled.

Application Express

Application Express feature is being used.

ASO native encryption and checksumming

ASO network native encryption and checksumming is being used.

Automatic Maintenance – Optimizer Statistics Gathering

Automatic initiation of Optimizer Statistics Collection

Automatic Maintenance – Space Advisor

Automatic initiation of Space Advisor

Automatic Maintenance – SQL Tuning Advisor

Automatic initiation of SQL Tuning Advisor

Automatic Memory Tuning

Automatic Memory Tuning is enabled.

Automatic Segment Space Management (system)

Extents of locally managed tablespaces are managed automatically by Oracle.

Automatic Segment Space Management (user)

Extents of locally managed user tablespaces are managed automatically by Oracle.

Automatic SGA Tuning

Automatic SGA Tuning is enabled.

Automatic SQL Execution Memory

Sizing of work areas for all dedicated sessions (PGA) is automatic.

Automatic SQL Tuning Advisor

Automatic SQL Tuning Advisor has been used.

Automatic Storage Management

Automatic Storage Management has been enabled

Automatic Undo Management

Oracle automatically manages undo data using an UNDO tablespace.

Automatic Workload Repository

A manual Automatic Workload Repository (AWR) snapshot was taken in the last sample period.

AWR Baseline

At least one AWR Baseline has been created by the user

AWR Baseline Template

At least one AWR Baseline Template has been created by the user

AWR Report

At least one Workload Repository Report has been created by the user

Backup and Restore of plugged database

Backup and Restore of plugged database by RMAN is used.

Backup BASIC Compression

BASIC compressed backups are being used.

Backup BZIP2 Compression

BZIP2 compressed backups are being used.

Backup Encryption

Encrypted backups are being used.

Backup HIGH Compression

HIGH compressed backups are being used.

Backup LOW Compression

LOW compressed backups are being used.

Backup MEDIUM Compression

MEDIUM compressed backups are being used.

Backup Rollforward

Backup Rollforward strategy is being used to backup the database.

Backup ZLIB Compression

ZLIB compressed backups are being used.

Baseline Adaptive Thresholds

Adaptive Thresholds have been configured.

Baseline Static Computations

Static baseline statistics have been computed.

Bigfile Tablespace

Bigfile tablespace is being used

Block Media Recovery

Block Media Recovery is being used to repair the database.

Change-Aware Incremental Backup

Track blocks that have changed in the database.

Character Semantics

Character length semantics is used in Oracle Database

Character Set

Character set is used in Oracle Database

Client Identifier

Application User Proxy Authentication: Client Identifier is used at this specific time.

Clusterwide Global Transactions

Clusterwide Global Transactions is being used.

Compression Advisor

Compression Advisor has been used.

Concurrent Statistics Gathering

Concurrent Statistics Gathering has been used

CONVERT command

RMAN's CONVERT command used by the database.

Cross-Platform Backups

Cross-Platform Backup and Restore used by the database.

Crossedition Triggers

Crossedition triggers is being used.

CSSCAN

Oracle Database has been scanned at least once for character set: CSSCAN has been run at least once.

Data Guard

Data Guard, a set of services, is being used to create, maintain, manage, and monitor one or more standby databases.

Data Mining

There exist Oracle Data Mining models in the database.

Data Recovery Advisor

Data Recovery Advisor (DRA) is being used to repair the database.

Data Redaction

Data redaction is being used. There is at least one policy that is defined.

Database Migration Assistant for Unicode

Database Migration Assistant for Unicode has been used

Database Replay: Workload Capture

Database Replay: Workload was ever captured.

Database Replay: Workload Replay

Database Replay: Workload was ever replayed.

Database Resident Connection Pooling (DRCP)

Active Database Resident Connection Pool has been detected.

DBFS Content

Oracle Database FileSystem Content feature is being used.

DBFS HS

Oracle Database FileSystem Hierarchical Store feature is being used.

DBFS SFS

Oracle Database FileSystem SecureFile Store feature is being used.

DBMS_STATS Incremental Maintenance

DBMS_STATS Incremental Maintenance has been used.

Deferred Open Read Only

Deferred open read only feature is enabled

Deferred Segment Creation

Deferred Segment Creation is being used.

Direct NFS

Direct NFS is being used to connect to an NFS server.

Duplicate Db from Active Db using BackupSet

Duplicate from Active Database using BackupSet is used.

Dynamic SGA

The Oracle SGA has been dynamically resized through an ALTER SYSTEM SET statement.

Editioning Views

Editioning views is being used.

Editions

Editions is being used.

EM Cloud Control

EM Cloud Control Database Home Page has been visited at least once.

EM Performance Page

EM Performance Page has been visited at least once.

Encrypted Tablespaces

Encrypted Tablespaces is enabled.

Exadata

Exadata is being used.

Extensibility

Extensibility feature is being used.

File Mapping

File Mapping, the mechanism that shows a complete mapping of a file to logical volumes and physical devices, is being used.

Fine Grained Audit

Fine Grained Audit in use.

Flashback Data Archive

Flashback Data Archive, a historical repository of changes to data contained in a table, is used

Flashback Database

Flashback Database, a rewind button for the database, is enabled

GDS Catalog

Database is a GDS catalog database.

Global Data Services

Database contains global services.

GoldenGate

Oracle GoldenGate Capabilities are in use.

HeapCompression

Heap Compression is being used

Heat Map

Heat Map is used

Hybrid Columnar Compression

Hybrid Columnar Compression is used

Hybrid Columnar Compression Row Level Locking

Hybrid Columnar Compression Row Level Locking is used.

Information Lifecycle Management

Information Lifecycle Management is used

Instance Caging

Instance Caging is being used to limit the CPU usage by the database instance.

Internode Parallel Execution

Internode Parallel Execution is being used.

Job Scheduler

Job Scheduler feature is being used.

Label Security

Oracle Label Security is being used.

LOB

Persistent LOBs are being used.

Locally Managed Tablespaces (system)

There exists tablespaces that are locally managed in the database.

Locally Managed Tablespaces (user)

There exists user tablespaces that are locally managed in the database.

Locator

There is at least one usage of the Oracle Locator feature usage table.

Logfile Multiplexing

Multiple members are used in a single log file group

Long-term Archival Backup

Long-term archival backups are being used.

Materialized Views (User)

User Materialized Views exist in the database

Messaging Gateway

Messaging Gateway, that enables communication between non-Oracle messaging systems and Advanced Queuing (AQ), link configured.

MTTR Advisor

Mean Time to Recover Advisor is enabled.

Multi Section Backup

Multi section backups are being used.

Multiple Block Sizes

Multiple Block Sizes are being used with this database.

Object

Object feature is being used.

OLAP – Analytic Workspaces

OLAP – the analytic workspaces stored in the database.

OLAP – Cubes

OLAP – number of cubes in the OLAP catalog that are fully mapped and accessible by the OLAP API.

Online Move Datafile

Online Move Datafile is being used to move data files

Online Redefinition

Online Redefinition is being used.

Oracle Advanced Network Compression Service

Oracle Advanced Network Compression Service used.

Oracle Database Vault

Oracle Database Vault is being used

Oracle In-Database Hadoop

In-Database Hadoop for running MapReduce in java

Oracle Java Virtual Machine (system)

OJVM default system users.

Oracle Java Virtual Machine (user)

OJVM has been used by at least one non-system user.

Oracle Managed Files

Database files are being managed by Oracle.

Oracle Multimedia

Oracle Multimedia has been used

Oracle Multimedia DICOM

Oracle Multimedia DICOM (Digital Imaging and COmmunications in Medicine) has been used

Oracle Multitenant

Oracle Multitenant option is being used.

Oracle Secure Backup

Oracle Secure Backup is used for backups to tertiary storage.

Oracle Text

Oracle Text is being used – there is at least one Oracle text index

Oracle Utility Datapump (Export)

Oracle Utility Datapump (Export) has been used.

Oracle Utility Datapump (Import)

Oracle Utility Datapump (Import) has been used.

Oracle Utility External Table

Oracle Utility External Table has been used.

Oracle Utility Metadata API

Oracle Utility (Metadata API) has been used.

Oracle Utility SQL Loader (Direct Path Load)

Oracle Utility SQL Loader (Direct Path Load) has been used.

Parallel SQL DDL Execution

Parallel SQL DDL Execution is being used.

Parallel SQL DML Execution

Parallel SQL DML Execution is being used.

Parallel SQL Query Execution

Parallel SQL Query Execution is being used.

Partitioning (system)

Oracle Partitioning option is being used – there is at least one partitioned object created.

Partitioning (user)

Oracle Partitioning option is being used – there is at least one user partitioned object created.

Pillar Storage

Tablespaces stored on Oracle Pillar Axiom Storage

Pillar Storage with EHCC

EHCC used on tablespaces stored on Oracle Pillar Axiom Storage.

PL/SQL Native Compilation

PL/SQL Native Compilation is being used – there is at least one natively compiled PL/SQL library unit in the database.

Privilege Capture

Privilege Capture is being used.

Quality of Service Management

Quality of Service Management has been used.

Read Only Tablespace

Read only tablespace is being used

Real Application Clusters One Node

Real Application Clusters (Oracle RAC) One Node is being used.

Real Application Clusters (RAC)

Oracle Real Application Clusters (Oracle RAC) is configured.

Real Application Security

Oracle Real Application Security is being used.

Real-Time SQL Monitoring

Real-Time SQL Monitoring Usage.

Recover Table

Recover Table is used to recover a table in the database.

Recover Until Snapshot

Recover until snapshot is used to recover the database.

Recovery Area

The recovery area is configured.

Recovery Manager (RMAN)

Recovery Manager (RMAN) is being used to backup the database.

Resource Manager

Oracle Database Resource Manager is being used to manage database resources.

Restore Point

Restore Points are being used as targets for Flashback

Result Cache

The Result Cache feature has been used.

RMAN – Disk Backup

Recovery Manager (RMAN) is being used to backup the database to disk.

RMAN – Tape Backup

Recovery Manager (RMAN) is being used to backup the database to tape.

Rules Manager

Rules Manager and Expression Filter

SecureFile Compression (system)

SecureFile Compression is being used by system users

SecureFile Compression (user)

SecureFile Compression is being used

SecureFile Deduplication (system)

SecureFile Deduplication is being used by system users

SecureFile Deduplication (user)

SecureFile Deduplication is being used

SecureFile Encryption (system)

SecureFile Encryption is being used by system users

SecureFile Encryption (user)

SecureFile Encryption is being used

SecureFiles (system)

SecureFiles is being used by system users

SecureFiles (user)

SecureFiles is being used

Segment Advisor (user)

Segment Advisor has been used. There is at least one user task executed.

Segment Maintenance Online Compress

Segment Maintenance Online Compress

Segment Shrink

Segment Shrink has been used.

Semantics/RDF

A semantic network has been created indicating usage of the Oracle Semantics Feature.

Server Flash Cache

Database Smart Flash Cache is being used with this database.

Server Parameter File

The server parameter file (SPFILE) was used to startup the database.

Services

Oracle Services.

Shared Server

The database is configured as Shared Server, where one server process can service multiple client programs.

Spatial

There is at least one usage of the Oracle Spatial feature usage table.

SPM Evolve Advisor

SPM Evolve Advisor has been used.

SQL Access Advisor

SQL Access Advisor has been used.

SQL Monitoring and Tuning pages

EM SQL Monitoring and Tuning pages has been visited at least once.

SQL Performance Analyzer

SQL Performance Analyzer has been used.

SQL Plan Directive

SQL plan directive has been used

SQL Plan Management

SQL Plan Management has been used.

SQL Profile

SQL profiles have been used.

SQL Repair Advisor

SQL Repair Advisor has been used.

SQL Tuning Advisor

SQL Tuning Advisor has been used.

SQL Tuning Set (system)

A SQL Tuning Set has been created in the database in the SYS schema.

SQL Tuning Set (user)

A SQL Tuning Set has been created in the database in a user schema.

SQL Workload Manager

SQL Workload Manager has been used.

Streams (system)

Oracle Streams processes have been configured

Streams (user)

Users have configured Oracle Streams AQ

Sun ZFS with EHCC

EHCC used on tablespaces stored on Oracle Sun ZFS Storage

Traditional Audit

Traditional Audit in use.

Transparent Data Encryption

Transparent Database Encryption is being used. There is at least one column or tablespace that is encrypted.

Transparent Gateway

Heterogeneous Connectivity, access to a non-Oracle system, has been configured.

Transparent Sensitive Data Protection

Transparent Sensitive Data Protection (TSDP).

TRANSPORT TABLESPACE command

RMAN'S TRANSPORT TABLESPACE command used by the database.

Transportable Tablespace

Transportable tablespace is being used

Tune MView

Tune MView has been used.

Undo Advisor

Undo Advisor has been used.

Unified Audit

Unified Audit in use.

Very Large Memory

Very Large Memory is enabled.

Virtual Private Database (VPD)

Virtual Private Database (VPD) policies are being used.

Workspace Manager

There is at least one version enabled table.

XDB

XDB feature is being used.

XStream In

Oracle XStream Inbound servers have been configured.

XStream Out

Oracle XStream Outbound servers have been configured.

XStream Streams

Oracle Streams with XStream functionality has been configured.

ZFS Storage

Tablespaces stored on Oracle Sun ZFS Storage.

Create Oracle Script To Count All Rows In Each Table

Create Script to count rows in each table.  

The Script Looks as follows:

 

set lines 200
set pages 200
select 'Select count(*) from ' ||owner|| '.' ||table_name|| ';' from dba_all_tables order by owner, table_name;

 

 

This will produce a script that looks like:

 

Select count(*) from XDBPM.REVISED_CHOICE_MODEL;
Select count(*) from XDBPM.REVISED_COLL_TYPES;
Select count(*) from XDBPM.REVISED_TYPES;
Select count(*) from XDBPM.REVISED_TYPE_ATTRS;
Select count(*) from XDBPM.REVISED_TYPE_SUMMARY;
Select count(*) from XDBPM.SQLLDR_STAGING_TARGET;
Select count(*) from XDBPM.STORAGE_MODEL_CACHE;
Select count(*) from XDBPM.TYPE_SUMMARY;
Select count(*) from XDBPM.XDBPM_INDEX_DDL_CACHE;

Display All The Oracle Tables In The Database

 

Display All Oracle Tables in the database.  Shows all tables(less the system tables.   Change for your databases.  With where statement like  "where owner in 'YOUROWNERNAME'"

 

 

set lines 200
set pages 200
select owner, table_name, table_type, tablespace_name 
from dba_all_tables 
where owner not in ('SYSTEM','DBSNMP','ORDSYS','OUTLN','SYS') 
order by owner, table_name, tablespace_name