oracle常用命令

oracle数据库基础命令

使用DBA连接数据库

su - oracle
source ~/.bash_profile
sqlplus / as sysdba

创建表空间

#创建一个表空间，初始大小为1024M，每次自增100M，最大值不限
create tablespace WEIHE DATAFILE '/data/oracle/oradata/weihe/WEIHE.dbf' SIZE 1024M AUTOEXTEND ON  next 100M maxsize unlimited; 

新增表空间

#适用于原来的8k表空间只能支持32G数据，已经满了，必须为用户新增一个新的表空间文件
alter tablespace WEIHE add default '/data/oracle/oradata/weihe/WEIHE2.dbf'SIZE 1024M AUTOEXTEND ON  next 100M maxsize unlimited;

查看表空间

#通过数据字典查看有多少个用户
select username from dba_users;

#查看所有表空间
select tablespace_name from dba_tablespaces;

#查看用户所属表空间
select username,default_tablespace from dba_users order by username;

#查看表空间的名字及大小：select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size   
from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name   
group by t.tablespace_name;

创建用户

#创建一个用户weihe，密码为weihe123
create user weihe identified by weihe123;
#修改用户weihe的默认表空间为WEIHE_DATA 
alter user weihe default tablespace WEIHE_DATA QUOTA unlimited on WEIHE_DATA;
#修改用户临时表空间为temp
alter user weihe temporary tablespace temp;
#授权dba权限给用户，一般不建议
-- grant dba to weihe;
#授权connect和resource组的权限给用户
#connect角色只有创建会话的权限，create session
#resource角色有create CLUSTER,INDEXTYPE,OPERATOR,PROCEDURE,SEQUENCE,TABLE,TRIGGER,TYPE权限。
grant connect,resource to weihe;
#授权导入角色权限给用户，这个角色有80种权限。几乎拥有对数据库进行的所有操作权限，不包括对系统和数据库配置的修改。
GRANT IMP_FULL_DATABASE TO weihe;
#授权导出角色权限给用户，除了创建表的权限，多为查询权限。
GRANT EXP_FULL_DATABASE TO weihe;
#修改用户对表空间的使用率为无限使用。
ALTER USER weihe QUOTA UNLIMITED ON WEIHE_DATA;
commit;

创建只有查询权限的用户

create user userName identified by password;
GRANT CREATE SESSION TO userName; --授权登录
grant select any table to userName; --授予查询任何表du
grant select any dictionary to userName;--授予 查询任何字典
执行zhi上面三步就行了，那么dao这个用户就只zhuan有查询shu限，其权他的权限都没有
select 'grant select on base.' || table_name || ' to test_cx;' from user_tables;

导入导出

#使用imp导入、exp导出，weihe为用户名，斜线后为密码，@后为数据库连接地址和实例名，file后是导出文件的地址，log是导出时的日志，如果有错误，能够有据可查，owner是指定导出的用户数据。
exp weihe/weihe@127.0.0.1:1521/weihe file=weihe20180801.dmp  log=weihe20180801.log owner=weihe buffer=1000000;
imp weihe/weihe@127.0.0.1:1521/weihe file=weihe20180801.dmp  log=weihe20180801.log fromuser=weihe touser=weihe buffer=1000000;

#使用数据泵导入导出
expdp 'weihe/weihe'@127.0.0.1:11521/weihe directory=MYDIR dumpfile=weihe_20210113.dmp logfile=weihe_20210113.log schemas=weihe  compression=all;
impdp 'weihe/weihe'@127.0.0.1:11521/weihe directory=MYDIR dumpfile=weihe_20210113.dmp logfile=weihe_20210113.log schemas=weihe;
#如果要排除导入导出某些表，增加如下参数：
INCLUDE=TABLE:\"like \'TM_GH%\'\"
#指定导出或者导入某些表,不能和schemas=参数一起使用
tables=TABLE1,TABLE2,TABLE3
#指定条件导入导出,跟select的where条件一样
query=\"where bureau_code in \(\'0450\'\)\" 
#如果要从用户1导入到用户2，则增加如下参数：
remap_schema=dmp里的用户:要导入的用户
remap_tablespace=dmp里的表空间:要导入的表空间
#导入时忽略原dmp里面的表空间，直接导入到被导入用户默认的表空间，此参数会忽略remap_tablespace参数
transform=segment_attributes:n
#导入时指定EXCLUDE= GRANT排除所有对象类型上的对象授予和系统特权授予
exclude=grant
#如果是rac集群，导出的时候需要指定非集群模式
cluster=N
··· 注意事项
··· 用户必须有导入导出权限，否则过程可能失败。高版本数据库要导入到低版本数据库，需要在导出的时候指定导出版本号，例如19c导出为11g，参数为：version=11.2.0.4.0

imp导入报错表空间，修改dmp文件里面的表空间名

由于导出的表空间和要被导入的表空间不一致，在使用imp导入时有一部分表会报错，哪怕制定了tablespace=xxx也会报错。
grep -a 'TABLESPACE "xxxx"'  xxxx.dmp
使用sed修改dmp文件里的表空间名字即可；
sed -i s/'TABLESPACE "xxxx"'/'TABLESPACE "yyyy"'/g xxxx.dmp

高版本导出库到低版本

例如：12C导出到11g，在导出dmp的时候指定版本为11g的。

1、select * from v$version 查询要导入的目标数据库版本
2、导出：expdp c##pwp/pwp@orcl dumpfile=/data/local_12c_to_11g.dmp nologfile=Y version=11.2.0.4.0
3、查询数据库设置的directory：SELECT * FROM dba_directories， 将刚刚导出的dmp拷贝至DATA_PUMP_DIR对应目录下
4、导入：impdp pwp/pwp@10.111.58.189:1521/pwp dumpfile=LOCAL_12C_TO_11G.DMP nologfile=Y fromuser=c##pwp touser=pwp
5、select job_name,state from dba_datapump_jobs 查看导入操作是否种终止
··· 注意：正确停止导入导出操作：ctrl+c后，export/import> stop_job=immediate ；ctrl+c后导入或导出操作并未终止

创建DBLink

由于oracle版本不一致，可能出现的问题。如果link_weihe创建失败，执行：

#删除dblink
DROP DATABASE link LINK_WEIHE;
#创建dblink
CREATE DATABASE LINK LINK_WEIHE CONNECT TO weihe IDENTIFIED BY weihe
	USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = WEIHE)))';

查看数据库字符集

select userenv('language') from dual; 

查看该dmp文件的字符集

cat s201211.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6
0354

然后用以下SQL查出它对应的字符集:
SQL> select nls_charset_name(to_number('0354','xxxx')) from dual;
NLS_CHARSET_NAME(TO_NUMBER('0354','XXXX'

解决中文乱码

1）查看数据库字符集
    03:12:58 SQL> select userenv('language') from dual; 
    USERENV('LANGUAGE')
    ----------------------------------------------------
    AMERICAN_AMERICA.ZHS16GBK

2）编辑linux 的NLS_LANG变量
[oracle@oraserv ~]$ pwd
/home/oracle
[oracle@oraserv ~]$ vi .bashrc
    # .bashrc
    # Source global definitions
    if [ -f /etc/bashrc ]; then
            . /etc/bashrc
    fi
    # User specific aliases and functions
    export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
3）注销oracle用户，重新登录
    [oracle@oraserv ~]$ su - oracle
    [oracle@oraserv ~]$ env |grep LANG
    NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
    LANG=en_US.UTF-8
    [oracle@oraserv ~]$ export ORACLE_SID=prod
    [oracle@oraserv ~]$ env |grep LANG
    NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
    LANG=en_US.UTF-8
     
    通过sqlplus验证：
    [oracle@oraserv ~]$sqlplus / as sysdba
    SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 8 03:15:59 2011 
    Copyright (c) 1982, 2005, Oracle. All rights reserved.
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
     
    03:16:02 SQL> conn scott/tiger
    Connected.
    03:16:08 SQL> select * from tab;
    TNAME                          TABTYPE CLUSTERID
    ------------------------------ ------- ----------
    DEPT                           TABLE
    EMP                            TABLE
    BONUS                          TABLE
    SALGRADE                       TABLE
     
    03:16:16 SQL> update dept set dname='财务' where deptno=10;
    1 row updated.
     
    03:16:47 SQL> select * from dept;
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 财务   NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

oracle用户设置

1、oracle用户解锁
ALTER USER pwp ACCOUNT UNLOCK；

2、oracle设置用户登录输错密码次数为不限制
alter profile default limit failed_login_attempts unlimited;

3、查看所有用户
  select * from dba_users;
  select * from all_users;
  select * from user_users;

4、查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
  select * from dba_sys_privs;
  select * from user_sys_privs;
 
5、查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;

6、查看用户对象权限：
  select * from dba_tab_privs;
  select * from all_tab_privs;
  select * from user_tab_privs;

7、查看所有角色：
  select * from dba_roles;

8、查看用户或角色所拥有的角色：
  select * from dba_role_privs;
  select * from user_role_privs;
 
9、查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS
 
比如我要查看用户 wzsb的拥有的权限：
SQL> select * from dba_sys_privs where grantee='WZSB';
 
GRANTEE                        PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
WZSB                           CREATE TRIGGER                           NO
WZSB                           UNLIMITED TABLESPACE                     NO
比如我要查看用户 wzsb的拥有的角色：
SQL> select * from dba_role_privs where grantee='WZSB';
 
GRANTEE   GRANTED_ROLE   ADMIN_OPTION  DEFAULT_ROLE
------------------------------ ------------------------------ ------------ ------------
WZSB                           DBA                         NO              YES
10、查看一个用户所有的权限及角色
select privilege from dba_sys_privs where grantee='WZSB'
union
select privilege from dba_sys_privs where grantee in 
(select granted_role from dba_role_privs where grantee='WZSB' );

关闭某个会话或者连接

#基于连接的用户找到sid和serial的值
select sid,serial# from v$session where username='WEIHE;
#通过下列语句杀死会话
alter system kill session 'sid,serial#';
例如：alter system kill session '222,123';