Gaius is right, use DBA_TABLES
for NUM_ROWS
and DBA_SEGMENTS
for size:
select owner,table_name,num_rows,last_analyzed from dba_tables;
The num_rows count is as of LAST_ANALYZED
date, which should be close enough even without running DBMS_STATS.
For sizing:
select owner,segment_type,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_type;
or (depending what level of details you need):
select owner,segment_name,sum(bytes)/1024/1024 size_mb
from dba_segments
group by owner,segment_name;
For LOBs you'll need to join it to DBA_LOBS
, for indexes to DBA_INDEXES
, for tables to DBA_TABLES
. A lot will depend on your specific requirement. Since you mentioned you are trying to figure out used space in tablespace it might be as simple as:
select sum(bytes)/1024/1024 size_mb
from dba_segments
where tablespace_name='YOUR_TBLSP_NAME';
Your solution would really work only with small DB as it would not be feasible to read all data on large DB.
I am hesitant to post how to address ORA-1555
as in this case this it NOT your primary issue but just for completeness - you are on 10g using automatic undo management so your DBA would have to increase undo_retention
in your database (the ixora link is relevant to a DB without auto undo management).
Martin Smith's answer will serve very well to get you all the columns you need for an entire database in SQL 2008. Very nice!
Here is how I did it in the days before SQL had CTEs and PIVOT. This will be compatible with older versions of SQL where Martin's solution won't work, and still works in 2008 as well, but with poorer performance than his solution.
USE MyDB
SET NOCOUNT ON
CREATE TABLE ##nullable (
ID INT IDENTITY(1,1),
SchName VARCHAR(128),
TblName VARCHAR(128),
ColName VARCHAR(128),
hasNulls BIT,
PRIMARY KEY(ID)
)
DECLARE @currTbl VARCHAR(128)
DECLARE @currCol VARCHAR(128)
DECLARE @currSch VARCHAR(128)
DECLARE @limit INT
DECLARE @i INT
DECLARE @sql NVARCHAR(4000)
INSERT INTO ##nullable (
SchName,
TblName,
ColName,
hasNulls
)
SELECT
c.TABLE_SCHEMA,
c.TABLE_NAME,
c.COLUMN_NAME,
0 AS hasNulls
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.IS_NULLABLE = 'YES'
AND t.TABLE_TYPE = 'BASE TABLE'
SET @limit = (SELECT MAX(ID) FROM ##nullable)
SET @i = 1
WHILE @i <= @limit
BEGIN
SELECT @currSch = SchName,
@currTbl = TblName,
@currCol = ColName
FROM ##nullable
WHERE ID = @i
SET @sql = 'UPDATE ##nullable
SET hasNulls = 1
WHERE ID = ' + CAST(@i AS VARCHAR(20)) + '
AND EXISTS (SELECT 1 FROM ' + QUOTENAME(@currSch) + '.'+ QUOTENAME(@currTbl) + '
WHERE ' + QUOTENAME(@currCol) + ' IS NULL)'
EXEC(@sql)
SET @i = @i + 1
END
SELECT DISTINCT * FROM ##nullable
WHERE hasNulls = 0
DROP TABLE ##nullable
Best Answer
Simple! Put
NULL
without quotes.E.g.
We must always use
NULL
without quotes when we intend to use NULL values. Putting quotes around it, like'NULL'
makes it a string literal and it is treated as character data. Hence for using NULL values, we must always useNULL
without quotes, be it a null string, a null number or a null BLOB. For every data type, we must useNULL
without quotes.