常用oracle sql一览

--查看oracle数据库表，索引所占用的空间大小
Select Segment_Name, Sum(bytes) / 1024 / 1024/1024 as Gsize
From User_Extents
Group By Segment_Name
order 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_text
FROM v$sqltext a
WHERE (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 b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE 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 by
SELECT *
  FROM DEALER_MODULE
 WHERE DEL_MARK = 0
 start with MOD_ID = 430
connect 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_wait
SELECT * FROM v$session_event
--视图记录数据库启动以来所有等待事件的汇总信息。通过v$system_event视图，用户可以迅速第获得数据库运行的总体概括
select * from v$system_event
--通过会话id（sid）查看关联的完整sql
select 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 asc

select 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_list
TABLESPACE tbl_pro --保存表空间 
BUILD IMMEDIATE --创建视图时就生成数据 
REFRESH FORCE --如果可以快速刷新则进行快速刷新，否则完全刷新 
on demand --按照指定方式刷新 
START WITH SYSDATE --第一次刷新时间 
next SYSDATE + 1 as
select 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 jin
alter table cai add  (d varchar2(30),e number(4))
alter table cai rename column e  to f
alter table cai modify d varchar(40)
alter table cai drop column  f
ALTER 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;

--tablespace
CREATE TABLESPACE PRJ_AR
DATAFILE 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\EXDB\PRJ_AR' SIZE 50M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING
ONLINE
BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
/
--user
CREATE USER PRJ_AR
IDENTIFIED BY PRJ_AR
DEFAULT TABLESPACE PRJ_AR
TEMPORARY TABLESPACE TEMP
PROFILE 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_IDX
local
--索引分区
--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