常用脚本

 
$ cat arch.sql
select to_char(first_time,'yyyy-mm-dd') day,count(*)/2,sum(blocks*block_size)/1024/1024/1024/2 from v$archived_log group by
to_char(
first_time,'yyyy-mm-dd');

$ cat temp.sql
Select round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",
round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024, 2)  "Free MB" ,
d.file_name "Datafile name",
round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2) "Used MB",
round((f.bytes_free + f.bytes_used) / 1024, 2) "total KB",
round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024, 2)  "Free KB",
round(nvl(p.bytes_used, 0)/ 1024, 2) "Used KB",0 "Fragmentation Index"
from   SYS.V_$TEMP_SPACE_HEADER f, DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p
where  f.tablespace_name(+) = d.tablespace_name
and    f.file_id(+) = d.file_id
and    p.file_id(+) = d.file_id
;

$ cat tbs.sql
set line 132
set wrap off
select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;

 

$ cat resize.sql
ALTER DATABASE DATAFILE '/zyhome/oracle/oradata/ossdb1/users01.dbf' RESIZE 12288M;
ALTER DATABASE DATAFILE '/zyhome/oracle/oradata/ossdb1/users02.dbf' RESIZE 12288M;
ALTER DATABASE DATAFILE '/zyhome/oracle/oradata/ossdb1/users03.dbf' RESIZE 12288M;
ALTER DATABASE DATAFILE '/zyhome/oracle/oradata/ossdb1/users04.dbf' RESIZE 12288M;

 

$ cat system.sql
ALTER DATABASE
    DATAFILE '/zyhome/oracle/oradata/ossdb1/system01.dbf' RESIZE
    2000M;

$ cat data.sql
alter database datafile '/zyhome/oracle/oradata/ossdb1/users01.dbf' resize 16384M;
alter database datafile '/zyhome/oracle/oradata/ossdb1/users02.dbf' resize 16384M;
alter database datafile '/zyhome/oracle/oradata/ossdb1/users03.dbf' resize 16384M;
alter database datafile '/zyhome/oracle/oradata/ossdb1/users04.dbf' resize 16384M;

 

$ cat undo.sql
ALTER DATABASE
    DATAFILE '/zyhome/oracle/oradata/ossdb1/undotbs01.dbf' RESIZE
     4000M;

$ cat users.sql
ALTER TABLESPACE "USERS"
    ADD
    DATAFILE '/zyhome/oracle/oradata/ossdb1/users02.dbf' SIZE
    10240M, '/zyhome/oracle/oradata/ossdb1/users03.dbf' SIZE
    10240M, '/zyhome/oracle/oradata/ossdb1/users04.dbf' SIZE
    10240M;
ALTER DATABASE DATAFILE '/zyhome/oracle/oradata/ossdb1/users01.dbf' RESIZE  10240M;

$ cat kill.sh
 ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $2}'|xargs kill -9

$ cat event.sql
set line 132
set wrap off
Select s.username,s.program,count(se.event) t,se.event from v$session s,v$session_event se
Where s.sid=se.sid And se.event not like 'SQL*Net%' And s.status = 'ACTIVE' And s.username is not null
group by se.event,s.username,s.program;