Oracle相关命令:用expdp命令导出数据库对象名称为VP0723,则要导入的命令用impdp且对象名称要一致。
impdp导入数据库命令
1、已sys身份登录:sqlplus "/as sysdba"
然后创建导入文件目录:create directory exp as 'E:\';
2、给scott用户赋予在指定目录的操作权限,最好以system等管理员赋予。
grant read,write on directory exp to VP0811;
3、导入:impdp VP0812/VP0812 schemas=VP0812 directory=exp dumpfile=*.dmp logfile=imp.log
impdp VP0811/VP0811 DIRECTORY=exp DUMPFILE=VP0811.DMP FULL=y;
exp mdmdev0711/mdmdev_0711@192.168.5.13/utf8 file=e:\mdmdev0711.dmp full=y
删除新建的用户:
1、查看用的sid
select username,sid,serial# from v$session;
2、杀死相应的进程
alter system kill session '11,132';
3、查看是否杀掉
select saddr,sid,serial#,paddr,username,status from v$session where username is not null
4、删掉用户
impdp导入数据库命令
1、已sys身份登录:sqlplus "/as sysdba"
然后创建导入文件目录:create directory exp as 'E:\';
2、给scott用户赋予在指定目录的操作权限,最好以system等管理员赋予。
grant read,write on directory exp to VP0811;
3、导入:impdp VP0812/VP0812 schemas=VP0812 directory=exp dumpfile=*.dmp logfile=imp.log
impdp VP0811/VP0811 DIRECTORY=exp DUMPFILE=VP0811.DMP FULL=y;
exp mdmdev0711/mdmdev_0711@192.168.5.13/utf8 file=e:\mdmdev0711.dmp full=y
删除新建的用户:
1、查看用的sid
select username,sid,serial# from v$session;
2、杀死相应的进程
alter system kill session '11,132';
3、查看是否杀掉
select saddr,sid,serial#,paddr,username,status from v$session where username is not null
4、删掉用户
drop user VP0723 cascade;
当不知道数据库表结构时根据数据变动查询哪个表:
select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT
from v$sqlarea b where b.SQL_TEXT like '%解决新报工系统日志无数据%' order by b.FIRST_LOAD_TIME desc
时间处理函数:
to_date('2014-07-28', 'yyyy-MM-dd')
select to_char(sysdate,'day','NLS_DATE_LANGUAGE=''american''') from dual;
select to_char(sysdate,'day','NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE''') from dual;
select to_char(sysdate,'yyyy-MM-dd')||' '||to_char(sysdate,'day','NLS_DATE_LANGUAGE=''american''') from dual;