ts.sql

--ts.sql
set linesize 200 pagesize 50000;
col tablespace_name format a50;
select   a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024   "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name)   a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name)   b  where   a.tablespace_name=b.tablespace_name
order   by   ((a.bytes-b.bytes)/a.bytes)   desc ;

--asm.sql
column name format a20
column free_mb format 999,999,999
select name,to_char(sysdate,'YYYY-MM-DD HH24:MI') SYSTEM_DATETIME from v$database;
select (select name from v$database) db_name,name diskgroup_name,total_mb,free_mb,round(free_mb/total_mb*100,1) FREE_Uti_PCT from v$asm_diskgroup order by 4;

set lines 200 pages 50000
col file_name for a80
col TABLESPACE_NAME for a30
select file_id,file_name,tablespace_name,autoextensible,BYTES/1024/1024,MAXBYTES/1024/1024,status from dba_data_files order by 1;
select file_id,file_name,tablespace_name,autoextensible,BYTES/1024/1024,MAXBYTES/1024/1024,status from dba_temp_files order by 1;

select count(*) count from (select tablespace_name , sum(bytes)/1024/1024 free from dba_free_space where tablespace_name not like '%UNDO%' group by tablespace_name) a,(select tablespace_name , sum(bytes)/1024/1024 total ,AUTOEXTENSIBLE from dba_data_files where tablespace_name not like '%UNDO%' group by tablespace_name,AUTOEXTENSIBLE) b where a.tablespace_name(+)=b.tablespace_name and round((b.total-a.free)/b.total*100,2) > 75 and a.free < 204800;

select a.tablespace_name from (select tablespace_name , sum(bytes)/1024/1024 free from dba_free_space where tablespace_name not like '%UNDO%' group by tablespace_name) a,(select tablespace_name , sum(bytes)/1024/1024 total ,AUTOEXTENSIBLE from dba_data_files where tablespace_name not like '%UNDO%' group by tablespace_name,AUTOEXTENSIBLE) b where a.tablespace_name(+)=b.tablespace_name and round((b.total-a.free)/b.total*100,2) > 75 and a.free < 204800;

--location
set lines 200 pages 50000
col file_name for a60
col TABLESPACE_NAME for a30
select file_id,file_name,tablespace_name,autoextensible,BYTES/1024/1024,MAXBYTES/1024/1024,status from dba_data_files where tablespace_name='&tsname' order by file_id;

--resize
set lines 200 pages 50000
col file_name for a60
col TABLESPACE_NAME for a30
select 'alter database datafile '||file_id||' resize 8G;' from dba_data_files where tablespace_name='&tsname' order by file_id;

--resize
set lines 200 pages 50000
col file_name for a60
col TABLESPACE_NAME for a30
select 'alter database datafile '||file_id||' resize 16G;' from dba_data_files where tablespace_name='&tsname' order by file_id;

--resize
set lines 200 pages 50000
col file_name for a60
col TABLESPACE_NAME for a30
select 'alter database datafile '||file_id||' resize 20G;' from dba_data_files where tablespace_name='&tsname' order by file_id;

set pages 999
select 'alter database datafile '||file_id||' autoextend on maxsize unlimited;' from dba_data_files
union
select 'alter database datafile '||file_id||' autoextend on maxsize unlimited;' from dba_data_files where tablespace_name like '%UNDO%'
union
select 'alter database tempfile '||file_id||' autoextend on maxsize unlimited;' from dba_temp_files;

set linesize 120 pagesize 50000
select 'alter database datafile '||file_id||' autoextend off;' from dba_data_files
union
select 'alter database datafile '||file_id||' autoextend off;' from dba_data_files where tablespace_name like '%UNDO%'
union
select 'alter database tempfile '||file_id||' autoextend off;' from dba_temp_files;

select sum(bytes)/1024/1024/1024 GB from dba_temp_files
union
select sum(bytes)/1024/1024/1024 GB from dba_data_files
union
select sum(bytes)/1024/1024/1024 GB from dba_segments;

--/*查看每天产生的归档日志大小*/
select aa.ArchiveDate,aa.ArchiveSize from (
select trunc(first_time) as ArchiveDate,
sum(block_size * blocks) / 1024 / 1024 / 1024 as ArchiveSize
from v$archived_log
group by trunc(first_time)
) aa order by aa.ArchiveDate desc;