Oracle常用命令

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
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,temporary_tablespace from dba_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";
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;

常用角色就三个:

  1. CONNECT
  2. RESOURCE
  3. DBA
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
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';
1
SQL> alter profile default limit password_life_time unlimited;

查询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;

根据表查询约束

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
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:\"LIKE\ \'\%LOG\'\",STATISTICS,GRANT,PROCEDURE,PACKAGE,VIEW,CONSTRAINT,TRIGGER,PROCOBJ dumpfile=bak.dmp compression=all
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
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