Oracle运维常用操作

创建

create tablespace PMSAlex datafile 'E:/AlexPMS/tablespace/PMSAlex.DBF' size 100m autoextend on next 20m maxsize 4096m extent management local;
create tablespace PMSAlex datafile 'E:/AlexPMS/tablespace/PMSAlex.DBF' size 500m autoextend on next 50m maxsize unlimited;
create user PMSAlex identified by PMSAlex default tablespace PMSAlex; 新建用户
grant connect,resource,dba to PMSAlex;
quit;
附：
drop tablespace PMSAlex including contents and datafiles; 删除表空间
alter tablespace PRJAlex rename to PMSAlex; 重命名表空间
alter user PMSAlex identified by u0MWVwS5; 修改连接的用户名和密码

换库

drop user PMSAlex cascade; 删除用户
create user PMSAlex identified by PMSAlex default tablespace PMSAlex temporary tablespace temp; 新建用户
grant connect,resource,dba to prjAlex; 赋权
quit; 退出

导出导入

exp/imp

exp PMSAlex/PMSAlex file=E:AlexPMS_20181021.dmp;
imp PMSAlex/PMSAlex file=E:AlexPMS_20181021.dmp full=y ignore=y;
exp PMSAlex/PMSAlex@192.168.8.169:1521/orcl file=E:AlexPMS_20181021.dmp;
imp PMSAlex/PMSAlex@192.168.8.169:1521/orcl file=E:AlexPMS_20181021.dmp full=y ignore=y;

expdp/impdp

create directory expdp_dir as 'E:\oracle\temp_expdp';
grant read,write on directory expdp_dir to PMSAlex;
expdp PMSAlex/PMSAlex@orcl schemas=PMSAlex directory=expdp_dir dumpfile=AlexPMS_20181021.dmp logfile=AlexPMS_20181021.log parallel=4;
create directory impdp_dir as 'E:\oracle\temp_impdp';
impdp PMSAlex/PMSAlex@orcl schemas=PMSAlex directory=impdp_dir dumpfile=AlexPMS_20181021.dmp logfile=AlexPMS_20181021.log;
# 导入时如果需要更换表空间、用户，语句后面加：
REMAP_SCHEMA=oldUserName:newUserName REMAP_TABLESPACE=oldSpaceName:newSpaceName

赋权相关

create user PMSAlex identified by PMSAlex;
grant create session to PMSAlex; 赋予数据库连接权限
grant insert,update,select,delete on PMSAlex.W_PSDDOCUMENT to PMSAlex;
conn PMSAlex/PMSAlex;
select * from PMSAlex.W_PSDDOCUMENT;

库迁移相关

# 表转表空间，复制col列数据执行即可。
SELECT ('alter table '||a.TABLE_NAME||' move tablespace ALEXNEW;') AS col FROM user_tables a WHERE tablespace_name='ALEX';
SELECT ('alter table '||t.TABLE_NAME||' move tablespace ALEXNEW;') AS col FROM dba_tables t WHERE tablespace_name='ALEX' and t.OWNER='ALEX';
# 索引转表空间，针对lob类型索引应该先转分区，复制v_sql列数据执行即可。
SELECT 'alter table '||t.owner||'.'||t.table_name||' move tablespace ALEX lob('||t.column_name ||') store as(tablespace ALEXNEW);' v_sql FROM dba_lobs t WHERE t.tablespace_name='ALEX' and t.OWNER='ALEX';
# 针对手动添加的索引进行如下sql查询，复制sql列数据执行即可。
SELECT 'ALTER INDEX '||t.INDEX_NAME||' REBUILD TABLESPACE ALEXNEW;' sql FROM user_indexes t WHERE TABLESPACE_NAME='ALEX';
SELECT 'ALTER INDEX '||t.INDEX_NAME||' REBUILD TABLESPACE ALEXNEW;' sql FROM dba_indexes t WHERE  TABLESPACE_NAME='ALEX' and t.OWNER='ALEX';
# 在转空间的过程中会产生失效的索引，应rebuild下可在toad操作，执行以下查询复制sql列数据执行即可。
SELECT 'ALTER INDEX '||t.INDEX_NAME||' REBUILD;' sql FROM user_indexes t WHERE status='UNUSABLE';
ALTER INDEX "unique_dbom_code" REBUILD TABLESPACE ALEXNEW;
# 报错序列不存在，即Sequence的tablespace的名也未改可以执行如下语句(ALEX为你的项目名)
DROP SEQUENCE ALEX.${TABLENAME};

字段顺序调整

#1.查询出表的id,注意：owner和object_name的值要大写
--DBA用户使用
select object_id,object_name from all_objects where owner='PMSALEX' and object_name='TABLENAME';
--表归属用户可以使用
select object_id from obj where object_name='TABLENAME';
#2.通过id查出该表所有字段的顺序
select obj#,col#,name from sys.col$ where obj#=188299 order by col#;
#3.更新字段顺序,需要sys用户或是System用户 as sysdba登录，
# 或是其他拥有sysdba权限的用户，但是这里的操作一定要小心。
--更新受影响字段的col#，自己根据情况自己组织语句
update sys.col$ set col#=col#+1 where obj#=188299 and col#>4;
--更新目标字段的col#
update sys.col$ set col#=5 where obj#=188299 and name='FIELDNAME';
#4.commit;
#5.重启oracle服务

其它语句

(1)查目录 ，找不到导入导出路径时用
	select * from dba_directories;
(2)Oracle 高版本导出到低版本11->10，在低版本上找到具体版本
	select * from v$version;
	在高版本上导出时加上 version=10.2.0.4.0

num_rows不可信问题

1、把用户下的表改为statistics模式，使其num_rows的值可信
	select 'analyze table '||table_name||' compute statistics;' from user_tables where tablespace_name='PMSAlex' and INSTR(table_name, 'W_')>0
2、查询所有有数据的业务表，利用Excel构造成导库的语句
	select 'select * from '||table_name FROM user_tables where tablespace_name='PMSAlex' and INSTR(table_name, 'W_')>0 and num_rows>0;

ORA-28001密码过期

SQL>ALTER USER username IDENTIFIED BY password;（更换密码）
SQL>ALTER USER system account unlock；（解除锁定的方法）
--为防止多次错误后锁定账户，将次数修改为N/无限次
SQL>alter profile default limit FAILED_LOGIN_ATTEMPTS N/unlimited;
--查看被锁时间：
select username,lock_date from dba_users;

ORA-12516监听错误

错误代码含义：TNS监听程序找不到符合协议堆栈要求的可用处理器
原因及解决方法：修改连接数的限制 –session数不够

a.已sysdba 登录PL/SQL 或者Worksheet
b.查询目前连接数、最大连接数
	select count(*) from v$process;
	select value from v$parameter where name = 'processes';或show parameter processes;
c.更改系统连接数
	alter system set processes=1000 scope=spfile;//默认的连接数为150
d.创建pfile
	create pfile from spfile;
e.重启Oracle服务，或重启Oracle服务器；//reboot

ORA-39213导入错误

Metadata processing is not available

sqlplus /nolog;
SQL> conn sys/sys as sysdba;
SQL> exec dbms_metadata_util.load_stylesheets;

数据库显示：PL/SQL procedure successfully completed.

ORA-01940无法删除已连接用户

1.首先将索要删除的用户锁定，这句必须执行，否则之后杀死进程无效！
     alter user 用户名 account lock;
2.从【v$Session】表查看当前用户占用资源，有使用资源的情况下，肯定不能删除用户
     select saddr,sid,serial#,paddr,username,status from v$session where username = '用户名';
3. 杀死status为【 INACTIVE】的进程，sid和seria值为该列下的数值
     alter system kill session 'sid,serial';
4. 删除用户，如果不成功，即还是会报01940错，因为还有【 INACTIVE】进程没杀死
     drop user 用户名 cascade;

编码相关错误

ORA-39064: 无法写入日志文件

ORA-29285: 文件写入错误

导入索引报错，中文数据乱码

解决方案:（导入导出两边都要改，改成一样的）
一、windows
运行 -》 regedit -》查找 键值 NLS_LANG 
注册表路径：HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMExx\NLS_LANG
将字符集 SIMPLIFIED CHINESE_CHINA.ZHS16GBK 修改为AMERICAN_AMERICA.AL32UTF8
二、centos7
--查看oracle数据库字符集： 
　　select userenv('language') from dual; 
--查看oracle数据库的编码 
　　select * from nls_database_parameters where parameter ='NLS_CHARACTERSET'; 
--查询结果：
　　NLS_CHARACTERSET AL32UTF8
--修改oracle数据库字符集：（在SQL Plus中）
　　sqlplus / as sysdba;
　　sql> conn / as sysdba;
　　sql> shutdown immediate;
　　sql> startup mount;
　　sql> alter system enable restricted session;
　　sql> alter system set job_queue_processes=0;
　　sql> alter system set aq_tm_processes=0;
　　sql> alter database open;
　　sql> alter database character set internal_use AL32UTF8;
　　# alter database character set internal_use ZHS16GBK;
　　# alter database character set internal_use UTF8;
　　sql> shutdown immediate;
　　sql> startup;
--再次查看oracle数据库字符集
　　select userenv('language') from dual;

低效SQL识别

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;

自动备份与清除脚本

@echo off

rem set backupfile=f_database_%date:~0,4%-%date:~5,2%-%date:~8,2%.dmp

rem set logfile=f_database_%date:~0,4%-%date:~5,2%-%date:~8,2%.log

rem delete 30days files

forfiles /p "D:\temp\dmp" /d -30  /c "cmd /c echo deleting @file ... && del /f @path"

cd D:\temp\dmp

rem backup schemas

set backupfile=sshe_%date:~0,4%-%date:~5,2%-%date:~8,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%.dmp

set logfile=sshe_%date:~0,4%-%date:~5,2%-%date:~8,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%.log

expdp sshe/sshe directory=DIR_DP  dumpfile=%backupfile%  logfile=%logfile% schemas=sshe parallel=4

服务器上传文件备份

脚本：fileBackup.bat

1 2	@echo off xcopy "D:\alex01" "E:\alex02" /e/I/d/h/r/y

表数据定时同步脚本

merget.bat

1	sqlplus PMSAlex/PMSAlex@127.0.0.1:1521/ORCL @"E:\merge.sql"

merge.sql

delete from PMSAlex.LINKTEST02 b where b.ID not in 
(select c.ID from PMSAlex.LINKTEST02 c inner join PMSAlex.LINKTEST01@PMSAlexLINK d on c.ID=d.ID);
merge into PMSAlex.LINKTEST02 b using PMSAlex.LINKTEST01@PMSAlexLINK k on (b.ID=k.ID) 
when matched then update set b.NAME=k.NAME,b.AGE=k.AGE 
when not matched then insert values(k.ID, k.NAME,k.AGE);
commit;
quit;