我的oracle维护语句

dominic_80

★建立表空间

drop tablespace PTZXUSER including contents and datafiles;
create tablespace ptzxuser datafile '/data/u01/oradata/orcl/ptzxuser.dbf' size 200M autoextend on next 5M;
create user ptzxuser identified by "ptzxuser" default tablespace ptzxuser;
grant resource,create session,create view,create user,alter any trigger to ptzxuser;
commit;

 

create tablespace blogtest datafile '/data/u01/oradata/orcl/blogtest.dbf' size 200M autoextend on next 5M;
create user tiaoshi identified by "tiaoshi" default tablespace blogtest;
grant resource,create session,create view,create user,alter any trigger to tiaoshi;
commit;

SQL> create tablespace blogtest datafile '/u02/oradata/orcl/blogtest01.dbf' size 4g,
  2  '/u02/oradata/orcl/blogtest02.dbf' size 4g;

 

★增加表空间大小
alter tablespace blogtest add datafile '/u02/oradata/orcl/blogtest03.dbf' size 4g;
alter tablespace blogtest add datafile '/u02/oradata/orcl/blogtest04.dbf' size 4g;

create user tiaoshi identified by tiaoshi
default tablespace blogtest
temporary tablespace temp;

grant unlimited space on blogtest to tiaoshi;

 

★关于dba_directories
select * from dba_directories;

 

★oracle 挂载方式
mount -F nfs -o hard,nointr,rsize=32768,wsize=32768,actimeo=0,timeo=600  10.10.105.254:/vol/blogdb_3   /u02/oradata/orcl

★查看表空间占用大小
Select Tablespace_Name,Sum(bytes)/1024/1024 "Size(M)" From Dba_Segments Group By Tablespace_Name;

ztblog用户: Select Tablespace_Name,Sum(bytes)/1024/1024 "Size(M)" From user_segments group by tablespace_name;

 

★查看某张表占用大小
ztblog用户：select sum(bytes)/1024/1024 "size(M)" from user_segments where segment_name="表名";

 

★查看某个表空间里所有的表
select table_name,status,tablespace_name from dba_tables where tablespace_name='ZTBLOG';

 

★查看某表的结构
desc ZTBLOG.USERS;

 

★查看大于小于之间的数据行，使用minus实现
select user_account from ZTBLOG.USERS where rownum < 4 minus select user_account from ZTBLOG.USERS where rownum < 2;

 

★查看SHARED POOL
select s.pool Name,
       Round(to_number(p.value)/1024,2)||'K' "Size",
       Round(s.bytes/1024,2)||'K' "Free",
       Round((s.bytes/p.value),4)*100 "percentfree(%) "
from   v$sgastat s,
       v$parameter p
where s.name='free memory' and
      p.name='shared_pool_size' and
      s.pool='shared pool'
union
select s.pool Name,
       Round(to_number(p.value)/1024,2)||'K' "Size",
       Round(s.bytes/1024,2)||'K' "Free",
       Round((s.bytes/p.value),4)*100 "percentfree(%) "
from   v$sgastat s,
       v$parameter p
where s.name='free memory' and
      p.name='large_pool_size' and
      s.pool='large pool'

 

★查看ORACLE并发量
select count(*) from v$session where TYPE='USER';

 

★查看某一用户具有的权限
select * from dba_sys_privs where grantee='TIAOSHI';

 

★查看连接占用的PGA内存大小
select p.PGA_USED_MEM from v$process p,v$session s where s.paddr=p.addr;

select sum(p.PGA_USED_MEM)/1024/1024 from v$process p,v$session s where s.paddr=p.addr;  （按M来算）

 

15:11:18 SQL> set serverout on;
15:11:27 SQL> exec proc_blogStat('2008-04-25'); 

 

★删除物化视图
conn ztblog/ztblog;
select * from user_mviews;
drop materialized view mv_serverlist;

 

★查看SGA动态状态
select * from v$sga_dynamic_components;

 

★查看SGA各组件动态内存
select * from $sgainfo;

 

★查看系统给的SGA目标建议
select * from v$sga_target_advice;

@$ORACLE_HOME/rdbms/admin/spreport

★查看JOB

col Log_User for a8
col last_date for a19
col next_date for a19
col broken for a5
col Interval for a10
col what for a45

set linesize 150

select job,log_user,
        last_date,
        next_date,
        broken,
        failures,
        what
from dba_jobs;

execute dbms_job.remove(3);
select * from dba_jobs where job=3;

=================

select * from ( select a.article_createtime from article a order by article_id desc )where rownum <=1;
select * from (select p.photo_upload_time from photo p order by photo_id desc)where rownum<=1;

 

★查看死锁SQL语句
select sql_text from v$sql where hash_value in (select sql_hash_value from v$session
where sid in (select session_id from v$locked_object));

 

★查看某SESSION某一时间正在处理的SQL语句
方法一：
select v2.sid,v2.sql_id from v$process v1,v$session v2 where v1.ADDR=v2.PADDR and v1.SPID=16860;
select sql_text from v$sql where sql_id='gzrd51dkwa70f';
方法二：(比较慢)
select b.sid,c.spid,c.pid,a.sql_text from v$sqltext a,v$session b,v$process c where a.hash_value = b.sql_hash_value
and c.addr = b.paddr and c.spid=10211 order by piece asc;

★动态显示SGA大小调整操作信息
select COMPONENT,CURRENT_SIZE,MIN_SIZE, LAST_OPER_TIME,GRANULE_SIZE from v$sga_dynamic_components;

 

★查看所有动态性能视图和动态性能表
select name from v$fixed_table where name like 'V$%';