Oracle

De gacq wiki
Revisión del 15:14 8 oct 2006 de WikiSysop (discusión | contribuciones) (Not checked)
Saltar a: navegación, buscar

Administracion

START/STOP

como essdbo

$ORACLE_HOME/bin/dbstart
$ORACLE_HOME/bin/lsnrctl start

$ORACLE_HOME/bin/dbshut
$ORACLE_HOME/bin/lsnrctl stop

SQLplus

http://lbdwww.epfl.ch/f/teaching/courses/oracle8i/server.815/a66736/toc.htm

Monitoring

Not checked

List free and used space in database:
SELECT sum(bytes)/1024 "free space in KB" FROM dba_free_space ORDER BY TABLESPACE_NAME;
SELECT sum(bytes)/1024 "used space in KB" FROM dba_segments ORDER BY TABLESPACE_NAME;


--------------------------------------------------------
-- free.sql
--
-- This SQL Plus script lists freespace by tablespace
--------------------------------------------------------

column dummy noprint
column  pct_used format 999.9       heading "%|Used"
column  name    format a16      heading "Tablespace Name"
column  Kbytes   format 999,999,999    heading "KBytes"
column  used    format 999,999,999   heading "Used"
column  free    format 999,999,999  heading "Free"
column  largest    format 999,999,999  heading "Largest"
break   on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report

select nvl(b.tablespace_name,
             nvl(a.tablespace_name,'UNKOWN')) name,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
       ((kbytes_alloc-nvl(kbytes_free,0))/
                          kbytes_alloc)*100 pct_used,
       nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by &1
/
-------------------------------------------------------

It generates a report like:


Tablespace Name     KBytes      Used      Free   Used   Largest
---------------- --------- --------- --------- ------ ---------
ATCDEMO_DATA         3,072     2,048     1,024   66.7       704
ATCDEMO_IDX            504         8       496    1.6       496
....

                 --------- --------- ---------
sum              5,099,912 4,381,104   718,808


What that shows me is:

Kbytes = space allocated to the tablespace currently.
Used   = space allocated within the tablespace to specific 
         objects
Free   = space NOT yet allocated to any objects
Used   = % of space allocated to objects in tablespace
Largest= Largest free contigous extent available (NEXT_EXTENTS 
         larger then this will FAIL)