Oracle DBA tips for CNC

Sure, you might know it all about databases, but this is not for you.  This is for me, when I forget all of these commands next week...  I've got somewhere to go!

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 ;

Note that all of the above is pretty hard.  If you have command line on the DB server, check if emctl is running:

>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 :-(