惯用oracle sql一览

--查看oracle数据库表，索引所占用的空间大小Select Segment_Name, Sum(bytes) / 1024 / 1024/1024 as GsizeFrom User_ExtentsGroup By Segment_Nameorder by Gsize desc--oracle hint 强制指定走索引select /*+ index(t,IDX_AR_SELL)*/t.province, t.city, t.country  from ar_sell t group by t.type_code,t.province, t.city, t.country;--查看oracle数据库语言和编码select userenv('language') from dual;SIMPLIFIED CHINESE_CHINA.ZHS16GBK--查看包含undo的oracle参数show parameter undo--查看ORA-30012的错误信息oerr ora 30012--通过进程号取得相关的sql语句SELECT /*+ ORDERED */sql_textFROM v$sqltext aWHERE (a.hash_value, a.address) IN (SELECT DECODE (sql_hash_value,0, prev_hash_value,sql_hash_value),DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)FROM v$session bWHERE b.paddr = (SELECT addrFROM v$process cWHERE c.spid = '&pid'))ORDER BY piece ASC--正则表达式替换select regexp_replace('hello hello everybody,may I have your attention please?','^hello','one') from dual;select regexp_replace('hello hello everybody,may I have your attention please?','^hello$','one') from dual;select regexp_replace('hello hello everybody,may I have your attention please?','hello','one') from dual;UPDATE table1 t     SET t.sale = REPLACE(t.sale, '替换前', '替换后')   WHERE t.sale like '%替换前%'; --rollup grouping wm_concat rank select grouping(t.type_code),grouping(t.year_month), t.type_code, t.year_month, count(*),rank() over (partition by t.type_code order by count(*) desc)  from ar_sell t group by rollup(t.type_code, t.year_month) order by t.type_code, t.year_month-- having重复检测select PD_ID,count(shop_id) from SHOP_PD group by PD_ID,shop_id having count(shop_id ) > 1--start with connect bySELECT *  FROM DEALER_MODULE WHERE DEL_MARK = 0 start with MOD_ID = 430connect by prior PAR_ID = MOD_ID ORDER BY level desc, MOD_ID, ORDER_VALUE-- 不是数字select * from dual where not REGEXP_LIKE('1不是数字1', '^[0-9]+\.{0,1}[0-9]*$')-- 是数字select * from dual where REGEXP_LIKE('1.1', '^[0-9]+\.{0,1}[0-9]*$') --查看表是否被锁SELECT a.sid, b.owner, object_name, object_type  FROM v$lock a, all_objects b WHERE TYPE = 'TM'   and a.id1 = b.object_id;--删除被锁表SELECT sid,serial# FROM v$session WHERE sid = &sid;alter system kill session ‘sid,serial#’;--查询服务器中哪些语句走的是全表扫描select * from v$session_longops order by start_time desc;select opname,target,start_time,last_update_time,sql_hash_value from v$session_longops order by start_time desc;--根据sql_hash_value查询到相关的sql语句select * from v$sqltext where hash_value=822428411 order by piece;--查询每台应用服务器占用仍然存活的oracle数据库会话情况select machine, count(*) c from v$session where status = 'ACTIVE' group by machine order by c desc;--查询当前数据库的所有会话（有效，无效）select status,count(status) from v$session group by status;--查询每台应用服务器占用oracle数据库会话情况select machine, count(*) c from v$session group by machine order by c desc;--根据机器名查询该机器发出的所有会话select *  from v$session machine where machine like '%computername%'--版本select * from v$version--所有事件分类select * from v$event_name--视图记录的是数据库当前连接的session信息（动态信息）select * from v$session--视图记录的是当前数据库连接的活动session正在等待的资源或者事件信息。select * from v$session_waitSELECT * FROM v$session_event--视图记录数据库启动以来所有等待事件的汇总信息。通过v$system_event视图，用户可以迅速第获得数据库运行的总体概括select * from v$system_event--通过会话id（sid）查看关联的完整sqlselect sql_text  from v$sqltext a where a.hash_value =       (select sql_hash_value from v$session b where b.sid = '989') order by piece ascselect a.CPU_TIME,--CPU时间 百万分之一       a.OPTIMIZER_MODE,--优化方式       a.EXECUTIONS,--执行次数       a.DISK_READS,--读盘次数       a.SHARABLE_MEM,--占用shared pool的内存多少       a.BUFFER_GETS,--读取缓冲区的次数       a.COMMAND_TYPE,--命令类型(3:select,2:insert;6:update;7delete;47:pl/sql程序单元)       a.SQL_TEXT,--Sql语句       a.SHARABLE_MEM,       a.PERSISTENT_MEM,       a.RUNTIME_MEM,       a.PARSE_CALLS,       a.DISK_READS,       a.DIRECT_WRITES,       a.CONCURRENCY_WAIT_TIME,       a.USER_IO_WAIT_TIME  from SYS.V_$SQLAREA a WHERE PARSING_SCHEMA_NAME = 'CHEA_FILL'--表空间 order by a.CPU_TIME desc--建立物化视图CREATE MATERIALIZED VIEW mv_base_province_listTABLESPACE tbl_pro --保存表空间 BUILD IMMEDIATE --创建视图时就生成数据 REFRESH FORCE --如果可以快速刷新则进行快速刷新，否则完全刷新 on demand --按照指定方式刷新 START WITH SYSDATE --第一次刷新时间 next SYSDATE + 1 asselect case         when t3.p_name is not null then          t3.p_name || ',' || t2.p_name || ',' || t1.p_name         when t2.p_name is not null then          t2.p_name || ',' || t1.p_name         else          t1.p_name       end as p_full_name,       t1.*  from base_province_list t1  left join base_province_list t2 on t1.par_index = t2.p_index                                 and t2.par_index is not null  left join base_province_list t3 on t2.par_index = t3.p_index                                 and t3.par_index is not null--增删改重命名alter table liu rename to jinalter table cai add  (d varchar2(30),e number(4))alter table cai rename column e  to falter table cai modify d varchar(40)alter table cai drop column  fALTER TABLE AR_SELL MODIFY YEAR_MONTH NUMBER(6);CREATE TABLE TEMP_TABLE AS SELECT * FROM AR_SELL WHERE 1 = 2;ALTER TABLE TEMP_TABLE MODIFY YEAR_MONTH NUMBER(6);INSERT INTO TEMP_TABLE SELECT * FROM AR_SELL;COMMIT;DROP TABLE AR_SELL;RENAME TEMP_TABLE TO AR_SELL;create table tbl1 as select * from tbl;--tablespaceCREATE TABLESPACE PRJ_ARDATAFILE 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\EXDB\PRJ_AR' SIZE 50M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITEDLOGGINGONLINEBLOCKSIZE 8KEXTENT MANAGEMENT LOCAL AUTOALLOCATESEGMENT SPACE MANAGEMENT AUTO/--userCREATE USER PRJ_ARIDENTIFIED BY PRJ_ARDEFAULT TABLESPACE PRJ_ARTEMPORARY TABLESPACE TEMPPROFILE DEFAULT/GRANT CONNECT TO PRJ_AR/GRANT DBA TO PRJ_AR/GRANT CREATE PROCEDURE TO PRJ_AR/GRANT CREATE SEQUENCE TO PRJ_AR/GRANT CREATE SESSION TO PRJ_AR/GRANT CREATE TABLE TO PRJ_AR/GRANT UNLIMITED TABLESPACE TO PRJ_AR/--锁定，解锁用户alter user  user_name account  unlock; alter user  user_name account  lock; --分区create table tbl(id number) partition by list (id)    (        partition             PT_tbl_1            values (1),        partition             PT_tbl_2            values (2),        partition             PT_tbl_default            values (default)    );--检查分区select partition_name,high_value,t.* from user_tab_partitions t where table_name='tbl'--分区索引（local后面不跟参数默认对所有分区建立相应索引）create index IDX_tbl_YEAR_MONTH on tbl (   YEAR_MONTH ASC)tablespace tblspace_IDXlocal--索引分区--rebuild索引alter index IDX_tbl_YEAR_MONTH rebuild;alter index IDX_tbl_YEAR_MONTH rebuild online;--sqlplus->新建->命令窗口 分析表（建立索引后，重新分析表，执行计划才会更新）execute dbms_stats.gather_table_stats(ownname => 'USER_NAME',tabname => 'TABLE_NAME' ,estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true ,degree => 1);--oracle语法（通过表B的条件来更新表A的内容）UPDATE A SET (A1, A2, A3) = (SELECT B1, B2, B3 FROM B WHERE A.ID = B.ID);update entp_shop t   set link_email = (select t1.email                       from user_info t1                      where t.shop_id = t1.shop_id                        and t1.email is not null) where t.link_email is null   and t.p_index like '34%';--sql server语法UPDATE A SET A1 = B1, A2 = B2, A3 = B3 FROM A, B WHERE A.ID = B.ID--增加表空间数据文件ALTER TABLESPACE "TBS_PRO" ADD DATAFILE 'D:\ORACLE\ORADATA\PRO01.DBF' SIZE 2048M REUSE