【oracle】oracle常用sql

 

--查询表所有约束
select * from user_constraints c where c.table_name = upper('CHILD');

--查询所有引用该主键的外键
select b.table_name,b.column_name,a.constraint_name,a.constraint_type from user_constraints a inner join user_cons_columns b on a.constraint_name=b.constraint_name where a.r_constraint_name=upper('PK_CHIL_ID');

--清空oracle缓存
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

--被分析过的表：
select owner,table_name,last_analyzed from all_tables where last_analyzed is not null and owner='VACCTJ';
--没有被分析过的表：
select owner,table_name,last_analyzed from all_tables where last_analyzed is null and owner='VACCFS';

--添加字段
alter table TABLE_NAME add COLUMN_NAME varchar(10) DEFAULT 1; 
--删除字段
alter table TABLE_NAME drop column COLUMN_NAME; 
--更新字段名
alter table TABLE_NAME rename column column_old to column_new; 
--修改字段数据类型 
alter table tablename modify filedname varchar2(20); 

--查询表相关的所有索引
select index_name,index_type,table_name from user_indexes where table_name=UPPER('CHILD')
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name=UPPER('CHILD')


--分析表
analyze table table_name compute statistics for all indexes;
--重建索引
alter index index_name rebuild [subpartition subpartition_name | partition index_name]

--定时任务
declare fn number(10);
begin
  dbms_job.submit(
          fn,
          'analyzeTable;',
          to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'00:00:00'),'dd-mm-yyyy hh24:mi:ss'),				--sysdate
          'to_date(concat(to_char(sysdate+10,''dd-mm-yyyy''),''00:00:00''),''dd-mm-yyyy hh24:mi:ss'') '			--'sysdate+2/1440'
    );
  dbms_job.run(fn);
end;
/


--表分区
create table tab(
x int,
y varchar2(20),
z date
)
partition by range(x)
subpartition by list(y)
subpartition template
(
  SUBPARTITION SP0101  VALUES  ('0101'),
  SUBPARTITION SP0201  VALUES  ('0201'),
  SUBPARTITION SP0202  VALUES  ('0202')
)
(
      partition x1 values less than (0),
	  partition x2 values less than (100)
	 partition xd  values less than(maxvalue)
)

--索引分区
create index IX_CHILD_I14 on CHILD (CHIL_CURDEPARTMENT,CHIL_EDIT_DATE,chil_id) global
partition by hash (CHIL_CURDEPARTMENT)
partitions 20;


--dblink 
CREATE public   DATABASE LINK orcl_push_link 
CONNECT TO vaccah IDENTIFIED BY "123"
USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.15)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))';
/

---添加数据文件
 alter tablespace vacc  add datafile 'E:\ORADATA\ORCL\VACC1423.ORA' size 10000M autoextend on;
---增大数据文件
select * from dba_data_files d where d.tablespace_name=upper('vacc')
alter database    datafile 'd:oracledatamytbs01_2.dbf'    resize 4M
---修改数据文件为自动增长
alter database   datafile 'd:oracledatamytbs01_2.dbf'  autoextend on next 1M maxsize unlimited 

---查询oracle自动分配内存大小
 select component,current_size from v$sga_dynamic_components;

---启用用户跟踪文件
select sid,serial#,username,osuser,machine from v$session;
alter session set sql_trace=true;
dbms_session.set_sql_trace(true);

--启用其他用户session的跟踪文件（session id 和SERIAL#从session表中查询）
sys.dbms_system.set_sql_trace_in_session(10 , 39196 , true);

---获取当前会话的用户跟踪文件
select  
d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name  
from  
( select p.spid  
from sys.v$mystat m,sys.v$session s,sys.v$process p
where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,  
( select t.instance from sys.v$thread  t,sys.v$parameter  v  
where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
( select value from sys.v$parameter where name = 'user_dump_dest') d

--查询表空间使用率
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME