Oracle常用命令

不忘初心收录于类别 DB

2019-01-08 2023-01-04 约 1695 字预计阅读 4 分钟条评论

注意

本文最后更新于 2023-01-04，文中内容可能已过时。

Oracle常用的维护命令

1 表空间

1.1 创建表空间

extent management 有两种方式：extent management local（本地管理），extent management dictionary（数据字典管理），默认的是local

1
SQL> create tablespace bwcx datafile '/data/oracle/oradata/orcl/bwcx1.dbf' size 1G autoextend on next 256M;

扩展表空间后期使用

1
SQL> alter tablespace bwcx add datafile '/data/oracle/oradata/orcl/bwcx2.dbf' size 1G autoextend on next 256M;

创建临时表空间

1
SQL> create temporary tablespace bwcx_t tempfile '/data/oracle/oradata/orcl/bwcx_t1.dbf' size 1G autoextend on next 256M;

1.2 查询表空间

1
2
SQL> select * from dba_tablespaces;
SQL> select * from user_tablespaces;

1.3 删除表空间

1
2
3
SQL> drop tablespace bwcx_d including datafiles;
SQL> drop tablespace bwcx_d including contents and datafiles;
SQL> drop tablespace bwcx_d including contents and datafiles cascade constraint;

including contents #删除表空间及对象； including contents and datafiles #删除表空间、对象及数据文件； including contents cascade constraint #删除关联； including contents and datafiles cascade constraint #含前两项。

删除临时表空间

1
SQL> drop tablespace bwcx_t including contents and datafiles;

连接

1
SQL> conn bwcx_user/bwcx_user;

查询用户使用的表空间及临时表空间

1
2
3
SQL> select username,default_tablespace,temporary_tablespace from dba_users;
SQL> select username,default_tablespace,temporary_tablespace from user_users;
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TEST_USER';

查询临时表空间数据文件路径

1
SQL> select tablespace_name,file_name from dba_temp_files;

查询表空间数据文件路径

1
2
SQL> select tablespace_name,file_name,bytes from dba_data_files;
SQL> select tablespace_name,file_name,bytes from dba_data_files where tablespace_name ='BWCX_D';

2 用户

2.1 创建用户

1
SQL> create user 用户名 identified by "密码" default tablespace 表空间 temporary tablespace 临时表空间;

2.2 修改密码

1
SQL> alter user TEST_USER identified by "newpassword";

带特殊字符时，如果出现提示enter value for xxx

1
2
SQL> set define off
SQL> alter user TEST_USER identified by "newpassword";

2.3 删除用户

1
SQL> drop user 用户名 cascade;

2.4 修改用户表空间

修改用户默认表空间

1
SQL> alter user test default tablespace test1;

修改用户临时表空间

1
SQL> alter user test temporary tablespace test_t1;

2.5 权限

参考：GRANT

2.5.1 角色查询

1
select * from dba_roles;

常用角色就三个：

CONNECT
RESOURCE
DBA

2.5.2 角色拥有的权限

1
select * from DBA_SYS_PRIVS where GRANTEE='RESOURCE';

2.5.3 授权用户

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SQL> grant connect,resource to 用户名;
SQL> grant unlimited tablespace to 用户名;
SQL> grant create view to 用户名;
SQL> grant create job to 用户名;
SQL> grant create public database link to 用户名;
SQL> grant create session to 用户名;
SQL> grant select on 表名 to 用户名;
SQL> grant insert on 表名 to 用户名;
SQL> grant update on 表名 to 用户名;
SQL> grant select,insert,update,delete,all on 表名 to 用户名;
# 所有表查询（dba权限执行）
SQL> grant select any table to reader;

2.5.4 撤销授权

1
2
3
SQL> revoke connect from reader;
SQL> revoke select on wcpt_pt_pay_pre from reader;
SQL> revoke select any table from reader;

2.5.5 查询用户权限

查询用户拥有的角色

1
select * from user_role_privs;

查询用户拥有的权限

1
select * from user_sys_privs;

赋予权限

1
2
grant 权限 to user_name;
grant 角色 to user_name;

撤回权限

1
revoke 权限 from user_name;

创建角色

1
create role role_name

给权限赋予权限

1
2
grant create session to role_name;
grant select, insert, delete, update to role_name;

查询角色拥有的权限

1
select * from dba_sys_privs where grantee='resource';

3 修改密码不过期

1
SQL> alter profile default limit password_life_time unlimited;

4 directory操作

查询directory

1
SQL> select * from dba_directories;

创建directory

1
SQL> create directory bwcxbak as '/databak/bwcxdata/dmp/';

删除directory

1
SQL> drop directory bwcxbak;

授权默认directory(使用Oracle默认的directory)

1
SQL> grant read, write on directory DATA_PUMP_DIR to bwcx_user;

授权directory给用户访问

1
SQL> grant read, write on directory bwcxbak to bwcx_user;

5 约束

根据表查询约束

1
select * from user_constraints where table_name='TEST';

根据约束查询表

1
select * from all_constraints where constraint_name='SYS_C001011964'

删除约束

1
ALTER TABLE TEST DROP CONSTRAINT SYS_C0014147;

6 expdp导出

6.1 常用的过滤SQL表达式

EXCLUDE与INCLUDE规则相同，使用方法一致

过滤所有的SEQUENCE,VIEW

1
EXCLUDE=SEQUENCE,VIEW

过滤单个表对象

1
2
3
EXCLUDE=TABLE:"= 'EMP'"
转义后
EXCLUDE=TABLE:\"= \'EMP\'\"

过滤多个表对象，需要转义才能执行成功

1
2
3
EXCLUDE=TABLE:"IN('SYSTEM_LOG','SYSTEM_BUS_LOG')
转义后
EXCLUDE=TABLE:\"IN\(\'SYSTEM_LOG\',\'SYSTEM_BUS_LOG\'\)\"

模糊匹配

1
2
3
EXCLUDE=TABLE:"LIKE 'TMS%'"
转义后
EXCLUDE=TABLE:\"LIKE\ \'TMS%\'\"

过滤所有的SEQUENCE,VIEW以及表对象EMP,DEPT

1
EXCLUDE=SEQUENCE,VIEW,TABLE:"IN ('EMP','DEPT')"

过滤指定的索引对象INDX_NAME

1
EXCLUDE=INDEX:"= 'INDX_NAME'"

通过query过滤指定表数据

1
query=TMS_ORDER_SHIP:'" where 1=2"',TMS_TRANS_SHIP_ROUTE:'" where 1=2"',TMS_ORDER_SHIP_FEE:'" where 1=2"',TMS_ASSET_DRIVER:'" where 1=2"',TMS_FULL_PROCESS_IMPORT2:'" where 1=2"'

复杂过滤

1
EXCLUDE=TABLE:\"LIKE\ \'\%LOG\'\",STATISTICS,GRANT,PROCEDURE,PACKAGE,VIEW,CONSTRAINT,TRIGGER,PROCOBJ

1
EXCLUDE=TABLE:\"IN\(\'SYSTEM_LOG\',\'SYSTEM_BUS_LOG\'\)\" EXCLUDE=TABLE:\"LIKE\ \'TMS%\'\"

使用expdp备份时，建议exclude掉自己不需要备份的东西

1
exclude=USER,GRANT,SYSTEM_GRANT,ROLE_GRANT,DEFAULT_ROLE,TABLE_QUOTA,PASSWORD_HISTORY

示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
expdp ${user}/${passwd} \
DIRECTORY=DATA_PUMP_DIR \
SCHEMAS=${user} \
EXCLUDE=statistics \
filesize=2048M \
parallel=4 \
dumpfile=bak_${date}_%U.dmp \
compression=all

参数说明：
filesize # 单个DUMP文件的最大容量
parallel # 并行度
dumpfile # 如使用变量 %U ，impdp时也需使用此变量

7 impdp导入

1
2
3
4
5
6
7
8
impdp ${dbuser}/${dbpasswd} DIRECTORY=DATA_PUMP_DIR dumpfile=${filename} remap_tablespace=bwcx:bwcx1 remap_schema=bwcx_user:bwcx_user1 exclude=statistics table_exists_action=replace transform=segment_attributes:n

参数说明：
remap_tablespace #将表空间对象重新映射到另一个表空间
remap_schema #将一个用户的的数据迁移到另外一个用户
exclude=statistics #排除统计值
table_exists_action=replace #删除已存在表，重新建表并追加数据
transform=segment_attributes:n #TRANSFORM适用场景，导入和导出的时候，有些表空间不一样。segment_attributes Y：默认值，表示这个段的属性(物理属性，存储属性，表空间和logging)都将被包含在DDL的语句中。N:表示导入该对象的时候，不会指定表空间等属性，只是简单的创建一个对象。

导入指定表

1
impdp ${dbuser}/${dbpasswd} directory=DATA_PUMP_DIR dumpfile=expdp.dmp tables=bwcx_user.table1,bwcx_user.table2 remap_schema=bwcx_user:bwcx_user1 table_exists_action=replace transform=segment_attributes:n

offline（关闭）表空间

1
SQL> alter tablespace bwcx_d offline;

online（打开）表空间

1
SQL> alter tablespace bwcx_d online;

Oracle表数据从快照区恢复

1
SQL> select * from abc as of timestamp  to_timestamp('2020-01-14 19:00:00','yyyy-mm-dd hh24:mi:ss');

输出每页行数，缺省为24,为了避免分页，可设定为0。

1
set pagesize 0

输出一行字符个数，缺省为80

1
set linesize 500

目录

Oracle常用命令

1 表空间

1.1 创建表空间

1.2 查询表空间

1.3 删除表空间

2 用户

2.1 创建用户

2.2 修改密码

2.3 删除用户

2.4 修改用户表空间

2.5 权限

2.5.1 角色查询

2.5.2 角色拥有的权限

2.5.3 授权用户

2.5.4 撤销授权

2.5.5 查询用户权限

3 修改密码不过期

4 directory操作

5 约束

6 expdp导出

6.1 常用的过滤SQL表达式

7 impdp导入