文章目录
最全面详细讲解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> 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; #删除非空表空间，包含物理文件
查看表空间物理文件的名称及大小
selecttablespace_name,file_id, file_name,round(bytes/(1024*1024),0) total_spacefromdba_data_filesorder 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 sysdbaalter 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=''
查看表
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; #查看数据库版本
查看及修改字符集
查看字符集
sqlplus / as sysdba SQL> select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';
五、导入导出
只导出表结构，不导出数据
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
六、非交互执行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=日志文件名
