For Oracle 11g, the FETCH FIRST 10 ROWS ONLY
syntax doesn't work since it was introduced in Oracle 12c. Instead, you can use the ROWNUM
approach to limit the results to the top 10 largest tables.
Here’s how you can modify the query for Oracle 11g:
Top 10 Largest Tables in Oracle 11g
1. Using DBA_SEGMENTS
(For DBA Users)
SELECT * FROM (
SELECT owner,
segment_name AS table_name,
ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb
FROM dba_segments
WHERE segment_type = 'TABLE'
GROUP BY owner, segment_name
ORDER BY size_mb DESC
)
WHERE ROWNUM <= 10;
-
This query:
-
Retrieves the table name, schema name (
owner
), and size in MB for the top tables. -
Uses
ROWNUM <= 10
to return only the top 10 largest tables after sorting by size.
-
2. Using USER_SEGMENTS
(For Non-DBA Users)
If you only have access to your schema, use the USER_SEGMENTS
view.
SELECT * FROM (
SELECT segment_name AS table_name,
ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb
FROM user_segments
WHERE segment_type = 'TABLE'
GROUP BY segment_name
ORDER BY size_mb DESC
)
WHERE ROWNUM <= 10;
-
This query shows the top 10 largest tables in your schema.
3. Getting Top 10 Tables from a Specific Schema
If you're querying a specific schema, you can filter the results by schema name (OWNER
):
SELECT * FROM (
SELECT owner,
segment_name AS table_name,
ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb
FROM dba_segments
WHERE segment_type = 'TABLE'
AND owner = '<SCHEMA_NAME>'
GROUP BY owner, segment_name
ORDER BY size_mb DESC
)
WHERE ROWNUM <= 10;
-
Replace
<SCHEMA_NAME>
with the schema you want to query.
Key Points:
- The
ROWNUM
clause limits the number of rows returned to the top 10. - In Oracle 11g,
ROWNUM
needs to be used after the sorting in the outer query. DBA_SEGMENTS
provides information about all tables in the database (for DBAs), whileUSER_SEGMENTS
is specific to the current user's schema.
To get the top 10 largest tables in an Oracle database, you can query the DBA_SEGMENTS
or USER_SEGMENTS
views (depending on your privileges) and order the results by the size of the tables.
Query for Top 10 Largest Tables in Oracle 12c
1. Using DBA_SEGMENTS
(For DBA Users)
This query gives the top 10 largest tables across all schemas in the database.
SELECT owner,
segment_name AS table_name,
segment_type,
ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb
FROM dba_segments
WHERE segment_type = 'TABLE'
GROUP BY owner, segment_name, segment_type
ORDER BY size_mb DESC
FETCH FIRST 10 ROWS ONLY;
- This query retrieves the table name, schema name (
owner
), and the size of the table in MB. - It orders the tables by size in MB and returns the top 10 largest tables.
2. Using USER_SEGMENTS
(For Non-DBA Users)
If you're querying for tables within your own schema (and don't have access to DBA_SEGMENTS
), use USER_SEGMENTS
:
SELECT segment_name AS table_name,
ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb
FROM user_segments
WHERE segment_type = 'TABLE'
GROUP BY segment_name
ORDER BY size_mb DESC
FETCH FIRST 10 ROWS ONLY;
- This query is similar but restricted to your user's tables only.
- It shows the top 10 largest tables in your schema by size in MB.
3. Getting Size for Specific Schema
If you're only interested in the top 10 tables from a specific schema, you can modify the query by adding a filter for the schema name (i.e., OWNER = '<SCHEMA_NAME>'
).
SELECT owner,
segment_name AS table_name,
ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb
FROM dba_segments
WHERE segment_type = 'TABLE'
AND owner = '<SCHEMA_NAME>'
GROUP BY owner, segment_name
ORDER BY size_mb DESC
FETCH FIRST 10 ROWS ONLY;
-
Replace
<SCHEMA_NAME>
with the schema you're interested in.
Key Notes:
DBA_SEGMENTS
shows the total space used by all segments, including tables, indexes, etc., for all schemas in the database. It's the most comprehensive view if you have access.USER_SEGMENTS
limits the query to the current user's schema, useful if you're not a DBA.FETCH FIRST 10 ROWS ONLY
is the SQL standard way to limit results to the top 10 in newer versions of Oracle (12c+). In older versions, you might need to useROWNUM
.