Oracle 常用SQL命令

数据运维 2024-04-09 泡泡 手机阅读

Oracle 常用SQL命令
1、备份单张表
创建复制表结构
create table employeesbak as select * from cims.employees
如果只复制表结构，只需要在结尾加上 where 1=0
插入数据
insert into employeesbak
select * from cims.employees
删除一条数据
delete from jjl_customersource jc where jc.code='C0000151721' and jc.name='唐';

select * from jjl_customersource j where j.code='C0000151721';

2、Sql代码
--查看所有的用户
select * from all_users;
--查看当前用户信息
select * from user_users;
--查看当前用户的角色
select * from user_role_privs;
--查看当前用户的权限
select * from user_sys_privs;
--查看当前用户的表可操作权限
select * from user_tab_privs;

3、获取星期几
select to_char(sysdate,'day') dayth from dual

5、查看所有被锁的表
select b.owner TABLEOWNER, b.object_name TABLENAME, c.OSUSER LOCKBY,
c.USERNAME LOGINID, c.sid SID, c.SERIAL# SERIAL
from v$locked_object a,dba_objects b, v$session c
where b.object_id = a.object_id AND a.SESSION_ID =c.sid;

6、解锁表
alter system kill session 'SID, SERIAL';

7、查看表空间的使用情况
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;

8、查看表空间的路径
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files where tablespace_name='SYSAUX'
order by file_name;

9、添加数据文件

alter tablespace SYSAUX add datafile '+DATA01/wyzx/datafile/sysaux_20210430' size 10 G;

给表空间SYSAUX添加10G的数据文件

10、删掉重复项
找出主键为nrcelldu_uk，start_time都重复的数据，只留下一条数据

delete from pm.F_5_C_S_NRCELLDU_PRB_Q a
where (a.nrcelldu_uk,a.start_time) in
(select nrcelldu_uk,start_time from pm.F_5_C_S_NRCELLDU_PRB_Q
group by nrcelldu_uk,start_time having count(*) > 1)
and rowid not in (select max(rowid)
from pm.F_5_C_S_NRCELLDU_PRB_Q group by nrcelldu_uk,start_time having count(*)>1
);
commit;

11、查看归档日志
SELECT A.NAME, A.TOTAL_MB / 1024, A.FREE_MB / 1024 FROM V$ASM_DISKGROUP A
————————————————

Oracle 常用SQL命令
1、备份单张表
创建复制表结构
create table employeesbak as select * from cims.employees
如果只复制表结构，只需要在结尾加上 where 1=0
插入数据
insert into employeesbak
select * from cims.employees
删除一条数据
delete from jjl_customersource jc where jc.code='C0000151721' and jc.name='唐';

select * from jjl_customersource j where j.code='C0000151721';

2、Sql代码
--查看所有的用户
select * from all_users;
--查看当前用户信息
select * from user_users;
--查看当前用户的角色
select * from user_role_privs;
--查看当前用户的权限
select * from user_sys_privs;
--查看当前用户的表可操作权限
select * from user_tab_privs;

3、获取星期几
select to_char(sysdate,'day') dayth from dual

5、查看所有被锁的表
select b.owner TABLEOWNER, b.object_name TABLENAME, c.OSUSER LOCKBY,
c.USERNAME LOGINID, c.sid SID, c.SERIAL# SERIAL
from v$locked_object a,dba_objects b, v$session c
where b.object_id = a.object_id AND a.SESSION_ID =c.sid;

6、解锁表
alter system kill session 'SID, SERIAL';

7、查看表空间的使用情况
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;

8、查看表空间的路径
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files where tablespace_name='SYSAUX'
order by file_name;

9、添加数据文件

alter tablespace SYSAUX add datafile '+DATA01/wyzx/datafile/sysaux_20210430' size 10 G;

给表空间SYSAUX添加10G的数据文件

10、删掉重复项
找出主键为nrcelldu_uk，start_time都重复的数据，只留下一条数据

delete from pm.F_5_C_S_NRCELLDU_PRB_Q a
where (a.nrcelldu_uk,a.start_time) in
(select nrcelldu_uk,start_time from pm.F_5_C_S_NRCELLDU_PRB_Q
group by nrcelldu_uk,start_time having count(*) > 1)
and rowid not in (select max(rowid)
from pm.F_5_C_S_NRCELLDU_PRB_Q group by nrcelldu_uk,start_time having count(*)>1
);
commit;

11、查看归档日志
SELECT A.NAME, A.TOTAL_MB / 1024, A.FREE_MB / 1024 FROM V$ASM_DISKGROUP A
————————————————

  1. http://www.cnph.cn/com/yhqda1dd/
  2. http://www.kuaijianjiameng.cn/com/yash1dx/
  3. http://cdfzsc.com/index.php?homepage=asaa21a
  4. http://www.zgksgjw.com/index.php?homepage=das21a
  5. http://tscyscwlw.com/index.php?homepage=fssd112
  6. http://b2c.airtra.cn/index.php?homepage=kibhhg9
  7. http://www.chinaxczxw.com/company/index.php?userid=425
  8. http://i.drymortar.net/ahj56ik/
  9. http://ww97au.chinalov.com/
  10. http://www.pos580.com/index.php?homepage=suzhou
  11. http://www.gaoyuanpt.com/index.php?homepage=suzhou
  12. http://www.pkufang.com/ask/q.asp?id=95
  13. http://www.meibaocn.com/ask/q.asp?id=110
  14. http://www.pkufang.com/ask/q.asp?id=94
  15. http://yc02pa.gl35w.com/
  16. http://i.drymortar.net/ahj56ik/
  17. http://www.6paper.com/index.php?homepage=uo-23_tj
  18. http://www.zbqd.com/index.php?homepage=peizi9
  19. www.scgwy.org/ask/show_3450.html
  20. www.sdgkw.org/ask/show_5622.html
  21. http://yc02pa.gl35w.com/
  22. http://www.pkufang.com/ask/q.asp?id=94
  23. www.sdgkw.org/ask/show_5622.html
  24. http://yc02pa.gl35w.com/
  25. http://www.pkufang.com/ask/q.asp?id=94
  26. http://ceshi.wpc.cn/index.php?homepage=peizi
  27. http://www.sinatechina.com/index.php?homepage=yajh1s
  28. http://www.yiyaoshebei.cn/index.php?homepage=iolns1
  29. http://zgybfmwlw.com/index.php?homepage=qq1123
  30. https://www.n360.cn/b2b/qiye_szgppz/
  31. https://www.ydjy8.com/index.php?homepage=rar405
  32. http://www.zdlhqcw.com/com/peizi999
  33. http://www.pkufang.com/ask/q.asp?id=95
  34. http://www.meibaocn.com/ask/q.asp?id=110
  35. www.364100.com/info/detail/246800
  36. http://zgxdpt.com/index.php?homepage=szpz
  37. http://xmwjyy.com/e/space/?userid=142
  38. http://www.shanxigwy.org/ask/show_1891.html
  39. http://www.dw28.com/shop/?shopid=952
  40. https://bbs.hellokid.com/topic-show-id-50992.html
  41. https://www.job5156.com/comp/1981441
  42. http://cn.ttfly.com/com/sddxx001
  43. http://cn.ttfly.com/index.php?homepage=sddxx001
  44. https://www.zgyjxxw.com/index.php?homepage=njgppz
  45. https://www.qiangdayun.com/index.php?homepage=b5r0n3u
  46. http://d0k7f9g.bolay.com.cn/
  47. https://www.carrymine.cn/index.php?homepage=dbsdb2
  48. http://jdxsazwxwlw.com/index.php?homepage=p6x1i1e
  49. http://bydmswlw.com/index.php?homepage=i8u1a4f
  50. http://zyzwlw.cn/index.php?homepage=x6g8v3q
  51. http://zssjwlw.com/index.php?homepage=f2r0a2g
  52. http://yszbwlw.com/index.php?homepage=y3k5l8
  53. https://zhuzao.com/index.php?homepage=y5p5b6
  54. https://www.springweb.cn/index.php?homepage=ber3sc
  55. https://www.sgc123.com/index.php?homepage=hnfer2s
  56. http://www.cnxhyp.com/index.php?homepage=fhsk23fd
  57. http://www.chinacaitang.com/index.php?homepage=szpz
  58. http://www.12580mh.com/index.php?homepage=qqmy5
  59. www.shanxigwy.org/ask/show_1880.html
  60. www.bidushe.com/u/2008.html
  61. www.tan8.com/user-thread-0-5187949-1-0.html
  62. www.lngwy.org/ask/show_3409.html
  63. www.njzp.com/company/index.php?c=show&id=2385
  64. news.lanhaizi.cn/n/2018/0831/9989.html
  65. http://www.cnxlssc.cn/index.php?homepage=zjgpz
  66. http://www.cnxlssc.cn/index.php?homepage=szpz
  67. http://chinacaitang.com/index.php?homepage=szpz
  68. http://www.rar405.maimait.net/
  69. www.lqjob88.com/company/cm1559013091182/
  70. https://www.mining120.com/com/rar405/introduce/
  71. https://www.mining120.com/com/rar405/sellsb/
  72. https://www.mining120.com/com/rar405/honor/
  73. https://www.mining120.com/com/rar405/job/
  74. https://www.mining120.com/com/rar405/contact/
  75. https://www.mining120.com/com/rar405
  76. http://e4g0f3v.bolay.com.cn/
  77. https://zhuzao.com/index.php?homepage=zjgpz
  78. http://jiaxiao.jiakao.com/jx/9636/news/179.html
  79. http://www.ip1689.com/index.php?homepage=szpz&file=introduce
  80. http://www.ip1689.com/index.php?homepage=szpz&file=credit
  81. http://www.ip1689.com/index.php?homepage=szpz&file=sell
  82. http://www.ip1689.com/index.php?homepage=szpz&file=mall
  83. http://www.lwhrss.com/member/index.php?uid=rar405
  84. https://show.precast.com.cn/index.php?homepage=rar405
  85. https://www.dingdx.com/index.php?homepage=q6t4d6p
  86. http://okjhs23.bolay.com.cn/
  87. https://www.dingdx.com/index.php?homepage=ojbh2x
  88. https://www.lidianshijie.com/index.php?homepage=suzhou
  89. http://www.brandfood.cn/index.php?homepage=fdfd32
  90. http://www.hnslmgl.com/ask/q.asp?id=114
  91. forum.vibunion.com/forum.php?mod=collection&action=view&ctid=251
  92. forum.vibunion.com/forum.php?mod=collection&action=view&ctid=250
  93. forum.vibunion.com/forum.php?mod=collection&action=view&ctid=249
  94. http://yuanqu.hbzxw.com.cn/home/news.html
  95. forum.vibunion.com/forum.php?mod=collection&action=view&ctid=248
  96. show.precast.com.cn/index.php?homepage=rar405
  97. www.jsgwyw.org/ask/show_137609.html
  98. http://user.show160.com/8709023/
  99. http://www.njzp.com/company/index.php?c=show&id=2385
  100. http://www.sucaijishi.com/index.php?m=member&c=space&uid=30729
  101.