oracle

登录

-- 登录
sqlplus / as sysdba
sqlplus sys/xxx@orcl as sysdba
sqlplus sys/xxx@//172.16.1.5:1521/orcl as sysdba

创建

-- 创建表空间
create tablespace XXXX logging datafile 'D:\ORADBDATA\XXXX.ora' size 50m autoextend on next 50m maxsize 20480m extent management local;
-- 创建用户
create user XXXX profile default identified by XXXX default tablespace XXXX;
-- 给用户授权
grant connect,resource,dba to XXXX;
-- 删除表空间
Drop tablespace XXXX INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
-- 删除用户
drop user XXXX cascade;

创建（高级）

CREATE TABLESPACE XXXX  
LOGGING  
DATAFILE  
  'D:\db\oradata\orcl\XXXX_01.dbf' SIZE 4096M,  
  'D:\db\oradata\orcl\XXXX_02.dbf' SIZE 4096M,  
  'D:\db\oradata\orcl\XXXX_03.dbf' SIZE 4096M  
AUTOEXTEND ON  
NEXT 1024M MAXSIZE UNLIMITED  
EXTENT MANAGEMENT LOCAL  
SEGMENT SPACE MANAGEMENT AUTO;  
  
CREATE TEMPORARY TABLESPACE XXXX  
TEMPFILE  
  'D:\db\oradata\orcl\XXXX_TEMP_01.dbf' SIZE 2048M,  
  'D:\db\oradata\orcl\XXXX_TEMP_02.dbf' SIZE 2048M  
AUTOEXTEND ON  
NEXT 1024M MAXSIZE UNLIMITED  
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;  
create user XXXX identified by XXXX default tablespace XXXX temporary tablespace XXXX_TEMP;  
GRANT connect,resource,dba TO XXXX; 

导入导出

-- 导入(注意表空间名称) 
Imp RDSYSEDUV8/eplugger@localhost:1521/orcl file=c:\work\20191009.dmp full=y log=c:\work\log.txt
-- 导出(注意检查空表是否能导出)
exp RDSYSEDUV8/eplugger@orcl file=c:\work\20190808.dmp grants=no consistent=y

数据泵导入导出

-- 创建目录对象
CREATE DIRECTORY dump_dir AS 'D:\db\dump';
GRANT READ, WRITE ON DIRECTORY dump_dir TO pro30029_test;
-- 导出命令
expdp 模式名/xxx@orcl DIRECTORY=dump_dir DUMPFILE=文件名.dmp LOGFILE=文件名.log SCHEMAS=模式名
-- 导入命令
impdp 目标模式名/eplugger@orcl directory=dump_dir dumpfile=文件名.DMP schemas=模式名 remap_schema=原模式名:目标模式名 remap_tablespace=原表空间:目标表空间 FULL=y

部分导入导出

expdp XXXX/XXXX@orcl DIRECTORY=TABLE_EXPORT_DIR DUMPFILE=%DUMP_FILENAME% LOGFILE=%EXPDP_LOG_FILENAME% TABLES=XXXX.TABLE1,XXXX.TABLE2,XXXX.TABLE3 COMPRESSION=ALL
impdp XXXX/XXXX@orcl DIRECTORY=TABLE_EXPORT_DIR DUMPFILE=XXXX.dmp LOGFILE=XXXX.log TABLES=XXXX.TABLE1,XXXX.TABLE2,XXXX.TABLE3 TABLE_EXISTS_ACTION=REPLACE 

密码过期

-- 查询数据库状态
SELECT * FROM dba_users a where a.username='RDSYSEDUV8_DEV';
-- 查询数据库密码过期策略
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
-- 修改数据库密码过期策略为永不过期
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
-- 重新初始化密码
alter user RDSYSEDUV8_DEV identified by eplugger;