--查看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
常用oracle sql一览
最新推荐文章于 2024-04-02 18:43:47 发布