0%

Oracle命令使用

创建表空间

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
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
SQL> select username,default_tablespace from dba_users order by username;
SQL> select username,default_tablespace from dba_users where username='TEST_USER_1';

查询表空间数据文件路径

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 bwcx_user identified by bwcx_user default tablespace bwcx temporary tablespace bwcx_t;

删除用户

1
SQL> drop user bwcx_user cascade;

授权用户

1
2
3
SQL> grant connect,resource to bwcx_user;
SQL> grant create view to bwcx_user;
SQL> grant create job to bwcx_user;

修改密码不过期

1
SQL> alter profile default limit password_life_time unlimited;

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;

expdp导出

常用的过滤SQL表达式

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

过滤所有的SEQUENCE,VIEW

1
EXCLUDE=SEQUENCE,VIEW

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

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
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导入

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_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(关闭)表空间

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

查杀会话进程

通过top命令获取进程PID

image-20201014144435691

通过sql查询SIDSERIAL#

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

image-20201014144610705

杀死会话进程

1
alter system kill session '223,10057';

根据PID查询执行的sql

1
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;
-------------    本文结束  感谢您的阅读    -------------
请作者一杯咖啡。