oracle 命令

一.linux下的操作命令

1.windows管理员登录sys：
  安装数据库软件-->安装数据库服务->安装监听
  启动监听：lsnrctl start
  关闭监听：lsnrctl stop
  查看状态：lsnrctl status
  启动oracle服务实例：net start oracleServiceBILL
  关闭oracle服务实例: net stop oracleServiceBILL
  登录oracle：sqlplus  用户名/密码@数据库
  表空间：
        创建：create tablespace TEST 
        logging 
        datafile 'F:\app\Bill\oradata\bill\test.dbf' 
        size 10m 
        autoextend on 
        next 10m maxsize 20480m 
        extent management local;     
        
        删除：drop tablespace *;
    用户：    
        创建：create user 用户 identified by 密码 
        default tablespace TEST
        temporary tablespace temp; 
        
        删除用户 drop user 用户名;
    授权角色：
        系统预定义角色：CONNECT、RESOURCE、DBA、EXP_FULL_DATABASE、IMP_FULL_DATABASE。其中，CONNECT、RESOURCE、DBA主要用于数据库管理，数据库管理员需要被授予这三个角色。一般的数据库开发人员，需要被授予CONNECT、RESOURCE角色即可。EXP_FULL_DATABASE、IMP_FULL_DATABASE角色分别用于操作数据库导出、导入相关的操作。为用户授予角色；
        grant connect,resource,dba to 用户;
2.操作oracle
 0）监听：启动的话先启动监听 lsnrctl start 查看监听状态lsnrctl status
      关闭的话后关闭监听 lsnrctl stop
 1）登录:su -oracle
 2）连接：sqlplus /nolog
 3）使用管理员权限：connect /as sysdba
 4）操作：
    启动--startup 
    关闭--shutdown
        normal需要在所有连接用户断开后才执行关闭数据库任务，在执行这个命令后不允许新的连接
        immediate在用户执行完正在执行的语句后就断开用户连接，并不允许新用户连接。
        transactional 在拥护执行完当前事物后断开连接，并不允许新的用户连接数据库。
        abort 执行强行断开连接并直接关闭数据库。

3.备份oracle
 1）登录oracle：su - oracle 
   查看oracle_sid:echo $ORACLE_SID
 2）需要备份的实例：export ORACLE_SID=***
  *如果是oracle g11：select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null; 执行之后把查询结果中的alter语句执行下
 3）导出的命令：exp 用户名/密码 file=路径/**.dmp owner=''
 4）导入：:imp 用户名/密码 full=y file=路径/**.dmp ignore=y

例如：

cd /home/oracle/app/oracle/product/12.1.0/dbhome_1/binJAR
su - oracle;
./exp system/Wlb19880312@172.23.2.19/pdborcl.localdomain file=/home/oracleBackUp/20160923jzhc.dmp COMPRESS=n INDEXES=n STATISTICS=none log=/home/oracleBackUp/log/explog.log owner=jzhc

su - oracle;
cd /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin
./exp jzhc/jzhc#21M1@172.23.2.17/pdborcl.localdomain file=/home/oracleBackUp/20160923jzhc.dmp COMPRESS=n INDEXES=n STATISTICS=none log=/home/oracleBackUp/log/explog.log owner=jzhc

./imp jzhc/jzhc2#21M@172.23.2.19/pdborcl.localdomain file=/home/oracleBackUp/20160923jzhc.dmp log=d:\implog.log

4.解锁账户
 1)su - oracle
 2)sqlplus "/as sysdba"
 3)解锁账户：alter user tcny account unlock;

二.工具sql

1、查看当前的数据库连接数
select count(*) from v$process ;
2、数据库允许的最大连接数
select value from v$parameter where name ='processes';
3、修改数据库最大连接数
alter system set processes = 300 scope = spfile;
4.恢复误删的表
flashback table 表名 to before drop;
5、查看当前有哪些用户正在使用数据
select osuser, a.username, cpu_time/executions/1000000||'s', b.sql_text, machine
from v$session a, v$sqlarea b
where a.sql_address =b.address
order by cpu_time/executions desc;
6、当前的session连接数
select count(*) from v$session
7、当前并发连接数
select count(*) from v$session where status='ACTIVE';
8.查询当前数据库名：
select name from v$database;
9.分组汇总函数：wm_concat(distinct(id))

三、性能检测sql

1.表大小   
   SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MBYTESE
    FROM USER_SEGMENTS
    WHERE SEGMENT_TYPE = 'TABLE'
    GROUP BY SEGMENT_NAME
    order by MBYTESE desc; 
   
2. 最消耗资源的sql
    Select se.username,
     se.sid,
     su.extents,
     su.blocks * to_number(rtrim(p.value)) as Space,
     tablespace,
     segtype,
     sql_text
  from v$sort_usage su, v$parameter p, v$session se, v$sql s
  where p.name = 'db_block_size'
   and su.session_addr = se.saddr
   and s.hash_value = su.sqlhash
   and s.address = su.sqladdr
  order by se.username, se.sid;
 
3.  Temp表空间上进程的查询
select a.tablespace, b.sid, b.serial#, a.blocks,c.sql_text
 from v$sort_usage a,v$session b,v$sqltext c
where a.session_addr = b.saddr
 and b.sql_address = c.address
order by a.tablespace,b.sid,b.serial#,c.address, c.piece;

4.查看Temp 表空间实际使用磁盘大小
Select f.tablespace_name,
     d.file_name "Tempfile name",
   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",
   round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) "Used MB",
   round((round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) /
      round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)) * 100,
      2) as "Used_Rate(%)"
 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;
 
5.查看当前临时表空间使用大小与正在占用临时表空间的sql语句
 select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
  from v$sort_usage sort, v$session sess, v$sql sql
  where sort.SESSION_ADDR = sess.SADDR
   and sql.ADDRESS = sess.SQL_ADDRESS
  order by blocks desc;
 
6.性能最差的SQL
SELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text
        FROM v$sqlarea
        ORDER BY disk_reads DESC)
WHERE ROWNUM<100; 

7.读磁盘数超100次的sql
select * from sys.v_$sqlarea where disk_reads>100;

8.最频繁执行的sql
select * from sys.v_$sqlarea where executions>100;

9.查询使用CPU多的用户session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and
   c.sid=a.sid and
   a.paddr=b.addr
order by value desc;

10.杀死进程
alter system kill session '331,51488';    
    
11、查看谁在用临时表空间
SELECT SE.USERNAME,
SE.SID,
　　SE.SERIAL#,
　　SE.SQL_ADDRESS,
　　SE.MACHINE,
　　SE.PROGRAM,
　　SU.TABLESPACE,
　SU.SEGTYPE,
　　SU.CONTENTS 　FROM V$SESSION SE,
　　V$SORT_USAGE SU 　　WHERE SE.SADDR = SU.SESSION_ADDR;

12、查看临时表空间TEMP空闲情况
SELECT TABLESPACE_NAME,
FILE_ID,
BYTES_USED / 1024 / 1024,
BYTES_FREE / 1024 / 1024
FROM V$TEMP_SPACE_HEADER;

13, 具体到某个SID临时表空间使用情况
SELECT B.TABLESPACE,
B.SEGFILE#,
B.SEGBLK#,
B.BLOCKS,
B.BLOCKS * 32 / 1024 / 1024,
A.SID,
A.SERIAL#,
A.USERNAME,
A.OSUSER,
A.STATUS,
C.SQL_TEXT,
B.CONTENTS
FROM V$SESSION A, V$SORT_USAGE B, V$SQL C
WHERE A.SADDR = B.SESSION_ADDR
AND A.SQL_ADDRESS = C.ADDRESS(+)
ORDER BY B.BLOCKS DESC

在创建用户时,
有一个默认的表空间的参数. 可以通过查看视图DATABASE_PROPERTIES可以看到相应的信息.
SELECT A.PROPERTY_NAME, A.PROPERTY_VALUE
FROM DATABASE_PROPERTIES A
WHERE A.PROPERTY_NAME LIKE '%DEFAULT%';

14, /*查看临时表空间总体使用情况*/
SELECT TMP_TBS.TABLESPACE_NAME,
SUM(TMP_TBS.TOTAL_MB) TOTAL_MB,
SUM(USED_TOT.USED_MB) USED_MB,
SUM(USED_TOT.USED_MB) / SUM(TMP_TBS.TOTAL_MB) * 100 USED_PERSENT
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) TMP_TBS,
(SELECT TMP_USED.TABLESPACE,
SUM(TMP_USED.BLOCKS * PARA.DB_BLOCK_SIZE) / 1024 / 1024 USED_MB
FROM V$SORT_USAGE TMP_USED,
(SELECT VALUE DB_BLOCK_SIZE
FROM V$PARAMETER
WHERE NAME = 'DB_BLOCK_SIZE') PARA
GROUP BY TMP_USED.TABLESPACE) USED_TOT
WHERE TMP_TBS.TABLESPACE_NAME = USED_TOT.TABLESPACE(+)
GROUP BY TMP_TBS.TABLESPACE_NAME;

15, /*查看临时表空间中排序段和数据段的使用情况*/
SELECT TMP_TBS.TABLESPACE_NAME,
USED_TOT.SEGTYPE TEMP_SEG_TYPE,
SUM(TMP_TBS.TOTAL_MB) TOTAL_MB,
SUM(USED_TOT.USED_MB) USED_MB,
SUM(USED_TOT.USED_MB) / SUM(TMP_TBS.TOTAL_MB) * 100 USED_PERSENT
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) TMP_TBS,
(SELECT TMP_USED.TABLESPACE,
TMP_USED.SEGTYPE,
SUM(TMP_USED.BLOCKS * PARA.DB_BLOCK_SIZE) / 1024 / 1024 USED_MB
FROM V$SORT_USAGE TMP_USED,
(SELECT VALUE DB_BLOCK_SIZE
FROM V$PARAMETER
WHERE NAME = 'DB_BLOCK_SIZE') PARA
GROUP BY TMP_USED.TABLESPACE, TMP_USED.SEGTYPE) USED_TOT
WHERE TMP_TBS.TABLESPACE_NAME = USED_TOT.TABLESPACE(+)
GROUP BY TMP_TBS.TABLESPACE_NAME, USED_TOT.SEGTYPE;

16.数据库表空间占用
sELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;