ORACLE 运维命令

本文是本人在运维过程中常用的脚本

### bash脚本

--查看数据库字符集
select userenv('language') nls_lang from dual;

--dmp文件第2、3字节记录的字符集
select nls_charset_name(to_number('0354','xxxx')) from dual;

--关闭、启用外键
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R';
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R';

--禁用、启用用户创建的触发器
SELECT 'alter trigger '|| OBJECT_NAME ||' disable;' FROM DBA_OBJECTS WHERE OBJECT_TYPE='TRIGGER' and owner='YO_ONLINE' order by OBJECT_NAME;
SELECT 'alter trigger '|| OBJECT_NAME ||' enable;' FROM DBA_OBJECTS WHERE OBJECT_TYPE='TRIGGER' and owner='YO_ONLINE' order by OBJECT_NAME;

--删除表数据
SELECT 'truncate table '||table_name||';' FROM ALL_TABLES WHERE OWNER='YO_TEST';

select 'drop table '||table_name||';' FROM ALL_TABLES WHERE OWNER='YO_TEST';

SELECT ATS.NUM_ROWS,ATS.TABLE_NAME FROM ALL_TABLES ATS WHERE ATS.OWNER='YO_ONLINE' ORDER BY ATS.TABLE_NAME ASC;

--设置显示宽度、长度
show pagesize; --查看目前的pagesize
set pagesize 300; --设置pagesize为300
show linesize; --查看当前的linesize宽度
set linesize 300; --设置linesize宽度

--修改字符集
sqlplus /nolog
SQL>connect sys/sys 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 ZHS16GBK; //跳过超子集检测
SQL>ALTER DATABASE national CHARACTER SET INTERNAL ZHS16GBK;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP
=================================
SQL> conn /as sysdba
======================================
Connected.
================================
SQL> shutdown immediate;
================================
Database closed.

Database dismounted.

ORACLE instance shut down.
================================
SQL> startup mount
================================
ORACLE instance started.

Total System Global Area  236000356 bytes

Fixed Size                   451684 bytes

Variable Size             201326592 bytes

Database Buffers           33554432 bytes

Redo Buffers                 667648 bytes

Database mounted.
================================
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
================================
System altered.
================================
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
================================
System altered.
================================
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
================================
System altered.

SQL> alter database open;

Database altered.
================================
SQL> ALTER DATABASE CHARACTER SET AL32UTF8; --ZHS16GBK;
================================
ALTER DATABASE CHARACTER SET ZHS16GBK

*

ERROR at line 1:

ORA-12712: new character set must be a superset of old character set

提示我们的字符集：新字符集必须为旧字符集的超集，这时我们可以跳过超集的检查做更改：
================================
SQL> ALTER DATABASE character set INTERNAL_USE AL32UTF8;  --ZHS16GBK;
================================
Database altered.
================================
SQL> select * from v$nls_parameters;
================================
略

19 rows selected.

重启检查是否更改完成：
================================
SQL> shutdown immediate;
================================
Database closed.

Database dismounted.
ORACLE instance shut down.
================================
SQL> startup
================================
ORACLE instance started.
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
================================
SQL> select * from v$nls_parameters;
================================
略
19 rows selected.

D、client端字符集修改

在 /home/oracle与 /root用户目录下的.bash_profile中

添加或修改 export NLS_LANG="AMERICAN_AMERICA.UTF8" 语句

关闭当前ssh窗口。


--导出、导入
exp
imp cross_dev/sss@cross_test fromuser=cross_dev touser=cross_dev rows=y indexes=n ignore=y commit=y file=d:\cross_dev.dmp

--oracle server字符集
select userenv('language') from dual;

--oracle dmp字符集
用oracle的exp工具导出的dmp文件也包含了字符集信息，dmp文件的第2和第3个字节记录了dmp文件的字符集。如果dmp文件不大，比如只有几M或几十M，
可以用UltraEdit打开(16进制方式)，看第2第3个字节的内容，如0354，然后用以下SQL查出它对应的字符集:
select nls_charset_name(to_number('0354','xxxx')) from dual;
如果dmp文件很大，比如有2G以上(这也是最常见的情况)，用文本编辑器打开很慢或者完全打不开，可以用以下命令(在unix主机上):
cat exp.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6
然后用上述SQL也可以得到它对应的字符集。

--oracle client端的字符集
$echo $NLS_LANG

(1).数据库服务器字符集
select * from nls_database_parameters
来源于props$，是表示数据库的字符集。

(2).客户端字符集环境
select * from nls_instance_parameters
其来源于v$parameter，表示客户端的字符集的设置，可能是参数文件，环境变量或者是注册表

(3).会话字符集环境
select * from nls_session_parameters
来源于v$nls_parameters，表示会话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_parameters一致。

(4).客户端的字符集要求与服务器一致，才能正确显示数据库的非Ascii字符。
如果多个设置存在的时候，NLS作用优先级别：Sql function > alter session > 环境变量或注册表> 参数文件> 数据库默认参数

字符集要求一致，但是语言设置却可以不同，语言设置建议用英文。如字符集是zhs16gbk，则nls_lang可以是American_America.zhs16gbk。

============================导入数据=======================================================================================================
--登录oracle的sqlplus
sqlplus / as sysdba;

--查看主数据库归档日志的sequence是否一致
select max(sequence#) from v$archived_log;

---查看当前的数据库文件（表空间）存放位置：
 select name from v$datafile;

--创建表空间
create tablespace yoonline datafile '/data/app/oracle/oradata/orcl/yoonline01.dbf' size 3072m;

create tablespace yoonline datafile '/data/app/oracle/oradata/yo/yoonline01.dbf' size 4096m;

create tablespace yotest datafile '/data/app/oracle/oradata/orcl/yotest01.dbf' size 4096m;
　3000m指的是3000MB,10G

--创建用户并指定表空间
create user yo_online identified by yo_online123 default tablespace yoonline;

create user yo_test identified by yo_test123 default tablespace yotest;

--给用户授权
grant dba,connect,resource to yo_online;

--查询服务器端的字符集
select userenv('language') from dual;

--删除用户所有表
select 'drop table '||table_name||';' from cat where table_type='TABLE';

--退出sqlplus,设置client端字符集与服务端一致
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8


--批量建表

在命令窗口执行
@（sql文件位置）

--关闭、启用外键
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R';
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R';

--禁用、启用用户创建的触发器
SELECT 'alter trigger '|| OBJECT_NAME ||' disable;' FROM DBA_OBJECTS WHERE OBJECT_TYPE='TRIGGER' and owner='YO_ONLINE' order by OBJECT_NAME;
SELECT 'alter trigger '|| OBJECT_NAME ||' enable;' FROM DBA_OBJECTS WHERE OBJECT_TYPE='TRIGGER' and owner='YO_ONLINE' order by OBJECT_NAME;





--修改表的字段长度
select 'ALTER TABLE '||TABLE_NAME||' MODIFY '||COLUMN_NAME||' VARCHAR2(1000);' from user_tab_columns where data_type='VARCHAR2' AND DATA_LENGTH>=100 AND DATA_LENGTH<1000 and TABLE_NAME like 'T%';
select 'ALTER TABLE '||TABLE_NAME||' MODIFY '||COLUMN_NAME||' VARCHAR2(200);' from user_tab_columns where data_type='VARCHAR2' AND DATA_LENGTH<100 and TABLE_NAME like 'T%';

### expdp导出 impdp导入数据
sqlplus / as sysdba
execute dbms_metadata_util.load_stylesheets;

expdp yogs/yogsdbserver@orcl TABLES=t_bs_test dumpfile=expdp.dmp logfile=expdp.log DIRECTORY=dpdata;

扩展表空间

准备

• 首先查看表空间的名字和所属文件

  select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space
  from dba_data_files
  order by tablespace_name;

• 查看表空间的使用情况

  SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
  FROM dba_free_space
  GROUP BY tablespace_name;
  SELECT a.tablespace_name,
  a.bytes total,
  b.bytes used,
  c.bytes free,
  (b.bytes * 100) / a.bytes "% USED ",
  (c.bytes * 100) / a.bytes "% FREE "
  FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
  WHERE a.tablespace_name = b.tablespace_name
  AND a.tablespace_name = c.tablespace_name;

1.增加数据文件

ALTER TABLESPACE game ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;

ALTER TABLESPACE YOONLINE ADD DATAFILE '/data/oradata/yo/yoonline01.dbf' SIZE 20480M;

2.手动增加数据文件尺寸

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' RESIZE 4000M;

ALTER DATABASE DATAFILE '/data/oradata/yo/yoonline01.dbf' RESIZE 20480M;

3.设定数据文件自动扩展

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;

设定后查看表空间信息

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED,C.BYTES FREE,(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

启动oracle

sqlplus /as sysdba
sqlplus / as sysdba
startup

lsnrctl start

/sbin/iptables -I INPUT -p tcp --dport 1521 -j ACCEPT
/etc/init.d/iptables stop

---

导入备份数据

• 切换用户

  su oracle

• 环境变量生效 到~目录(当前登录用户目录)

  source ./bash-profie
  #或者 source .bash_profile

• 导入数据

  imp 用户/密码 fromuser=备份数据用户 touser=要导入数据的用户 rows=y indexes=n ignore=y commit=y file=dmp文件地址
  # 如果导入数据量大 加入 feedback=10000 buffer=20480000 参数可以根据实际情况调整
  # 例子
  ./imp yo_test/yo_test123 fromuser=yo_online touser=yo_test rows=y indexes=n ignore=y commit=y feedback=10000 buffer=20480000 file=/data/orabak/oracleBack_201605222219.dmp