Oracle常用命令

不忘初心收录于类别 DB

2019-01-08 2024-06-05 约 1958 字预计阅读 4 分钟次阅读条评论

Oracle常用的维护命令

1 表空间

1.1 创建表空间

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

sql

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

扩展表空间后期使用

sql

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

创建临时表空间

sql

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

1.2 查询表空间

sql

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

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

sql

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';

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

sql

SQL> select tablespace_name,file_name from dba_temp_files;

查询表空间数据文件路径

sql

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.3 删除表空间

sql

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 #含前两项。

删除临时表空间

在某些情况下，当完成了大量的数据操作后，临时表空间可能不会立即释放。这时，你可以创建一个新的临时表空间，然后将默认的临时表空间切换到新的临时表空间，再删除原来的临时表空间。

创建一个新的临时表空间

sql

SQL> CREATE TEMPORARY TABLESPACE temp_new TEMPFILE '/opt/oracle/oradata/orcl/temp_new01.dbf' SIZE 1G AUTOEXTEND ON;

把一个用户的临时表空间更改为新的表空间

sql

SQL> ALTER USER test_user TEMPORARY TABLESPACE temp_new;

删除原有的临时表空间，在执行这个命令之前，请确保没有任何用户或进程正在使用旧的临时表空间

sql

SQL> drop tablespace bwcx_t including contents and datafiles;

检查旧的临时表空间是否仍然被占用

sql

SELECT s.sid, s.username, s.program FROM v$session s JOIN v$tempseg_usage t ON s.saddr = t.session_addr WHERE t.tablespace = '旧的临时表空间名称';

2 用户

2.1 创建用户

sql

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

2.2 修改密码

sql

SQL> alter user TEST_USER identified by "newpassword";

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

sql

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

2.3 删除用户

sql

SQL> drop user 用户名 cascade;

2.4 修改用户表空间

修改用户默认表空间

sql

SQL> alter user test default tablespace test1;

修改用户临时表空间

sql

SQL> alter user test temporary tablespace test_t1;

2.5 权限

参考：GRANT

2.5.1 角色查询

sql

select * from dba_roles;

常用角色就三个：

CONNECT
RESOURCE
DBA

2.5.2 角色拥有的权限

sql

select * from DBA_SYS_PRIVS where GRANTEE='RESOURCE';

2.5.3 授权用户

sql

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 撤销授权

sql

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 查询用户权限

查询用户拥有的角色

sql

select * from user_role_privs;

查询用户拥有的权限

sql

select * from user_sys_privs;

赋予权限

sql

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

撤回权限

sql

revoke 权限 from user_name;

创建角色

sql

create role role_name

给权限赋予权限

sql

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

查询角色拥有的权限

sql

select * from dba_sys_privs where grantee='resource';

3 Dblink

创建PUBLIC DATABASE LINK

sql

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)))';

删除

sql

drop public database link dblink名称

创建DATABASE LINK

sql

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)))';

删除

sql

drop database link dblink名称

4 修改密码不过期

sql

SQL> alter profile default limit password_life_time unlimited;

5 directory操作

查询directory

sql

SQL> select * from dba_directories;

创建directory

sql

SQL> create [or replace] directory bwcx_dump as '/databak/bwcxdata/dmp/';

删除directory

sql

SQL> drop directory bwcx_dump;

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

sql

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

授权directory给用户访问

sql

SQL> grant read, write on directory bwcx_dump to bwcx_user;

6 约束

根据表查询约束

sql

select * from user_constraints where table_name='TEST';

根据约束查询表

sql

select * from all_constraints where constraint_name='SYS_C001011964'

删除约束

sql

ALTER TABLE TEST DROP CONSTRAINT SYS_C0014147;

7 expdp导出

7.1 常用的过滤SQL表达式

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

过滤单个表对象，写在命令行上需要加转义符

text

EXCLUDE=TABLE:"= 'EMP'"
转义后
EXCLUDE=TABLE:\"= \'EMP\'\"

过滤多个表对象

text

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

模糊匹配

text

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

如果需要过滤多个表的数据，那么如果用命令方式写，因为需要转译，是很麻烦的，也容易出错，这时我们可以使用 parfile 文件来避免大量的转译

text

cat parfile.txt

exclude=statistics
exclude=table:"in ('OMS_TO_TMS_LOG','SYSTEM_FEE_LOG','SYSTEM_LOG','TMS_APP_TASK_ORDER_PAYD','TMS_CASHIER_JOURNAL','TMS_FULL_PROCESS_IMPORT','TMS_INVOICE_RECEIVED','TMS_INVOICE_SHIP','TMS_OA_ASSET_CAR','TMS_OA_ASSET_DRIVER','TMS_OA_PAYABLE_APPLY_ITEM','TMS_OA_PAYABLE_APPLY_REMAIN','TMS_ORDER_SHIP_B0923','TMS_R_GROSS_PROFIT_BYMONTH','TMS_REPORT_CASHIER_RECEIPT','TMS_REPORT_COST','TMS_REPORT_INCOME','TMS_REPORT_INCOME_COST','TMS_REPORT_INCOME_COST2','TMS_REPORT_PAY','TMS_REPORT_SALES','TMS_REPORT_TRANS','TMS_TRANS_TRANSPORT_PD_221226')"
exclude=table:"like 'API%'"
exclude=table:"like 'MDM%'"
exclude=table:"like 'WCPT%'"

parfile 与命令行可以组合使用

示例

bash

expdp ${user}/${passwd} \
directory=DATA_PUMP_DIR \
schemas=${user} \
filesize=2048M \
parallel=4 \
dumpfile=back_${date}_%U.dmp \
compression=all \
parfile=$(dirname "$0")/parfile.txt

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

8 impdp导入

bash

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:表示导入该对象的时候，不会指定表空间等属性，只是简单的创建一个对象。

导入指定表

bash

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（关闭）表空间

sql

SQL> alter tablespace bwcx_d offline;

online（打开）表空间

sql

SQL> alter tablespace bwcx_d online;

Oracle表数据从快照区恢复

sql

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

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

sql

SQL> set pagesize 0

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

sql

SQL> 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 Dblink

4 修改密码不过期

5 directory操作

6 约束

7 expdp导出

7.1 常用的过滤SQL表达式

8 impdp导入

相关内容

目录

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 Dblink

4 修改密码不过期

5 directory操作

6 约束

7 expdp导出

7.1 常用的过滤SQL表达式

8 impdp导入

相关内容

Oracle数据块损坏

Oracle迁移审计表AUD$

Oracle init.ora常用参数

Oracle维护

Oracle rename数据文件