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