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

授权用户

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

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
expdp bwcx_user/bwcx_user directory=bwcxbak schemas=bwcx_user dumpfile=bwcxbak.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;

删除用户

1
SQL> drop user bwcx_user cascade;

删除表空间

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

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
-------------    本文结束  感谢您的阅读    -------------