oracle常用语句
oracle常用语句
--修改pl/sql时间显示
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD hh24:mi:ss';
--赋权
grant SELECT, INSERT, UPDATE, DELETE ON TF_SM_TEST to gd_ucr_crm;
--创建同义词
create or replace synonym TF_SM_TEST for gd_ucr_crm.TF_SM_TEST;
--删除同义词
DROP SYNONYM TF_SM_TEST;
--回收权限
revoke all on TF_SM_TEST from GD_UCR_SALE_CEN;
--过程函数授权语句范例
Grant execute on P_CRM_SALE_TEST to gd_ucr_crm;
--分区查询
gd_ucr_crm.TF_SM_TEST PARTITION(PAR_TF_SM_TEST_1)
--创建blink
create public database link DBLINK_CEN1 connect to gd_ucr_crm identified by h1cmzol_ using 'NGTEST';
--查看当条数据是第几条
select tt.rowno
from (select t.*, row_number() over(order by 1) rowno from gd_ucr_crm.TF_SM_TEST t) tt
where tt.SERIAL_NUMBER = '13511111111';
--创建index
create index IDX_TEST_USER_ID on TF_SM_TEST (USER_ID)
tablespace TBS_CL_TEST
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
) online;
--重建索引
alter index gd_ucr_crm.PK_TF_SM_TEST rebuild;
--修改表的某个字段属性
ALTER TABLE TF_SM_TEST RENAME column USER_IE TO USER_ID
--增加字段和备注
alter table TF_SM_TEST add USER_ID VARCHAR2(10) ;
comment on column TF_SM_TEST.USER_ID
is '用户编码';
--获取表所有字段并用逗号分隔显示在一个字段中
SELECT TO_CHAR(WMSYS.WM_CONCAT(A.COLUMN_NAME)) COLUMN_NAME
FROM(SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TF_SM_TEST' ) A
--数据恢复
select timestamp_to_scn(to_timestamp('2015-03-16 11:40:10.1','yyyy-MM-dd HH24:MI:ss.ff')) from dual;
insert into TF_SM_TEST_repair select * from gd_uop_crm.TF_SM_TEST AS OF SCN 14311403342206
create table TF_SM_TEST_repair SELECT * FROM TF_SM_TEST
SELECT * FROM TF_SM_TEST as of TIMESTAMP (SYSTIMESTAMP - INTERVAL '120' minute) where USER_NAME like '%恢复%'
grant select on TF_SM_TEST_repair to gd_uop_crm;
--查询包含某张表的所存储过程PROCEDURE和包 PACKAGE BODY
SELECT * FROM USER_SOURCE WHERE UPPER(TEXT) LIKE '%function_test%' --AND TYPE='FUNCTION' ;
SELECT * FROM USER_SOURCE
WHERE 1=1 and TYPE = 'PACKAGE'
AND UPPER(TEXT) LIKE '%TF_SM_TEST%';
select distinct object_type from user_objects;
select * from user_objectS where object_type= 'PACKAGE BODY';
select * from user_objectS where object_type= 'FUNCTION' AND OBJECT_NAME LIKE '%function_test%';
SELECT * FROM user_objectS WHERE UPPER(OBJECT_NAME) LIKE '%function_test%' --AND TYPE='FUNCTION' ;
--查询包含某个字段的所有表
SELECT * FROM dba_tab_columns where column_name='USER_ID';
SELECT * FROM user_arguments;
select * from user_source where type='PACKAGE BODY';
--查询索引属哪个表
select * from user_indexes where index_name ='PK_USER_ID';
--查询表被哪些视图/过程引用
Select owner, object_type, object_name, object_id, status
from sys.DBA_OBJECTS
where object_id in (Select object_id
from public_dependency
connect by prior object_id = referenced_object_id
start with referenced_object_id =
(Select object_id
from sys.DBA_OBJECTS
where object_name = 'TF_SM_TEST' -- 这里放 :name
));
--查询包含某个函数的所存储过程
SELECT * FROM DBA_SOURCE WHERE TEXT LIKE '%function_test%' --AND TYPE='FUNCTION' ;
select name,locks,pins
from v$db_object_cache
where locks > 0 and pins > 0 and type='PROCEDURE';
select * from v$access where object='pkg_function_test_limit' AND TYPE='PACKAGE';
--查询正在执行的存储过程
select b.sid,b.SERIAL#,a.OBJECT,a.OWNER, 'alter system kill session ' || '''' || b.sid || ',' ||b.SERIAL# || ''';' kill_command
from SYS.V_$ACCESS a, SYS.V_$session b
where a.type = 'PROCEDURE'
and (a.OBJECT like upper('%pkg_function_test_limit%') or
a.OBJECT like lower('%pkg_function_test_limit%'))
and a.sid = b.sid
and b.status = 'ACTIVE';
--或
select name from v$db_object_cache where locks > 0 and pins > 0 and type='PROCEDURE';
--Kill 正在执行的存储过程
alter system kill session '13565,45909';
--查看表空间大小
SELECT segment_name,OWNER,segment_type,MB FROM (select segment_name,OWNER,segment_type,bytes/1024/1024 MB from dba_segments
where tablespace_name='TBS_CL_TEST'
--and segment_name like '%TF_SM_TEST%'
--and segment_type='TABLE'
)
order by MB desc;
--查看哪个表被锁
select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id and b.OBJECT_NAME like '%TF_SM_TEST%';
--查看是哪个session引起的 b.username,b.sid,b.serial#,logon_time
select a.*,b.*
from v$locked_object a,v$session b
where a.session_id = b.sid and a.session_id in('5427','12066')
order by b.logon_time;
--查询表所占空间
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents where segment_name like 'TF_SM_TEST%' Group By Segment_Name;
---更改为压缩表
ALTER TABLE TF_SM_TEST move COMPRESS;
--解压
ALTER TABLE TF_SM_TEST move nocompress
--查询表是否是压缩
select a.COMPRESSION,a.* from USER_TABLES a where a.TABLESPACE_NAME='TBS_CL_TEST' and a.TABLE_NAME like 'TF_SM_TEST%';
--查询值显示一行,用单引号
SELECT TO_CHAR(WMSYS.WM_CONCAT(chr(39)||user_id||chr(39))) EPARCHY_CODES FROM (
SELECT h.user_id FROM TF_SM_TEST H
group by h.user_id);
-- DEL表后收缩表空间
analyze table TF_SM_TEST compute statistics;
select T.*,SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments T
where SEGMENT_NAME='TF_SM_TEST';
ANALYZE TABLE TF_SM_TEST compute statistics;
alter table TF_SM_TEST enable row movement;
ALTER TABLE TF_SM_TEST shrink space;
select t.BYTES,partition_name,SEGMENT_NAME,EXTENTS,BLOCKS from user_segments T where
T.partition_name='PAR_TF_SM_TEST_1'