Oracle常用命令

创建表空间

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

授权用户

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

修改密码不过期

SQL> alter profile default limit password_life_time unlimited;

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

导出

expdp bwcx_user/bwcx_user directory=bwcxbak schemas=bwcx_user dumpfile=bwcxbak.dmp compression=all

导入

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;

删除用户

SQL> drop user bwcx_user cascade;

删除表空间

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

查询表空间数据文件路径

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表数据从快照区恢复

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