oracle相关

女爷i 2022-05-20 05:15 171阅读 0赞

1、windows安装oracle11g

[https://www.2cto.com/database/201712/704034.html][https_www.2cto.com_database_201712_704034.html]

2、卸载

[https://www.cnblogs.com/huangjian20112939/p/3413173.html][https_www.cnblogs.com_huangjian20112939_p_3413173.html]

3、oracle 11g，创建用户，表空间，赋予用户权限，导入dmp

[https://blog.csdn.net/zhengsaisai/article/details/67631984][https_blog.csdn.net_zhengsaisai_article_details_67631984]

4、ora-00119和ora-00132问题

[https://blog.csdn.net/xb12369/article/details/26720275][https_blog.csdn.net_xb12369_article_details_26720275]

5、修改用户密码

[https://www.cnblogs.com/zhncnblogs/p/6609701.html][https_www.cnblogs.com_zhncnblogs_p_6609701.html]

6、查看/修改字符集

[https://www.cnblogs.com/fxyy/p/7795959.html][https_www.cnblogs.com_fxyy_p_7795959.html]

7、将某张表的数据还原到某个时间段（前提是该时间段中表结构没有改变过）

CREATE TABLE tmp AS SELECT \* FROM oldTable AS of timestamp to\_timestamp('2018-7-12 22:00:00', 'yyyy-mm-dd hh24:mi:ss');

oldTable为要还原表的表名，tmp为运行该sql后创建的临时表

运行之后，数据库中将会生成一张临时表（tmp）

然后将原来的表删除（oldTable）

将tmp表的名字改为oldTable

8、查找所有clob字段的表

select t.column\_name ,DATA\_TYPE,TABLE\_NAME from user\_tab\_columns t where  DATA\_TYPE='CLOB'

9.wm\_concat排序

select m, max(r) from (select m, wm\_concat(n) over (partition by m order by n) r from t) group by m ;

把分隔后的 ， 改成 | select replace(wm\_concat(name),',','|') from test;

10.命令行导出全库

在sql下运行    create directory user\_dump\_tmp as 'd:\\user\_dump'

expdp xw/root@SERVER\_NAME schemas=XW dumpfile=test.dmp directory=user\_dump\_tmp log=test.log

server\_name为：J:\\oracle\\product\\11.2.0\\dbhome\_1\\NETWORK\\ADMIN\\tnsnames.ora下的

11.删除重复数据

DELETE FROM T\_ZS\_STUDENT\_INFO E  
    
  WHERE E.ROWID > (SELECT MIN(X.ROWID)  
    
  FROM T\_ZS\_STUDENT\_INFO x   
    
  WHERE X.id = E.id  );

12.impdb&&expdb  imp&&exp

impdb&&expdb

[https://blog.csdn.net/chengxumengzhidui/article/details/78093793][https_blog.csdn.net_chengxumengzhidui_article_details_78093793]

imp&&exp

[https://blog.csdn.net/wangchunyu11155/article/details/53635602][https_blog.csdn.net_wangchunyu11155_article_details_53635602]

\---SELECT \* FROM dba\_directories;

\---drop directory dump\_dir;

imp需要指定详细路径  impdb需要制定directory

13.查找带有换行符的记录

select \* from T\_SG\_LOUDONG\_INFO where REMARK like '%'||chr(13)||'%';

[https_www.2cto.com_database_201712_704034.html]: https://www.2cto.com/database/201712/704034.html
[https_www.cnblogs.com_huangjian20112939_p_3413173.html]: https://www.cnblogs.com/huangjian20112939/p/3413173.html
[https_blog.csdn.net_zhengsaisai_article_details_67631984]: https://blog.csdn.net/zhengsaisai/article/details/67631984
[https_blog.csdn.net_xb12369_article_details_26720275]: https://blog.csdn.net/xb12369/article/details/26720275
[https_www.cnblogs.com_zhncnblogs_p_6609701.html]: https://www.cnblogs.com/zhncnblogs/p/6609701.html
[https_www.cnblogs.com_fxyy_p_7795959.html]: https://www.cnblogs.com/fxyy/p/7795959.html
[https_blog.csdn.net_chengxumengzhidui_article_details_78093793]: https://blog.csdn.net/chengxumengzhidui/article/details/78093793
[https_blog.csdn.net_wangchunyu11155_article_details_53635602]: https://blog.csdn.net/wangchunyu11155/article/details/53635602