常用sql语句

计算表的行数count，sh为oracle测试用户，times为该用户下的表select count(*) from sh.times;从表student01的B列中，查找不重复的数据select distinct B from student01;复制表create table time_hz tablespace xing1228 asselect * from sh.times将A列的数值加1，但是原始数据库中不变select a+1,b from student01;select a,(a+1) bieming,bfrom student01;查看当前用户所有表名select unique tname from col;查看还没有提交的事务select * fromv$locked_object;select * fromv$transaction;查看当前所有对象select * from tab;查看当前会话的SID和serialSELECT Sid, Serial#FROM V$sessionWHERE Audsid = Sys_Context('USERENV', 'SESSIONID');查询当前时间select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;使用sqlplus计算select 256*256 from dual;通过授权方式来创建用户grant connect,resource to nlj identified by "123456";用户之间复制数据创建一个表dep，dep列不能为空create table dep (dep number(2) not null, dname char(14), loc char(13));找出未断开的连接select process,osuser,username,machine,logon_time,sql_textfrom v$session a,v$sqltext bwhere a.sql_address=b.address;查看表空间名称及大小（MB）select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_sizefrom dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_namegroup by t.tablespace_name;查看表空间对应的物理文件位置及大小select tablespace_name, file_id,file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_filesorder by tablespace_name;查看UNDO段select segment_name, tablespace_name,r.status,(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,max_extents, v.curext CurExtentFrom dba_rollback_segs r, v$rollstat vWhere r.segment_id = v.usn(+)order by segment_name ;查看表空间使用情况select sum(bytes)/(1024*1024) asfree_space,tablespace_namefrom dba_free_spacegroup by tablespace_name;查看oracle版本Select version FROMProduct_component_versionWhere SUBSTR(PRODUCT,1,6)='Oracle';查看数据库创建时间和归档方式Select Created, Log_Mode, Log_Mode From V$Database;捕捉运行很久的SQL语句（TOP SQL）select username,sid,opname,round(sofar*100 / totalwork,0)  '%' as progress,time_remaining,sql_textfrom v$session_longops , v$sqlwhere time_remaining 0and sql_address = address and sql_hash_value = hash_value