lunedì 14 maggio 2012

Alcune query ORACLE utili


User quotas on all tablespaces


col quota format a10
select username
,      tablespace_name
,      decode(max_bytes, -1, 'unlimited'
       , ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from   dba_ts_quotas
where  tablespace_name not in ('TEMP')




List tablespaces, their files, allocated space, free space, and next free extent


clear breaks
set linesize 132
set pagesize 60
break on tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'


SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;


Aggiugere un datafile a una tablespace





alter tablespace PROTO0_D add datafile 'G:\oradata\orcl\PROTO0_D8.DBF' SIZE 30720M AUTOEXTEND OFF;