Stay tuned for some banter about CHAR to NCHAR, it's my next port of call!
List your table spaces and space remaining:
SELECT
df.tablespace_name AS "Tablespace",
df.bytes / (1024 * 1024 * 1024) AS "Size (GB)",
Trunc(fs.bytes / (1024 * 1024 * 1024)) AS "Free (GB)"
FROM
(
SELECT
tablespace_name,
Sum(bytes) AS bytes
FROM
dba_free_space
GROUP BY
tablespace_name
) fs,
(
SELECT
tablespace_name,
SUM(bytes) AS bytes
FROM
dba_data_files
GROUP BY
tablespace_name
) df
WHERE
fs.tablespace_name = df.tablespace_name
ORDER BY 3 desc
Tablespace Size (GB) Free (GB)
------------------------------ ---------------------- ----------------------
PRODDTAT 12.05078125 7
UNDOTBS1 3.7109375 3
TESTDTAT 21.953125 2
TESTDTAI 20.953125 1
PRODDTAI 1.953125 1
SYS7333T 0.029296875 0
List your datafiles and amount used:
Now, list your database files and how much of them are being used, whether they are autoextend
SELECT substr(df.file_name,1,45),
df.tablespace_name,
df. status,
(df.bytes/1024000) t,
round((fs.s/df.bytes*100),2) p,
decode (ae.y,1,'YES','NO') a
FROM dba_data_files df,
(SELECT file_id,SUM(bytes) s
FROM dba_free_space
GROUP BY file_id) fs,
(SELECT file#, 1 y
FROM sys.filext$
GROUP BY file#) ae
WHERE df.file_id = fs.file_id
AND ae.file#(+) = df.file_id
--AND df.tablespace_name in ('TESTDTAT', 'TESTDTAI')
ORDER BY t desc;
/u02/jdedwardsppack/ORCL/Tables/proddtat02.db PRODDTAT AVAILABLE 10485.76 60.23 NO
/u02/jdedwardsppack/ORCL/Tables/testdtat03.db TESTDTAT AVAILABLE 10485.76 2.11 NO
/u02/jdedwardsppack/ORCL/Tables/testdtat05.db TESTDTAT AVAILABLE 5242.88 41.99 NO
/u02/oracle/oradata/jdepoc/undotbs01.dbf UNDOTBS1 AVAILABLE 3891.2 91 YES
/u02/jdedwardsppack/ORCL/Tables/dv910t02.dbf DV910T AVAILABLE 3686.4 16.14 YES
/u02/jdedwardsppack/ORCL/Tables/testdtai05.db TESTDTAI AVAILABLE 2097.152 70.75 NO
If you are running out of space, add some more:
ALTER TABLESPACE testdtat ADD DATAFILE '/u02/jdedwardsppack/ORCL/Tables/testdtat04.dbf' SIZE 5G ;
Check that the physical file system can extend of course (df -k /u02)
If you need some space back, you could try a:
alter database datafile '/u02/jdedwardsppack/ORCL/Tables/proddtat03.dbf' RESIZE 100M ;
>emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
https://aubdc00-jdb01t:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is not running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/112/aubdc00-jdb01t_jdepoc/sysman/log
The above gives you the URL to go to for the dbconsole. This is a great graphical way of doing everything easily. You can chuck the above scripts in the bin and click all your problems away! (of course, unless you are like me where the console will not start :-(
