Oracle常用命令

Oracle常用的维护命令

创建表空间

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

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

扩展表空间 后期使用

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

创建临时表空间

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

删除表空间

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> drop tablespace bwcx_t including contents and datafiles;

连接

SQL> conn bwcx_user/bwcx_user;

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

SQL> select username,default_tablespace,temporary_tablespace from dba_users; 
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TEST_USER';

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

SQL> select tablespace_name,file_name from dba_temp_files;

查询表空间数据文件路径

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

用户

创建用户

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

修改密码

SQL> ALTER USER TEST_USER IDENTIFIED BY "newpassword";

删除用户

SQL> drop user 用户名 cascade;

修改用户表空间

修改用户默认表空间

SQL> alter user test default tablespace test1;

修改用户临时表空间

SQL> alter user test temporary tablespace test_t1;

授权用户

SQL> grant connect,resource to 用户名;
SQL> grant create view to 用户名;
SQL> grant create job to 用户名;
SQL> grant create public database link to 用户名;
SQL> grant unlimited tablespace 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;

撤销授权

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

查询用户权限

# 查看用户或角色系统权限(直接赋值给用户或角色的系统权限)
SQL> select * from dba_sys_privs where grantee = '用户名';
# 查看用户或角色所拥有的角色
SQL> select * from dba_role_privs where grantee = '用户名';

修改密码不过期

SQL> alter profile default limit password_life_time unlimited;

directory操作

查询directory

SQL> select * from dba_directories;

创建directory

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

删除directory

SQL> drop directory bwcxbak;

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

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

授权directory给用户访问

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

约束

根据表查询约束

select * from user_constraints where table_name='TEST';

根据约束查询表

select * from all_constraints where constraint_name='SYS_C001011964'

删除约束

ALTER TABLE TEST DROP CONSTRAINT SYS_C0014147;

expdp导出

常用的过滤SQL表达式

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

过滤所有的SEQUENCE,VIEW

EXCLUDE=SEQUENCE,VIEW

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

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

模糊匹配

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

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

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

过滤指定的索引对象INDX_NAME

EXCLUDE=INDEX:"= 'INDX_NAME'"

通过query过滤指定表数据

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

导出示例:

expdp user/passwd directory=DATA_PUMP_DIR schemas=user EXCLUDE=TABLE:\"LIKE\ \'\%LOG\'\",STATISTICS,GRANT,PROCEDURE,PACKAGE,VIEW,CONSTRAINT,TRIGGER,PROCOBJ dumpfile=bak.dmp compression=all

expdp user/passwd directory=DATA_PUMP_DIR schemas=user EXCLUDE=TABLE:\"IN\(\'SYSTEM_LOG\',\'SYSTEM_BUS_LOG\'\)\" EXCLUDE=TABLE:\"LIKE\ \'TMS%\'\" dumpfile=bak.dmp compression=all

impdp导入

impdp ${dbuser}/${dbpasswd} DIRECTORY=DATA_PUMP_DIR dumpfile=${filename} remap_tablespace=bwcx:bwcx1 remap_schema=bwcx_user:bwcx_user 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:表示导入该对象的时候，不会指定表空间等属性，只是简单的创建一个对象。

offline（关闭）表空间

SQL> alter tablespace bwcx_d offline;

online（打开）表空间

SQL> alter tablespace bwcx_d online;

Oracle表数据从快照区恢复

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

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

set pagesize 0

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

set linesize 500

查杀会话进程

通过top命令获取进程PID

通过sql查询SID及SERIAL#

select SID,SERIAL# from v$session where paddr in (select addr from v$process where spid in ('15552'));

查询锁死的session

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;

杀死会话进程

alter system kill session '223,10057';

根据 PID 查询执行的 sql

SELECT sql_text FROM v$sqltext a WHERE (a.hash_value,a.address) IN (SELECT DECODE(sql_hash_value,0,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr,sql_address) FROM v$session b WHERE b.paddr=( SELECT addr FROM v$process c WHERE c.spid=15552)) ORDER BY piece ASC;

太多的时候可以获取 kill 语句

select 'alter system kill session '''||sid||','||serial#||''';' from v$session where paddr in (select addr from v$process where spid in ('15552'));

更多用法：Oracle维护