数据库 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

相关文章