注意
本文最后更新于 2023-11-15，文中内容可能已过时。
Oracle常用的维护命令
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
| SQL> select * from dba_tablespaces;
SQL> select * from user_tablespaces;
|
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';
|
1
| SQL> create user 用户名 identified by "密码" default tablespace 表空间 temporary tablespace 临时表空间;
|
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";
|
1
| SQL> drop user 用户名 cascade;
|
修改用户默认表空间
1
| SQL> alter user test default tablespace test1;
|
修改用户临时表空间
1
| SQL> alter user test temporary tablespace test_t1;
|
参考：GRANT
1
| select * from dba_roles;
|
常用角色就三个：
CONNECTRESOURCEDBA
1
| select * from DBA_SYS_PRIVS where GRANTEE='RESOURCE';
|
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;
|
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;
|
查询用户拥有的角色
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
2
| grant create session to role_name;
grant select, insert, delete, update to role_name;
|
查询角色拥有的权限
1
| select * from dba_sys_privs where grantee='resource';
|
创建PUBLIC DATABASE LINK
1
| CREATE PUBLIC DATABASE LINK dblink名称 CONNECT TO tms_user IDENTIFIED BY "password" USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';
|
删除
1
| drop public database link dblink名称
|
创建DATABASE LINK
1
| CREATE DATABASE LINK dblink名称 CONNECT TO tms_user IDENTIFIED BY "password" USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';
|
删除
1
| drop database link dblink名称
|
1
| SQL> alter profile default limit password_life_time unlimited;
|
查询directory
1
| SQL> select * from dba_directories;
|
创建directory
1
| SQL> create [or replace] directory bwcx_dump as '/databak/bwcxdata/dmp/';
|
删除directory
1
| SQL> drop directory bwcx_dump;
|
授权默认directory(使用Oracle默认的directory)
1
| SQL> grant read, write on directory DATA_PUMP_DIR to bwcx_user;
|
授权directory给用户访问
1
| SQL> grant read, write on directory bwcx_dump to bwcx_user;
|
根据表查询约束
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;
|
EXCLUDE与INCLUDE规则相同，使用方法一致
过滤所有的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=back_${date}_%U.dmp \
compression=all
参数说明：
filesize # 单个DUMP文件的最大容量
parallel # 并行度
dumpfile # 如使用变量 %U ，impdp时也需使用此变量
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| 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
2
3
4
5
6
7
| 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。
输出一行字符个数，缺省为80