常用语句

创建表空间及用户

/*分为四步 */
/*第1步：创建临时表空间  */
create temporary tablespace test_temp  
tempfile 'E:\oracle\orcl\test_temp.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  
 
/*第2步：创建数据表空间  */
create tablespace test_data  
logging  
datafile 'E:\oracle\orcl\test_data.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  
 
/*第3步：创建用户并指定表空间  */
create user test identified by test  
default tablespace test_data 
temporary tablespace test_temp;  
 
/*第4步：给用户授予权限  */
grant connect,resource,dba to test;

--删除用户
--drop user test cascade;

修改Oracle连接数

--1. 查看processes和sessions参数
show parameter processes;
show parameter sessions;
--2. 修改processes和sessions值
alter system set processes=500 scope=spfile;
alter system set sessions=555 scope=spfile;
--3. 修改processes和sessions值必须重启oracle服务器才能生效
--ORACLE的连接数(sessions)与其参数文件中的进程数(process)有关，它们的关系如下：
--sessions=(1.1*process+5)
--重启数据库：
shutdown immediate;
startup;

Oracle数据泵导出数据

--1.创建一个用户
create user test identified by test;
--2.赋给dba权限
grant dba to test;
--3.创建目录(这里并不会自动创建目录，目录需手动创建)
create directory dp_dir AS '/home/oracle/dp_dir';  
--4.查看目录
select  DIRECTORY_NAME,DIRECTORY_PATH from dba_directories where directory_name='DP_DIR';
--5.创建dblink 用户名和密码均为远程数据的
 create public database link DB_LINK connect to 用户 identified by "密码" using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XX.XX)(PORT = 1521)))  (CONNECT_DATA =  (SERVER = DEDICATED)   (SERVICE_NAME = SID )))';
--6.赋给读写权限
grant read,write on directory dp_dir to kpb;
--7.导出
expdp test/test SCHEMAS=kpbtest directory=dump_dir dumpfile=kpbtest1.dmp  logfile=kpbtest1.log network_link=DB_LINK
expdp kpbtest/kpbtest@orcl schemas=kpbtest directory=dump_dir dumpfile=kpbtest-3-31.dmp logfile=kpbtest-3-31.log CONTENT=DATA_ONLY(已有表结构时使用)
--8.导入 
impdp test/test@orcl DIRECTORY=dump_dir DUMPFILE=test_201907301000.DMP logfile=test_201907301030.log SCHEMAS=kpb 

---Oracle 数据泵导出
expdp test/test schemas=kpb directory=DP_DIR dumpfile=test_201907301000.dmp logfile=test_201907301000.log

Oracle修改字段类型

/** 字段数据为空 */
alter table tb modify (name nvarchar2(20));

/*修改原字段名name为name_tmp*/
alter table QXKP_TSCX_GOAL_HISTORY rename column GOAL_STATUS_OF_WORK to GOAL_STATUS_OF_WORK_tmp;

/*增加一个和原字段名同名的字段name*/
alter table QXKP_TSCX_GOAL_HISTORY add GOAL_STATUS_OF_WORK CLOB;

/*将原字段name_tmp数据更新到增加的字段name*/
update QXKP_TSCX_GOAL_HISTORY set GOAL_STATUS_OF_WORK=trim(GOAL_STATUS_OF_WORK_tmp);


/*更新完，删除原字段name_tmp*/
alter table QXKP_TSCX_GOAL_HISTORY drop column GOAL_STATUS_OF_WORK_tmp;

创建索引、触发器

--创建索引
create index SYS_C0025706 ON KPBSUGGESTIONFLOW (SGSTFACEDICTID, SGSTPROPERTYDICTID, SGSTTYPEDICTID, SGSTSEVENHARDDICTID, SGSTRESPONSUNITID)
  tablespace KPB_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 7M
    next 1M
    minextents 1
    maxextents unlimited
  );

--创建触发器
CREATE OR REPLACE TRIGGER kpb."TRI_KpbLastestMapMerger"
   before insert  on KpbLastestMapMerger
    for each row

begin
     select NEXT.nextval into :new.ID from dual;
end;

Oracle性能检测

select round(100 * a.pct, 2) 耗时占比, 
       round(a.elapsed_time/1000000, 2) 消耗时间, 
       round(a.elapsed_time/a.executions/1000) 耗时每次, 
       round(a.cpu_time/1000000, 2) CPU占用时间, 
       a.buffer_gets 缓存读取次数, 
       a.disk_reads 磁盘读取次数, 
       round(a.buffer_gets/a.executions) 缓存读每次, 
       a.executions 执行次数, 
       a.rows_processed 解析记录条数, 
       s.sql_text, 
       s.SQL_FULLTEXT 
from (select * 
      from (select elapsed_time, 
                   ratio_to_report(elapsed_time) over () pct, 
                   cpu_time, 
                   buffer_gets,
                   disk_reads,
                   executions, 
                   rows_processed, 
                   address, 
                   hash_value 
            from  v$sql 
            order by elapsed_time desc) 
      where rownum < 26) a, 
     v$sqlarea s 
where a.address = s.address 
  and a.hash_value = s.hash_value 
  and a.executions <> 0 
order by 耗时占比 desc, CPU占用时间 desc;
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=risen_2021 entries=5 force=y

Oracle 会话管理

select '会话上限' AS 参数名称, value AS 参数值, 4 AS 排序 FROM v$parameter where name = 'sessions' UNION
select '实际会话' AS 参数名称, count(*)||'' AS 参数值, 5 AS 排序 from v$session UNION
select '活动会话' AS 参数名称, count(*)||'' AS 参数值, 6 AS 排序  from v$session where status='ACTIVE' UNION
select '进程上限' AS 参数名称, value AS 参数值, 7 AS 排序 from v$parameter where name = 'processes' UNION
select '实际进程' AS 参数名称, count(*)||'' AS 参数值, 8 AS 排序  from v$process UNION
select '我的会话' AS 参数名称,count(username)||''  AS 参数值, 9 AS 排序 from v$session where username='kpb';

Linux配置Oracle临时环境

export ORACLE_BASE=/usr/app/oracle; 
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; 
export ORACLE_SID=orcl; 
export PATH=$ORACLE_HOME/bin:$PATH;

Linux下Oracle启动关闭

#使用su - oracle命令进入oracle用户
1、查看
lsnrctl status
lsnrctl stop
lsnrctl start
#使用sqlplus /nolog 进入sqlplus界面
conn / as sysdba; #登录DBA账号
shutdown immediate #关闭数据库
startup #启动数据

Oracle删除归档日志

rman target /
RMAN> crosscheck archivelog all; //检查归档日志
RMAN> delete archivelog all completed before 'sysdate - 3'; //删除3天以前的归档日志

Oracle死锁

查看锁表进程SQL语句1： 
select sess.sid, 
   sess.serial#, 
   lo.oracle_username, 
   lo.os_user_name, 
   ao.object_name, 
   lo.locked_mode 
   from v$locked_object lo, 
   dba_objects ao, 
   v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid; 

查看锁表进程SQL语句2： 
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID; 

杀掉锁表进程： 
如有記錄則表示有lock，記錄下SID和serial# ，將記錄的ID替換下面的738,1429，即可解除LOCK 
alter system kill session '738,1429'; 

数据库日志分析

BEGIN
dbms_logmnr.add_logfile(logfilename=>'/home/soft/database/oracle/oradata/orcl/redo01.log',options=>dbms_logmnr.NEW);
END;
/