ORACLE 综合知识

转：http://blog.chinaunix.net/u/4325/showart_185446.html
-------创建表空间
CREATE TABLESPACE MT_MSG_TAB DATAFILE '/data/oracle/product/oradata/orcl/MT_TAB/mt_msg_tab01.dbf' SIZE 2000M
DEFAULT STORAGE ( INITIAL 4M NEXT 4M MINEXTENTS 2 MAXEXTENTS UNLIMITED PCTINCREASE 0);
alter tablespace DCS_INDEX_SPACE add datafile '/data/oracle/product/oradata/orcl/MT_TAB/dcs_index02.dbf' size 2000M;
-------删除表空间
startup mount
alter database datafile filename offline drop;
alter database open;
drop tablespace tablespace_name including contents;
-------查看当前用户缺省表空间
select username,default_tablespace from user_users;
-------查看当前用户的角色
select * from user_role_privs;
-------查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;
-------查询表空间使用情况（可以查出表空间总量的情况，各项分别是表空间名，总大小，已使用了多少，剩余百分比）
set line 150
column 表空间名 format A25 WORD_WRAPPED
column total_size heading 总大小(MB) format 999,999
column used_size heading 已使用大小(MB) format 999,999
column REMAIN_PERCENTAGE format A10 heading 剩余百分比
SELECT c.tablespace_name 表空间名, ROUND(a.bytes/1048576,2) total_size,
ROUND((a.bytes-b.bytes)/1048576,2) used_size,
ROUND(b.bytes/a.bytes * 100,2)||'%' REMAIN_PERCENTAGE
FROM (SELECT tablespace_name,SUM(a.bytes) bytes
FROM sys.DBA_DATA_FILES a GROUP BY tablespace_name) a,
(SELECT a.tablespace_name,
         NVL(SUM(b.bytes),0) bytes
  FROM sys.DBA_DATA_FILES a,
       sys.DBA_FREE_SPACE b
  WHERE a.tablespace_name = b.tablespace_name (+)
    AND a.file_id         = b.file_id (+)
  GROUP BY a.tablespace_name) b,
  sys.DBA_TABLESPACES c
WHERE a.tablespace_name = b.tablespace_name(+)
  AND a.tablespace_name = c.tablespace_name
ORDER BY 1;
--------查询数据文件使用情况（具体到表空间使用的数据文件的情况，各项分别是表空间名，数据文件名，数据文件大小，使用了多少，剩余比例）
set line 150
select b.tablespace_name||'  '||b.file_name||'  '|| b.bytes||' 
'||(b.bytes-sum(nvl(a.bytes,0)))||' 
'||round(sum(nvl(a.bytes,0))/(b.bytes)*100)||'%'
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
order by b.tablespace_name
/

>>>>>>>>>>>>>>>>>>>>表
--------创建表
create table <table_name> (
... ... ...
);
--------删除表
drop table <table_name>;
--------改表名
alter table src_table_name rename to dst_table_name;
--------查看名称包含log字符的表
select object_name,object_id from user_objects
         where instr(object_name,'LOG')>0;
--------查看某表的创建时间
select object_name,created from user_objects where object_name=upper(<&table_name>);   
--------查看某表的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments
         where segment_name=upper(<&table_name>);
--------删除表分区
alter table <table_name> drop partion <partition_name>;
--------增加表分区
ALTER TABLE <table_name> ADD PARTITION <partition_name> VALUES LESS THAN
(分区条件) TABLESPACE <tablespace_name>;
--------分区的倒入
oracle$ imp univas/univas2000 FILE MT2002_09_12=.dmp TABLES = (T_ISMG_MT_MSG:MT2002_09_12) IGNORE=y
--------分区的倒出
oracle$ exp unvias/univas2000 TABLES = T_ISMG_MT_MSG:MT2002_09_12 rows=Y file=MT2002_09_12.dmp
--------查看分区信息
SELECT * FROM user_extents WHERE SEGMENT_NAME=T_ISMG_MT_MSG;



>>>>>>>>>>>>>>>>>>>>索引
---------------------------查看索引
SELECT  index_name, index_type, table_name FROM
       ind(dba_indexes,user_indexes,all_indexes) order by table_name;
---------------------------查看索引被索引的字段
select * from user_ind_columns where index_name=upper(<&index_name>);
    
---------------------------查看索引的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments
         where segment_name=upper(<&index_name>);
   
---------------------------分析索引
ANALYZE INDEX <index_name> VALIDATE STRUCTURE;
col name         heading '<Index Name>'          format a30
col del_lf_rows  heading 'Deleted|Leaf Rows'   format 99999999
col lf_rows_used heading 'Used|Leaf Rows'      format 99999999
col ratio    heading '% Deleted|Leaf Rows' format 999.99999
SELECT name,
         del_lf_rows,
         lf_rows - del_lf_rows lf_rows_used,
         to_char(del_lf_rows / (lf_rows)*100,'999.99999') ratio
        FROM index_stats where name = upper('&index_name');
---------------------------查看索引浪费空间数目
SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 "Wasted Space"
FROM index_stats WHERE name = <index_name>;
---------------------------查询层数
SELECT index_name,blevel FROM ind(dba_indexes,user_indexes,all_indexes);
---------------------------如果层数大于四层，需要重新建立索引
ALTER INDEX <index_name> REBUILD;
>>>>>>>>>>>>>>>>>>>>日志
----------------查看redo log的路径和状态
select * from v$logfile
select * from v$log;
   
定期整理ORACLE网络请求日志
auto_listener.sh
#-------------------------------------------------------------------------------------
rq=` date +"%d" `
cp $ORACLE_HOME/network/log/listener.log $ORACLE_BACKUP/network/log/listener_$rq.log
su - oracle -c "lsnrctl set log_status off"
cp /dev/null $ORACLE_HOME/network/log/listener.log
su - oracle -c "lsnrctl set log_status on"
#-------------------------------------------------------------------------------------


>>>>>>>>>>>>>>>>>>>>会话管理

-----------------用系统管理员，查看当前数据库有几个用户连接：
select username,sid,serial# from v$session;
-----------------查看连接序号
select a.username, a.sid, a.serial#,
       a.lockwait, a.machine,a.status,
       a.last_call_et,a.sql_hash_value,a.program
  from v$session a
where nvl(a.username,'NULL')< >'NULL';

-----------------查看该连接的SQL语句
select hash_value , sql_text
  from v$sqltext s
where s.hash_value=<sql_hash_value>;  
-----------------如果要停某个连接用
alter system kill session 'sid,serial#';
-----------------如果这命令不行,找它UNIX的进程数
select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;
说明：21是某个连接的sid数
然后用 kill 命令杀此进程号。
-----------------生成杀死连接的脚本
set head off;
set feedback off;
set newpage none;
spool kill_all_session.sql
select 'alter system kill session '''||sid||','||serial#||''';' from v$session where username is not null;
spool off;

>>>>>>>>>>>>>>>>>>>>>>>>>存储过程
-----------------查看源码
select text from all_source where owner=user and name=upper(<plsql_name>);

---------碎片整理
alter tablespace temp coalesce;

>>>>>>>重建分区索引方法：
     Alter index indexname rebuild partition paritionname tablespace tablespacename;
     Alter index indexname rebuild subpartition partitioname tablespace  tablespacename;
     Partition name 可以从user_ind_partitions查找
     Tablepace 参数允许alter index操作更改索引的存储空间；
六：其他
   1：truncate 分区操作和truncate 普通表的区别
      Truncate 分区操作会导致全局索引失效; truncate 普通表对索引没有影响；
      Truncate 分区操作不会释放全局索引中的空间，而truncate 普通表会释放