---

最全面详细讲解Oracle入门（新手必看)
Linux系统下使用impdp导入dmp文件

一、概述

1、用户

sys 和system都是系统管理员（DBA），拥有最大的权限，密码是安装时设置的；scott是普通用户，拥有一些用于学习的表，初始密码是tiger。

2、内置角色

角色是权限的集合，以下是三个内置角色：

sysdba 数据库管理员，拥有最多权限，包括：打开和关闭、备份和恢复数据库、日志管理、会话管理、创建数据库、用户管理等。

sysoper 数据库操作员，包括权限：打开和关闭、备份和恢复数据库、日志管理、会话管理。

normal 普通用户，只有查询表的权限。

二、启停oracle

Linux下启动/关闭Oracle服务和 oracle监听启动/关闭/查看状态

1、启动

su - oracle
lsnrctl start
sqlplus / as sysdba
startup

2、停止

sqlplus / as sysdba
SQL> alter system checkpoint	#保存一下检查点，防止丢失数据
SQL> SHUTDOWN IMMEDIATE
[oracle@localhost ~]$ lsnrctl stop

三、访问oracle

1、登录本地oracle

su - oracle
sqlplus / as sysdba

2、登录远程oracle

语法：sqlplus 用户名/密码@主机IP:端口号/实例名

sqlplus oracle/123456@192.168.2.1:1521/db_test

四、常用sql

1、表空间

查看创建表空间的语句

SELECT dbms_lob.substr(DBMS_METADATA.GET_DDL('TABLESPACE', 表空间名字.tablespace_name))FROM DBA_TABLESPACES 表空间名字;

查看用户属于哪个表空间

select username,default_tablespace from dba_users  where username='db_test';
select * from user_users;		#查看当前用户使用的表空间
SELECT * FROM DBA_USERS;		#查看所有用户对应的表空间

查看表空间文件的位置

select file_name from dba_data_files;

查看DATA_PUMP_DIR路径

select * from dba_directories;

查看表空间

select * from dba_tablespaces where tablespace_name = '表空间名字(大写)';
select * from Dba_Tablespaces;	#查看所有表空间
select   tablespace_name,table_name  from user_tables WHERE tablespace_name='表空间名称';	#查询表空间下的表
select TABLESPACE_NAME from dba_tablespaces;    #列出表空间

创建表空间

#test为表空间名字，100m代表初始大小，autoextend on表示自动扩展
create tablespace test datafile '/u01/app/oradata/test/test.dbf' size 500m autoextend on;

#创建新的表空间文件，解决表空间满的问题
ALTER TABLESPACE 表空间名字 ADD DATAFILE '/app/oracle/oradata/test/test2.dbf' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE 31G;

删除表空间

drop tablespace 表空间名字 including contents and datafiles CASCADE CONSTRAINTS;		#删除非空表空间，包含物理文件

查看表空间物理文件的名称及大小

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

给用户授权能访问表空间

ALTER USER 用户名 QUOTA UNLIMITED ON 表空间名;

2、用户管理

创建用户

#创建用户db_test,密码为并关联TEST表空间
create user db_test identified by 123456 default tablespace TEST;

查询用户

select * from all_users where USERNAME='用户名(大写)';		#查询指定用户
select * from all_users;		#查看所有用户
select username from dba_users;	#查看所有用户
select * from user_users; 		#查看当前用户
show user; 						#显示当前用户名

切换用户

alter session set current_schema=db_test;	#切换到db_test用户

授权

获取批量取消普通用户dba权限的命令

select 'revoke dba from '||USER_INFO.grantee||';' from dba_role_privs USER_INFO where granted_role='DBA' and admin_option='NO';

grant connect, resource to db_test;        #给db_test用户授予连接权限
grant read,write on directory DATA_PUMP_DIR to db_test;    #给db_test用户添加对DATA_PUMP_DIR的读写权限
grant dba to db_test;		#授予dba权限
grant exp_full_database to 用户名;	#授予导出权限

角色

select * from user_role_privs;		#查看当前用户角色
select * from dba_role_privs;		#查看所有用户角色
select * from dba_role_privs where granted_role='DBA';	#查看哪些用户具有DBA角色
revoke dba from 用户名;				#取消DBA角色
select * from dba_roles;			#查看所有角色
select * from role_sys_privs where role = 'IMP_FULL_DATABASE';	#查看当前用户IMP_FULL_DATABASE角色所包含的权限

解锁用户

alter user 用户名 account unlock;	#解锁用户

删除用户

select sid,serial#,username from v$session;		#查询所有用户的会话连接
select sid,serial# from v$session where username='用户名(大写)';		#查看指定用户的会话信息
alter system kill session '12,235';			#kill会话信息

drop user db_test cascade;		#删除db_test用户
drop user "db_test" cascade;	#如果查出来的用户名是小写，则需要加双引号，忽略大小写

创建只读角色 - 创建只读用户(所有表可访问)

Oracle 创建只读角色 - 创建只读用户(所有表可访问)

修改密码

sqlplus / as sysdba
alter user system identified by 新密码;

3、实例

select status from v$instance;			#查看实例的状态
select instance_name from v$instance;	#查看实例名

4、表

查看创建表的sql语句

SELECT DBMS_METADATA.GET_DDL('TABLE','表名')FROM DUAL;

查看清理日志表的sql语句

echo "SELECT 'TRUNCATE TABLE '||TABLE_NAME||';' FROM USER_TABLES WHERE TABLE_NAME LIKE '%LOG%';" | sqlplus 用户名/密码@ip:端口/服务名

删除以日期结尾的日志表

SELECT 'DROP TABLE '||TABLE_NAME||';' FROM DBA_TABLES WHERE REGEXP_LIKE(TABLE_NAME,'.*_LOG_.*\d+') AND OWNER='${DB_USER}'

按条件删除表中数据

DELETE  FROM 表名 WHERE 条件;

查看表

select table_name from user_tables;		#列出所有的表
select * from user_tables;		#查看当前用户的表
#查看用户db_user下是否有flyway_schema_history表
SELECT COUNT(*) FROM ALL_TABLES WHERE OWNER = UPPER('db_user') AND TABLE_NAME = UPPER('flyway_schema_history')

flyway按版本排序

SELECT * FROM "schema_version"  order by "version" desc;

5、数据库

查看

select name from v$database;	#查看数据库名
select * from v$version;		#查看数据库版本
select dbtimezone, sessiontimezone, sysdate,systimestamp from dual;	#查看时区、时间

查看及修改字符集

查看字符集

sqlplus / as sysdba
SQL> select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';

oracle查看编码以及修改编码

五、导入导出

oracle导出导入dmp详解

只导出表结构，不导出数据

expdp  用户名/密码@服务名 \
      DIRECTORY=DATA_PUMP_DIR \
      exclude=PROCEDURE,FUNCTION,STATISTICS \
      DUMPFILE=导出后的文件名 \
      content=metadata_only \
      tables=表1,表2 \
      compression=all

或者

#rows=n代表不要数据行
expdp 用户名/密码@服务名 DIRECTORY=DATA_PUMP_DIR \
DUMPFILE=导出后的文件名 \
rows=n compression=all

非交互获取DATA_PUMP_DIR路径

echo "select dd.DIRECTORY_PATH from dba_directories dd WHERE dd.DIRECTORY_NAME = 'DATA_PUMP_DIR';" | sqlplus system/123456@192.168.1.2/orcl | grep -A2 'DIRECTORY_PATH' | sed -n '$p'

备份单张表

#格式：
expdp 用户名/密码@orcl DIRECTORY=DATA_PUMP_DIR \
tables=表空间名.表名 \
DUMPFILE=备份文件名 \
exclude=PROCEDURE,FUNCTION,STATISTICS \
compression=all

#示例：
expdp test_user/test_user@orcl DIRECTORY=DATA_PUMP_DIR \
tables=test_user.abc \
DUMPFILE=test_user.dmp \
exclude=PROCEDURE,FUNCTION,STATISTICS \
compression=all

如果导出多张表，可以这么写：

tables=(表空间名1.表名1,表空间名2.表名2)

导入单张表

impdp 用户名/密码@orcl directory=DATA_PUMP_DIR dumpfile=备份文件名 full=y logfile=日志文件名

六、非交互执行sql命令

Oracle| sqlplus的非交互式使用
服务名通过lsnrctl service查看

方法一，通过echo重定向

echo 'select username from dba_users;' | sqlplus 用户名/密码@172.16.2.190:1521/服务名

方法二，读取sql文件

sqlplus 用户名/密码@172.16.2.190:1521/服务名 @oracle.sql

七、实战

Oracle 19创建表空间和用户

show pdbs;		#查看所有容器
alter session set container=PDB1;	#切换到PDB1容器
select file_name from dba_data_files;	#查看表空间文件的位置
create tablespace test datafile '/u01/app/oradata/test/test.dbf' size 200m autoextend on NEXT 50M MAXSIZE 31G;

create user 用户名 identified by 密码 default tablespace 表空间名;
grant connect, resource,IMP_FULL_DATABASE  to 用户名;
grant read,write on directory DATA_PUMP_DIR to 用户名;
ALTER USER 用户名 QUOTA UNLIMITED ON 表空间名;	#给用户授权能访问表空间

Oracle 19还原库

参考文章：oracle11导出导入dmp详解
与Oracle11版本不一样的地方是需要指定IP地址:端口/pdb名

impdp 导入的用户名/密码@ip:port/pdb名 schemas=导出的用户名 \
directory=DATA_PUMP_DIR dumpfile=导入的dmp文件名 \
remap_schema=导出的用户名:导入的用户名 \
REMAP_TABLESPACE=导出的表空间:导入的表空间 \
EXCLUDE=STATISTICS TRANSFORM=segment_attributes:n \
table_exists_action=replace \
logfile=日志文件名