oracle常用命令

-- 查看表空间
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
       D.TOT_GROOTTE_MB "表空间大小(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
                     2),
               '990.99') "使用比%",
       F.TOTAL_BYTES "空闲空间(M)",
       F.MAX_BYTES "最大块(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY F.TABLESPACE_NAME;

-- 查看表空间是否为自动扩展

select tablespace_name, AUTOEXTENSIBLE, INCREMENT_BY from dba_data_files;

--如果你的系统表空间不是自动扩展的那么，有如下方法可以进行扩展。
--１、增加system表空间的数据文件。 　　
alter tablespace system add datafile 'D:\oracleXE\oradata\XE\system_01.dbf' resize 1024M;

--２、更改system的数据文件，让其自动扩展，并扩大其大小。 　　
alter database datafile 'D:\oracleXE\oradata\XE\system_01.dbf' autoextend on;
　　alter database datafile 'D:\oracleXE\oradata\XE\system_01.dbf' resize 2048M;

-- 增加表空间容量
alter TABLESPACE WEBSMS_DATA add DATAFILE '/opt/oracle/oradata/websms/l2007test/raw15' SIZE 500M REUSE AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED, '/opt/oracle/oradata/websms/l2007test/raw16' SIZE 500M REUSE AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED, '/opt/oracle/oradata/websms/l2007test/raw17' SIZE 500M REUSE AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED, '/opt/oracle/oradata/websms/l2007test/raw18' SIZE 500M REUSE AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED

-- 禁用外键
alter table corpmember disable constraint FK_CORPMEMBER_MEMBERID
-- 启用外键
alter table corpmember enable constraint FK_CORPMEMBER_MEMBERID

-- 查看索引状态
  select index_name, index_type, tablespace_name, table_type, status
    from user_indexes
   where index_name = 'PRIMARY_MESSAGEINFO_BAK';

-- 恢复索引
alter index PRIMARY_MESSAGEINFO_BAK rebuild;

-- 看分区信息
select u.table_name, u.partition_name
  from USER_TAB_PARTITIONS u
where u.table_name = 'MESSAGESTATUS_BAK'

-- 删除分区
alter table MESSAGESTATUS_BAK drop partition
MESSAGESTATUS_BAK20100730

-- 增加分区
alter table messageinfo_bak add partition
messageinfo_bak201007311232 values less
than(to_date('201007311232', 'yyyymmddHH24mi')) tablespace
d671_message_bak

-- 查看锁定的对象

  select a.sid,
         a.serial#,
         a.username,
         a.program,
         c.owner,
         c.object_name
          from v$session a, v$locked_object b, all_objects c
         where a.sid = b.session_id
           and c.object_id = b.object_id;


-- 杀锁定的对象
alter system kill session 'sid,#serial';