一、统计表所占空间大小（表容量）
/*一般情况下，表所占空间分为三部分：表数据、表索引、表blob字段数据*/
--1 统计含(blob字段)的单表所占用的空间
SELECT TABLE_NAME, SUM(SIZE_MB)
FROM (SELECT A.SEGMENT_NAME AS TABLE_NAME,
SUM(BYTES) / 1024 / 1024 SIZE_MB
FROM USER_SEGMENTS A
GROUP BY A.SEGMENT_NAME --文本信息容量
UNION ALL
SELECT A.TABLE_NAME, SUM(B.BYTES) / 1024 / 1024 SIZE_MB
FROM USER_LOBS A, USER_SEGMENTS B
WHERE A.SEGMENT_NAME = B.SEGMENT_NAME
GROUP BY A.TABLE_NAME) --lob字段容量
WHERE TABLE_NAME = '&table_name'
GROUP BY TABLE_NAME
ORDER BY 2 DESC;
--2 统计含(blob字段)的表所占用的空间
SELECT TABLE_NAME, SUM(SIZE_MB)
FROM (SELECT A.SEGMENT_NAME AS TABLE_NAME,
SUM(BYTES) / 1024 / 1024 SIZE_MB
FROM USER_SEGMENTS A
GROUP BY A.SEGMENT_NAME --文本信息容量
UNION ALL
SELECT A.TABLE_NAME, SUM(B.BYTES) / 1024 / 1024 SIZE_MB
FROM USER_LOBS A, USER_SEGMENTS B
WHERE A.SEGMENT_NAME = B.SEGMENT_NAME
GROUP BY A.TABLE_NAME) --lob字段容量
GROUP BY TABLE_NAME
ORDER BY 2 DESC;
--3 统计不含lob字段的表所占空间
SELECT A.SEGMENT_NAME AS TABLE_NAME,
SUM(BYTES) / 1024 / 1024 SIZE_MB
FROM USER_SEGMENTS A
WHERE A.segment_type = 'TABLE'
GROUP BY A.SEGMENT_NAME ;
二、统计数据文件使用率
select b.file_name 物理文件名,
b.tablespace_name 表空间,
b.AUTOEXTENSIBLE,
b.MAXBYTES,
b.bytes / 1024 / 1024 大小M,
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,
substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
--and b.tablespace_name in('TBS_CRJ_SQ','TBS_CRJ_RZ_INDEX')
group by b.tablespace_name, b.AUTOEXTENSIBLE,b.MAXBYTES, b.file_name, b.bytes
order by b.tablespace_name
三、查看表空间是否已满
select
a.tablespace_name,trunc(sum(a.bytes)/1024/1024) total,
trunc(sum(a.bytes)/1024/1024 - sum(b.bytes)/1024/1024) used,
trunc(sum(b.bytes)/1024/1024) free,
to_char(trunc((sum(a.bytes)/1024/1024 - sum(b.bytes)/1024/1024)/(sum(a.bytes)/1024/1024),4)*100)||'%' pused,
to_char(trunc((sum(b.bytes)/1024/1024)/(sum(a.bytes)/1024/1024),4)*100)||'%' pfree
from
(select sum(bytes) bytes,tablespace_name from dba_data_files group by tablespace_name) a,
(select sum(bytes) bytes,tablespace_name from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name(+)
group by a.tablespace_name
order by to_number(rtrim(pused,'%')) desc;
四、杀进程
select a.MACHINE,a.PROGRAM,a.SID,a.SERIAL#,a.STATUS,b.SQL_TEXT,
'alter system kill session'||''''||a.SID||','||a.SERIAL#||'''immediate ;'kill_sql
from v$session a , v$sql b
where a.USERNAME is not null
and a.SQL_ID=b.SQL_ID;
五、SCN查询
select version,
to_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') DATE_TIME,
((((((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60)+
((to_number(to_char(sysdate, 'MM')) - 1) * 31 * 24 * 60 * 60) +
(((to_number(to_char(sysdate, 'DD')) - 1)) * 24 * 60 * 60) +
(to_number(to_char(sysdate, 'HH24')) * 60 * 60) +
(to_number(to_char(sysdate, 'MI')) * 60) +
(to_number(to_char(sysdate, 'SS')))) * (16 * 1024)) -
dbms_flashback.get_system_change_number) /
(16 * 1024 * 60 * 60 * 24)) headroom
from v$instance;
--SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND x.ksppinm LIKE '%_external_scn_rejection_threshold_hours%';
--alter system set "_external_scn_rejection_threshold_hours"=8;
六、数据库用户
--1、用户挂起（锁定用户）
alter user crjapp account lock;
--2、用户解锁
alter user crjapp account unlock;
--3、删除用户
drop user user_name cascade ;
七、权限的授予、收回
--1、授权
grant select ,insert,udate,delete on table_name to user1;
--2、收回权限
revoke select ,insert,udate,delete on table_name from user1;
--3、允许授权的对象继续授权
grant select ,insert,udate,delete on table_name to user1 with grant option;
八、表的改动
--1、重命名表名
ALTER TABLE old_tablename RENAME TO new_tablename;
--ALTER INDEX old_tablename RENAME TO new_tablename; /*重命名索引名*/
--2、修改表的字段长度
alter table table_name modify emsjjdh VARCHAR2(30);
--3、新增表字段
alter table table_name add column_name VARCHAR2(30);
--4、删除表字段
alter table table_name drop column column_name;
--5、创建同义词并授权
select 'grant all on user1.'||u.table_name||' to user2;' from dba_tables u where owner = 'user1';
select 'create or replace synonym user2.'||u.table_name||' for user1.'||u.TABLE_NAME||';' from dba_tables u where owner = 'user1';