Oracle操作笔记

Oracle操作笔记

启服务

• linux下启动oracle

su - oracle e
sqlplus /nolog 
conn /as sysdba 
startup 
exit 
lsnrctl start 
exit

停服务

• linux下关闭oracle

su - oracle 
sqlplus /nolog 
conn /as sysdba 
shutdown immediate 
exit 
lsnrctl stop 
exit

建库脚本

create tablespace dp_crqssingle_uc datafile '/opt/oracle/oradata/develop/dp_crqssingle_uc.dbf' size 10m autoextend on; 
create user dp_crqssingle_uc identified by dp_crqssingle_uc default tablespace dp_crqssingle_uc;
grant connect to dp_crqssingle_uc;
grant dba to dp_crqssingle_uc;
grant unlimited tablespace to dp_crqssingle_uc;

create tablespace credit datafile '/u01/app/oracle/oradata/XE/credit.dbf' size 10m autoextend on;
create user credit identified by credit_8520 default tablespace credit;
grant connect to credit;
grant dba to credit;
grant unlimited tablespace to credit;

create tablespace QUERY1102 datafile 'D:\Root\Opt\SDE\oraclexe\oradata\Data\QUERY1102.dbf' size 10m autoextend on;
create user QUERY1102 identified by QUERY1102 default tablespace QUERY1102;
grant connect to QUERY1102;
grant dba to QUERY1102;
grant unlimited tablespace to QUERY1102;

大系统解锁

select * from outeruser a where a.userid='yangqinhua' for update;
update outeruser  set password='96J0TlUbNWulsaAHLaFRVg==' where userid='yangqinhua';
update outeruser set lockflag='0',locktime='' where userid='yangqinhua';

oracle用户被锁

• 报错信息

ORA-28000: the account is locked

• 解决命令

（1）conn sys/sys as sysdba; //以DBA的身份登录
（2）alter user scott account unlock;// 然后解锁
（3）conn scott/tiger //弹出一个修改密码的对话框，修改一下密码就可以了

数据库导出

exp V1_03_018/V1_03_018@xe  file=D:/V1_03_018.dmp log=D:/V1_03_018.log 

数据库导入

imp V1_03_018/V1_03_018    BUFFER=64000 file=/u01/app/oracle/oradata/dump/V1_03_018.dmp ignore=y  full=y