Oracle
Contenido
Tools
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)
Errors
Codes
- ORA-7306, ORA-7336, ORA-7329
Oracle received a system error on a shmget() call. The system error should be reported. There are a few possibilities:
There is insufficient shared memory available. This is indicated by the operating system error ENOSPC. Most likely, SHMMNI is too small. Alternatively, there may shared memory already allocated; if it is not attached, perhaps it can be freed. Maybe shared memory isn't configured in the kernel. There is insufficient memory available. Remember, shared memory needs pages of virtual memory. The system error ENOMEM indicates there is insufficient virtual memory. Swap needs to be increased, either by adding more or by freeing currently used swap (i.e. free other shared memory, kill other processes) The size of the shared memory segment requested is invalid. In this case, EINVAL is returned by the system. This should be very rare - however, it is possible. This can occur if SHMMAX is not a muliple of the page size and Oracle is trying a multi-segment model. Remember that Oracle rounds its calculation of SHMMAX to a page boundary, so it may have rounded it up past the real SHMMAX! (Whether this is a bug is debatable). The shared memory segment does not exist. This would be indicated by the system error ENOENT. This would never happen on startup; it only would happen on connects. The shared memory most likely has been removed unexpectedly by someone or the instance is down.
- ORA-7307, ORA-7337, ORA-7320
Oracle received a system error on a shmat() call. The system error should be reported. There a a few possibilities: The attach address is bad. If this is the cause, EINVAL is returned by the system. Refer to the section on the attach address to see why the attach address might be bad. This may happen after enlarging the SGA. The permissions on the segment do not allow the process to attach it. The operating system error will be EACCES. Generally the cause of this is either the setuid bit is not turned on for the oracle executable, or root started the database (and happens to own the shared memory). Normally, this would be seen only on connects. The process cannot attach any more shared memory segments. This would be accompanied by the system error EMFILE. SHMSEG is too small. Note that as long as SHMSEG is greater than SS_SEG_MAX, you should never see this happen.
- ORA-7329, ORA-7334
Oracle has determined the SGA needs too many shared memory segments. Since you can't change the limit on the number of segments, you should instead increase SHMMAX so that fewer segments are required.
- ORA-7339
Oracle has determined it needs too many semaphore sets. Since you can't change the limit on the number of semaphore sets, you should increase SEMMSL so fewer sets are required.
- ORA-7250, ORA-7279, ORA-7252
Oracle received a system error on a semget() call. The system error should be reported. There should be only one system error ever returned with this, ENOSPC. This can mean one of two things. Either the system limit on sempahore sets has been reached or the system limit on the total number of semaphores has been reached. Raise SEMMNI or SEMMNS, as is appropriate, or perhaps there are some semaphore sets which can be released. In the case of ORA-7250, ORANSEMS may be set too high (>SEMMSL). If it is, raise SEMMSL or decrease ORANSEMS.
- ORA-7251
Oracle failed to allocate even a semaphore set of only one semaphore. It is likely that semaphores are not configured in the kernel.
- ORA-7318
Oracle could not open the sgadef file. The system error number will be returned. There are a few possible causes:
The file doesn't exist. In this case, the system error ENOENT is returned. Maybe ORACLE_SID or ORACLE_HOME is set wrong so that Oracle is looking in the wrong place. Possibly the file does not exist (in this case, a restart is necessary to allow connections again). The file can't be accessed for reading. The operating system error returned with this is EACCES. The permissions on the file (or maybe directories) don't allow an open for reading of the sgadef file. It might not be owned by the oracle owner. The setuid bit might not be turned on for the oracle executable.
- ORA-7319
Oracle did not find all the data it expected when reading the sgadef.dbf file. Most likely the file has been truncated. The only recovery is to restart the instance.
- ORA-7430
Oracle expected a key to be used for the segment which does not match the key stored in the shared memory and semaphore data structure. This probably indicates a corruption of the sgadef file (in version 6) or the data in the first segment of the SGA (in version 7). A restart of the instance is probably necessary to recover in that case.It may also be a key collision problem and Oracle is attached to the wrong instance.
- ORA-7321
Oracle was able to attach the segment, but not at the address it requested. In most cases, this would be caused by corrupted data in the sgadefile (in version 6) or the first segment of the SGA (in version 7). A restart of the database may be necessary to recover.
- ORA-7324, ORA-7325
Oracle was unable to allocate memory. Most likely, the heap (data segment) has grown into the bottom of the SGA. Relocating the SGA to a higher attach address may help, but there may be other causes. Memory leaks can cause this error. The init.ora parameter sort_area_size may be too large, decreasing it may resolve the error. The init.ora parameter context_incr may also be too large, decreasing it may resolve this
- ORA-7264, ORA-7265
Oracle was unable to decrement/increment a semaphore. This generally is accompanied by the system error EINVAL and a number which is the identifier of the semaphore set. This is almost always because the semaphore set was removed, but the shadow process was not aware of it (generally due to a shutdown abort or instance crash). This error is usually ignorable.
