| Code Block |
|---|
SELECT NVL(b.tablespace_name,
NVL(a.tablespace_name,'UNKOWN')) name,
round(kbytes_alloc/1024,2) Mbytes,
round((kbytes_alloc-NVL(kbytes_free,0))/1024,2) used,
round(NVL(kbytes_free,0)/1024,2) free,
round(((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100,2) pct_used
FROM ( SELECT SUM(bytes)/1024 Kbytes_free,
tablespace_name
FROM sys.dba_free_space
GROUP BY tablespace_name ) a,
( SELECT SUM(bytes)/1024 Kbytes_alloc,
SUM(maxbytes)/1024 kbytes_max,
tablespace_name
FROM sys.dba_data_files
GROUP BY tablespace_name )b
WHERE a.tablespace_name (+) = b.tablespace_name
ORDER BY PCT_USED; |
| Code Block |
|---|
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks; |
see table spaces
| Code Block |
|---|
SELECT file_name, autoextensible, tablespace_name FROM sys.dba_data_files; |
...
| Code Block |
|---|
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/ts_DB.dbf' RESIZE 2G; |
| Code Block |
|---|
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/XE/temp.dbf' RESIZE 10G; |
Linux console
| Code Block |
|---|
netstat -n |grep ESTA |grep 1521|cut -d ':' -f8|sort|uniq -c |