51CTO下载-Oracle_DB常用经典sql查询

oracle常典SQL查询
常SQL查询：
 
1查表空间名称
 
select ttablespace_name round(sum(bytes(1024*1024))0) ts_size
from dba_tablespaces t dba_data_files d
where ttablespace_name dtablespace_name
group by ttablespace_name
 
2查表空间物理文件名称
 
select tablespace_name file_id file_name
round(bytes(1024*1024)0) total_space
from dba_data_files
order by tablespace_name
 
3查回滚段名称
 
select segment_name tablespace_name rstatus
(initial_extent1024) InitialExtent(next_extent1024) NextExtent
max_extents vcurext CurExtent
From dba_rollback_segs r vrollstat v
Where rsegment_id vusn(+)
order by segment_name
 
4查控制文件
 
select name from vcontrolfile
 
5查日志文件
 
select member from vlogfile
 
6查表空间情况
 
select sum(bytes)(1024*1024) as free_spacetablespace_name
from dba_free_space
group by tablespace_name
 
SELECT ATABLESPACE_NAMEABYTES TOTALBBYTES USED CBYTES FREE
(BBYTES*100)ABYTES USED(CBYTES*100)ABYTES FREE
FROM SYSSMTS_AVAIL ASYSSMTS_USED BSYSSMTS_FREE C
WHERE ATABLESPACE_NAMEBTABLESPACE_NAME AND ATABLESPACE_NAMECTABLESPACE_NAME
 
7查数库库象
 
select owner object_type status count(*) count# from all_objects group by owner object_type status
 
8查数库版　
 
Select version FROM Product_component_version
Where SUBSTR(PRODUCT16)'Oracle'
 
9查数库创建日期档方式
 
Select Created Log_Mode Log_Mode From VDatabase
 
10捕捉运行久SQL
 
column username format a12
column opname format a16
column progress format a8
 
select usernamesidopname
      round(sofar*100 totalwork0) || '' as progress
      time_remainingsql_text
from vsession_longops vsql
where time_remaining <> 0
and sql_address address
and sql_hash_value hash_value

11查数表参数信息
SELECT   partition_name high_value high_value_length tablespace_name
        pct_free pct_used ini_trans max_trans initial_extent
        next_extent min_extent max_extent pct_increase FREELISTS
        freelist_groups LOGGING BUFFER_POOL num_rows blocks
        empty_blocks avg_space chain_cnt avg_row_len sample_size
        last_analyzed
   FROM dba_tab_partitions
  WHERE table_name tname AND table_owner towner
ORDER BY partition_position
 
12查没提交事务
select * from vlocked_object
select * from vtransaction
 
13查找object进程
select
pspid
ssid
sserial# serial_num
susername user_name
atype  object_type
sosuser os_user_name
aowner
aobject object_name
decode(sign(48 command)
1
to_char(command) 'Action Code #' || to_char(command) ) action
pprogram oracle_process
sterminal terminal
sprogram program
sstatus session_status  
from vsession s vaccess a vprocess p  
where spaddr paddr and
     stype 'USER' and   
     asid ssid   and
  aobject'SUBSCRIBER_ATTR'
order by susername sosuser
 
14回滚段查
select rownum sysdba_rollback_segssegment_name Name vrollstatextents
Extents vrollstatrssize Size_in_Bytes vrollstatxacts XActs
vrollstatgets Gets vrollstatwaits Waits vrollstatwrites Writes
sysdba_rollback_segsstatus status from vrollstat sysdba_rollback_segs
vrollname where vrollnamename(+) sysdba_rollback_segssegment_name and
vrollstatusn (+) vrollnameusn order by rownum
 
15耗资源进程（top session）
select sschemaname schema_name    decode(sign(48 command) 1
to_char(command) 'Action Code #' || to_char(command) ) action    status
session_status   sosuser os_user_name   ssid         pspid          sserial# serial_num  
nvl(susername '[Oracle process]') user_name   sterminal terminal   
sprogram program   stvalue criteria_value  from vsesstat st   vsession s  vprocess p  
where stsid ssid and   ststatistic# to_number('38') and   ('ALL' 'ALL'
or sstatus 'ALL') and paddr spaddr order by stvalue desc  pspid asc susername asc sosuser asc
 
16查锁（lock）情况
select *+ RULE * lsosuser os_user_name   lsusername user_name  
decode(lstype 'RW' 'Row wait enqueue lock' 'TM' 'DML enqueue lock' 'TX'
'Transaction enqueue lock' 'UL' 'User supplied lock') lock_type  
oobject_name object   decode(lslmode 1 null 2 'Row Share' 3
'Row Exclusive' 4 'Share' 5 'Share Row Exclusive' 6 'Exclusive' null)
lock_mode    oowner   lssid   lsserial# serial_num   lsid1   lsid2   
from sysdba_objects o (   select sosuser    susername    ltype    
llmode    ssid    sserial#    lid1    lid2   from vsession s    
vlock l   where ssid lsid ) ls  where oobject_id lsid1 and    oowner
<> 'SYS'   order by oowner oobject_name
 
17查等（wait）情况
SELECT vwaitstatclass vwaitstatcount count SUM(vsysstatvalue) sum_value
FROM vwaitstat vsysstat WHERE vsysstatname IN ('db block gets'
'consistent gets') group by vwaitstatclass vwaitstatcount
 
18查sga情况
SELECT NAME BYTES FROM SYSV_SGASTAT ORDER BY NAME ASC
 
19查catched object
SELECT owner              name              db_link              namespace 
           type              sharable_mem              loads              executions  
          locks              pins              kept        FROM vdb_object_cache
         
20查VSQLAREA
SELECT SQL_TEXT SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM SORTS
VERSION_COUNT LOADED_VERSIONS OPEN_VERSIONS USERS_OPENING EXECUTIONS
USERS_EXECUTING LOADS FIRST_LOAD_TIME INVALIDATIONS PARSE_CALLS DISK_READS
BUFFER_GETS ROWS_PROCESSED FROM VSQLAREA
 
21查object分类数量
select decode (otype#1'INDEX' 2'TABLE' 3 'CLUSTER' 4 'VIEW' 5
'SYNONYM' 6 'SEQUENCE' 'OTHER' ) object_type count(*) quantity from
sysobj o where otype# > 1 group by decode (otype#1'INDEX' 2'TABLE' 3
'CLUSTER' 4 'VIEW' 5 'SYNONYM' 6 'SEQUENCE' 'OTHER' ) union select
'COLUMN' count(*) from syscol union select 'DB LINK' count(*) from
 
22户查object种类
select uname schema   sum(decode(otype# 1 1 NULL)) indexes  
sum(decode(otype# 2 1 NULL)) tables   sum(decode(otype# 3 1 NULL))
clusters   sum(decode(otype# 4 1 NULL)) views   sum(decode(otype# 5 1
NULL)) synonyms   sum(decode(otype# 6 1 NULL)) sequences  
sum(decode(otype# 1 NULL 2 NULL 3 NULL 4 NULL 5 NULL 6 NULL 1))
others   from sysobj o sysuser u   where otype# > 1 and    uuser#
oowner# and   uname <> 'PUBLIC'   group by uname    order by
syslink union select 'CONSTRAINT' count(*) from syscon
 
23关connection相关信息
1）查户连接
select sosuser os_user_name    decode(sign(48 command) 1 to_char(command)
'Action Code #' || to_char(command) ) action     pprogram oracle_process    
status session_status    sterminal terminal    sprogram program   
susername user_name    sfixed_table_sequence activity_meter    '' query   
0 memory    0 max_memory     0 cpu_usage    ssid   sserial# serial_num   
from vsession s    vprocess p   where spaddrpaddr and    stype 'USER' 
order by susername sosuser
2）根vsid查应连接资源占等情况
select nname
vvalue
nclass
nstatistic# 
from  vstatname n
vsesstat v
where vsid 71 and
vstatistic# nstatistic#
order by nclass nstatistic#
3）根sid查应连接正运行sql
select *+ PUSH_SUBQ *
command_type
sql_text
sharable_mem
persistent_mem
runtime_mem
sorts
version_count
loaded_versions
open_versions
users_opening
executions
users_executing
loads
first_load_time
invalidations
parse_calls
disk_reads
buffer_gets
rows_processed
sysdate start_time
sysdate finish_time
'>' || address sql_address
'N' status
from vsqlarea
where address (select sql_address from vsession where sid 71)
 
24．查询表空间情况
select atablespace_name 表空间名称
100round((nvl(bbytes_free0)abytes_alloc)*1002) 占率()
round(abytes_alloc102410242) 容量(M)
round(nvl(bbytes_free0)102410242) 空闲(M)
round((abytes_allocnvl(bbytes_free0))102410242) (M)
Largest 扩展段(M)
to_char(sysdate'yyyymmdd hh24miss') 采样时间
from  (select ftablespace_name
   sum(fbytes) bytes_alloc
   sum(decode(fautoextensible'YES'fmaxbytes'NO'fbytes)) maxbytes
from dba_data_files f
group by tablespace_name) a
(select  ftablespace_name
    sum(fbytes) bytes_free
from dba_free_space f
group by tablespace_name) b
(select round(max(fflength)*1610242) Largest
   tsname tablespace_name
from sysfet ff sysfile tfsysts ts
where tsts#ffts# and fffile#tfrelfile# and tsts#tfts#
group by tsname tfblocks) c
where atablespace_name btablespace_name and atablespace_name ctablespace_name
 
25 查询表空间碎片程度
 
select tablespace_namecount(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10
 
alter tablespace name coalesce
alter table name deallocate unused
 
create or replace view ts_blocks_v as
select tablespace_nameblock_idbytesblocks'free space' segment_name from dba_free_space
union all
select tablespace_nameblock_idbytesblockssegment_name from dba_extents
 
select * from ts_blocks_v
 
select tablespace_namesum(bytes)max(bytes)count(block_id) from dba_free_space
group by tablespace_name
 
26查询数库实例运行
select inst_name from vactive_instances
 

######### 创建数库look ORACLE_HOMErdbmsadminbuildallsql #############
 
create database db01
maxlogfiles 10
maxdatafiles 1024
maxinstances 2
logfile
GROUP 1 ('u01oradatadb01log_01_db01rdo') SIZE 15M
GROUP 2 ('u01oradatadb01log_02_db01rdo') SIZE 15M
GROUP 3 ('u01oradatadb01log_03_db01rdo') SIZE 15M
datafile 'u01oradatadb01system_01_db01dbf') SIZE 100M
undo tablespace UNDO
datafile 'u01oradatadb01undo_01_db01dbf' SIZE 40M
default temporary tablespace TEMP
tempfile 'u01oradatadb01temp_01_db01dbf' SIZE 20M
extent management local uniform size 128k
character set AL32UTE8
national character set AL16UTF16
set time_zone'AmericaNew_York'
 
############### 数字典 ##########
 
set wrap off
 
select * from vdba_users
 
grant select on table_name to userrule
 
select * from user_tables
 
select * from all_tables
 
select * from dba_tables
 
revoke dba from user_name
 
shutdown immediate
 
startup nomount
 
select * from vinstance
 
select * from vsga
 
select * from vtablespace
 
alter session set nls_languageamerican
 
alter database mount
 
select * from vdatabase
 
alter database open
 
desc dictionary
 
select * from dict
 
desc vfixed_table
 
select * from vfixed_table
 
set oracle_sidfoxconn
 
select * from dba_objects
 
set serveroutput on
 
execute dbms_outputput_line('sfasd')
 
############# 控制文件 ###########
 
select * from vdatabase
 
select * from vtablespace
 
select * from vlogfile
 
select * from vlog
 
select * from vbackup
 
*备份户表空间*
alter tablespace users begin backup
 
select * from varchived_log
 
select * from vcontrolfile
 
alter system set control_files'ORACLE_HOMEoradatau01ctrl01ctl'
'ORACLE_HOMEoradatau01ctrl02ctl' scopespfile
 
cp ORACLE_HOMEoradatau01ctrl01ctl ORACLE_HOMEoradatau01ctrl02ctl
 
startup pfile'initSIDora'
 
select * from vparameter where name like 'control'
 
show parameter control
 
select * from vcontrolfile_record_section
 
select * from vtempfile
 
*备份控制文件*
alter database backup controlfile to 'filepathcontrolbak'
 
*备份控制文件二进制控制文件变asc 文文件*
alter database backup controlfile to trace
 
############### redo log ##############
 
archive log list
 
alter system archive log start启动动存档
 
alter system switch logfile强行进行次日志switch
 
alter system checkpoint强制进行次checkpoint
 
alter tablspace users begin backup
 
alter tablespace offline
 
*checkpoint 步频率参数FAST_START_MTTR_TARGET步频率越高系统恢复需时间越短*
show parameter fast
 
show parameter log_checkpoint
 
*加入日志组*
alter database add logfile group 3 ('ORACLE_HOMEoracleora_log_file6rdo' size 10M)
 
*加入日志组成员*
alter database add logfile member 'ORACLE_HOMEoracleora_log_file6rdo' to group 3
 
*删日志组前日志组删活动日志组删非档日志组删*
alter database drop logfile group 3
 
*删日志组中某成员组成员删*
alter databse drop logfile member 'ORACLE_HOMEoracleora_log_file6rdo'
 
*清线日志*
alter database clear logfile 'ORACLE_HOMEoracleora_log_file6rdo'
 
alter database clear logfile group 3
 
*清非档日志*
alter database clear unarchived logfile group 3
 
*重命名日志文件*
alter database rename file 'ORACLE_HOMEoracleora_log_file6rdo' to 'ORACLE_HOMEoracleora_log_file6ardo'
 
show parameter db_create
 
alter system set db_create_online_log_dest_1'path_name'
 
select * from vlog
 
select * from vlogfile
 
*数库档模式非档模式互换启动mount状态改变startup mount然开数库*
alter database noarchivelogarchivelog
 
achive log start启动动档
 
alter system archive all－－手工档日志文件
 
select * from varchived_log
 
show parameter log_archive
 
###### 分析日志文件logmnr ##############
 
1) initora中set utl_file_dir 参数
2) 重新启动oracle
3) create 目录文件
desc dbms_logmnr_d
dbms_logmnr_dbuild
4) 加入日志文件 addremove log file
dhms_logmnradd_logfile
dbms_logmnrremovefile
5) start logmnr
dbms_logmnrstart_logmnr
6) 分析出容查询 vlogmnr_content sqlredosqlundo
 
实践：
 
desc dbms_logmnr_d
 
*数表做操作恢复操作做准备*
update 表 set qty10 where stor_id6380
 
delete 表 where stor_id7066
***********************************
utl_file_dir路径
execute dbms_logmnr_dbuild('foxdictora''ORACLE_HOMEoracleadminfoxcdump')
 
execute dbms_logmnradd_logfile('ORACLE_HOMEoracleora_log_file6log'dbms_logmnrnewfile)
 
execute dbms_logmnrstart_logmnr(dictfilename>'ORACLE_HOMEoracleadminfoxcdumpfoxdictora')
 
######### tablespace ##############
 
select * form vtablespace
 
select * from vdatafile
 
*表空间数文件应关系*
select t1namet2name from vtablespace t1vdatafile t2 where t1ts#t2ts#
 
alter tablespace users add datafile 'path' size 10M
 
select * from dba_rollback_segs
 
*限制户某表空间限额*
alter user user_name quota 10m on tablespace_name
 
create tablespace xxx [datafile 'path_namedatafile_name'] [size xxx] [extent management localdictionary] [default storage(xxx)]
 
exmple create tablespace userdata datafile 'ORACLE_HOMEoradatauserdata01dbf' size 100M AUTOEXTEND ON NEXT 5M MAXSIZE 200M
create tablespace userdata datafile 'ORACLE_HOMEoradatauserdata01dbf' size 100M extent management dictionary default storage(initial 100k next 100k pctincrease 10) offline
*9ioracle建议local理dictionary理local采bitmap理表空间 会产生系统表空间愿争*
create tablespace userdata datafile 'ORACLE_HOMEoradatauserdata01dbf' size 100M extent management local uniform size 1m
create tablespace userdata datafile 'ORACLE_HOMEoradatauserdata01dbf' size 100M extent management local autoallocate
*创建表空间时设置表空间段空间理模式里动理*
create tablespace userdata datafile 'ORACLE_HOMEoradatauserdata01dbf' size 100M extent management local uniform size 1m segment space management auto
 
alter tablespace userdata mininum extent 10
 
alter tablespace userdata default storage(initial 1m next 1m pctincrease 20)
 
*undo tablespace(字典理模) *
create undo tablespace undo1 datafile 'ORACLE_HOMEoradataundo101dbf' size 40M extent management local
 
show parameter undo
 
*temporary tablespace*
create temporary tablespace userdata tempfile 'ORACLE_HOMEoradataundo101dbf' size 10m extent management local
 
*设置数库缺省时表空间*
alter database default temporary tablespace tablespace_name
 
*系统时线undo表空间offline*
alter tablespace tablespace_name offlineonline
 
alter tablespace tablespace_name read only
 
*重命名户表空间*
alter tablespace tablespace_name rename datafile 'ORACLE_HOMEoradataundo101dbf' to 'ORACLE_HOMEoradataundo102dbf'
 
*重命名系统表空间 重命名前必须数库shutdown重启mount状态*
alter database rename file 'ORACLE_HOMEoradatasystem01dbf' to 'ORACLE_HOMEoradatasystem02dbf'
 
drop tablespace userdata including contents and datafilesdrop tablespce
 
*resize tablespaceautoextend datafile space*
alter database datafile 'ORACLE_HOMEoradataundo102dbf' autoextend on next 10m maxsize 500M
 
*resize datafile*
alter database datafile 'ORACLE_HOMEoradataundo102dbf' resize 50m
 
*表空间扩展空间*
alter tablespace userdata add datafile 'ORACLE_HOMEoradataundo102dbf' size 10m
 
*表空间设置成OMF状态*
alter system set db_create_file_dest'ORACLE_HOMEoradata'
 
create tablespace userdatause OMF status to create tablespace
 
drop tablespace userdatauser OMF status to drop tablespace
 
select * from dba_tablespacevtablespacedba_data_files
 
*表某分区移动表空间*
alter table table_name move partition partition_name tablespace tablespace_name
 
###### ORACLE storage structure and relationships #########
 
*手工分配表空间段分区(extend)*
alter table kongtest12 allocate extent(size 1m datafile 'ORACLE_HOMEoradataundo102dbf')
 
alter table kongtest12 deallocate unused 释放表中没分区
 
show parameter db
 
alter system set db_8k_cache_size10m 配置8k块存空间块参数
 
select * from dba_extentsdba_segmentsdata_tablespace
 
select * from dba_free_spacedba_data_filedata_tablespace
 
*数象占字节数*
select sum(bytes) from dba_extents where onwer'kong' and segment_name 'table_name'
 
############ UNDO Data ################
 
show parameter undo
 
alter tablespace users offline normal
 
alter tablespace users offline immediate
 
recover datafile 'ORACLE_HOMEoradataundo102dbf'
 
alter tablespace users online
 
select * from dba_rollback_segs
 
alter system set undo_tablespaceundotbs1
 
*忽略回滚段错误提示*
alter system set undo_suppress_errorstrue
 
*动理模式会真正建立rbs1手工理模式建立私回滚段*
create rollback segment rbs1 tablespace undotbs
 
desc dbms_flashback
 
*提交修改数9i提供旧数回闪操作修改前数读户部分数会恢复表中旧数映射*
execute dbms_flashbackenable_at_time('26JAN04121700 pm')
 
execute dbms_flashbackdisable
 
*回滚段统计信息*
select end_timebegin_timeundoblks from vundostat
 
*undo表空间计算公式 UndoSpace[UR * (UPS * DBS)] + (DBS * 24)
UR UNDO_RETENTION 保留时间(秒)
UPS 秒回滚数块
DBS系统EXTENTFILE SIZE(db_block_size)*
 
select * from dba_rollback_segsvrollnamevrollstatvundostatvsessionvtransaction
 
show parameter transactions
 
show parameter rollback
 
*手工理模式建立公回滚段*
create public rollback segment prbs1 tablespace undotbs
 
alter rollback segment rbs1 online手工理模式
 
*手工理模式中initSIDora中指定 undo_managementmanual rollback_segment('rbs1''rbs2')
transactions100 transactions_per_rollback_segment10
然 shutdown immediate startup pfile\ora *
 
########## Managing Tables ###########
 
*char type maxlen2000varchar2 type maxlen4000 bytes
rowid 18位64进制字符串 (10bytes 80 bits)
rowid组成 object#(象号)32bits6位
rfile#(相文件号)10bits3位
block#(块号)22bits6位
row#(行号)16bits3位
64进制 AZaz09+ 64符号
 
dbms_rowid 包中函数提供rowid解释*
 
select rowiddbms_rowidrowid_block_number(rowid)dbms_rowidrowid_row_number(rowid) from table_name
 
create table test2
(
id int
lname varchar2(20) not null
fname varchar2(20) constraint ck_1 check(fname like 'k')
empdate date default sysdate)
) tablespace tablespace_name
 
 
create global temporary table test2 on commit deletepreserve rows as select * from kongauthors
 
create table usertable() tablespace tablespace_name storage() pctfree10 pctused 40
 
alter table usertablename pctfree 20 pctused 50 storage()changing table storage
 
*手工分配分区分配数文件必须表表空间数文件*
alter table usertable_name allocate extent(size 500k datafile '')
 
*释放表中没空间*
alter table table_name deallocate unused
 
alter table table_name deallocate unused keep 8k
 
*非分区表表空间搬新表空间移动表空间原表中索引象会必须重建*
alter table usertable_name move tablespace new_tablespace_name
 
create index index_name on usertable_name(column_name) tablespace users
 
alter index index_name rebuild
 
drop table table_name [CASCADE CONSTRAINTS]
 
alter table usertable_name drop column col_name [CASCADE CONSTRAINTS CHECKPOINT 1000]drop column
 
*表中列做标记*
alter table usertable_name set unused column comments CASCADE CONSTRAINTS
 
*drop表中做标记列*
alter table usertable_name drop unused columns checkpoint 1000
 
*drop col出现异常CONTINUE防止重删前面column*
ALTER TABLE USERTABLE_NAME DROP COLUMNS CONTINUE CHECKPOINT 1000
 
select * from dba_tablesdba_objects
 
######## managing indexes ##########
 
*create index*
example
*创建般索引*
create index index_name on table_name(column_name) tablespace tablespace_name
*创建位图索引*
create bitmap index index_name on table_name(column_name1column_name2) tablespace tablespace_name
*索引中pctused*
create [bitmap] index index_name on table_name(column_name) tablespace tablespace_name pctfree 20 storage(inital 100k next 100k)
*数量索引做日志*
create [bitmap] index index_name table_name(column_name1column_name2) tablespace_name pctfree 20 storage(inital 100k next 100k) nologging
*创建反转索引*
create index index_name on table_name(column_name) reverse
*创建函数索引*
create index index_name on table_name(function_name(column_name)) tablespace tablespace_name
*建表时创建约束条件*
create table usertable_name(column_name number(7) constraint constraint_name primary key deferrable using index storage(initial 100k next 100k) tablespace tablespace_namecolumn_name2 varchar2(25) constraint constraint_name not nullcolumn_name3 number(7)) tablespace tablespace_name
 
*创建bitmap index分配存空间参数加速建索引*
show parameter create_bit
 
*改变索引存储参数*
alter index index_name pctfree 30 storage(initial 200k next 200k)
 
*索引手工分配分区*
alter index index_name allocate extent (size 200k datafile 'ORACLEoradata')
 
*释放索引中没空间*
alter index index_name deallocate unused
 
*索引重建*
alter index index_name rebuild tablespace tablespace_name
 
*普通索引反转索引互换*
alter index index_name rebuild tablespace tablespace_name reverse
 
*重建索引时锁表*
alter index index_name rebuild online
 
*索引整理碎片*
alter index index_name COALESCE
 
*分析索引事实更新统计程*
analyze index index_name validate structure
 
desc index_state
 
drop index index_name
 
alter index index_name monitoring usage监视索引否
 
alter index index_name nomonitoring usage取消监视
 
*关索引信息视图*
select * from dba_indexesdba_ind_columnsdbs_ind_expressionsvobject_usage
 
########## 数完整性理(Maintaining data integrity) ##########
 
alter table table_name drop constraint constraint_namedrop 约束
 
alter table table_name add constraint constraint_name primary key(column_name1column_name2)创建键
 
alter table table_name add constraint constraint_name unique(column_name1column_name2)创建唯约束
 
*创建外键约束*
alter table table_name add constraint constraint_name foreign key(column_name1) references table_name(column_name1)
 
*效验老数约束新数[enabledisable：约束约束新数novalidatevalidate老数进行验证]*
alter table table_name add constraint constraint_name check(column_name like 'B') enabledisable novalidatevalidate
 
*修改约束条件延时验证commit时验证*
alter table table_name modify constraint constraint_name initially deferred
 
*修改约束条件立验证*
alter table table_name modify constraint constraint_name initially immediate
 
alter session set constraintsdeferredimmediate
 
*drop外键键表带cascade constraints参数级联删*
drop table table_name cascade constraints
 
*truncate外键表时先外键设效truncate*
truncate table table_name
 
*设约束条件效*
alter table table_name disable constraint constraint_name
 
alter table table_name enable novalidate constraint constraint_name
 
*效约束数行放入exception表中表记录违反数约束行行号前先建exceptions表*
alter table table_name add constraint constraint_name check(column_name >15) enable validate exceptions into exceptions
 
*运行创建exceptions表脚*
start ORACLE_HOMErdbmsadminutlexcptsql
 
*获取约束条件信息表视图*
select * from user_constraintsdba_constraintsdba_cons_columns
 
################## managing password security and resources ####################
 
alter user user_name account unlockopen锁定开户
 
alter user user_name password expire设定口令期
 
*建立口令配置文件failed_login_attempts口令输少次锁password_lock_times指少天口令动解锁*
create profile profile_name limit failed_login_attempts 3 password_lock_times 11440
*创建口令配置文件*
create profile profile_name limit failed_login_attempts 3 password_lock_time unlimited password_life_time 30 password_reuse_time 30 password_verify_function verify_function password_grace_time 5
*建立资源配置文件*
create profile prfile_name limit session_per_user 2 cpu_per_session 10000 idle_time 60 connect_time 480
 
alter user user_name profile profile_name
 
*设置口令解锁时间*
alter profile profile_name limit password_lock_time 124
 
*password_life_time指口令文件少时间期password_grace_time指第次成功登录口令期少天时间改变口令*
alter profile profile_name limit password_lift_time 2 password_grace_time 3
 
*password_reuse_time指口令少天重password_reuse_max口令重次数*
alter profile profile_name limit password_reuse_time 10[password_reuse_max 3]
 
alter user user_name identified by input_password修改户口令
 
drop profile profile_name
 
*建立profile指定某户必须CASCADE删*
drop profile profile_name CASCADE
 
alter system set resource_limittrue启愿限制缺省false
 
*配置资源参数*
alter profile profile_name limit cpu_per_session 10000 connect_time 60 idle_time 5
*资源参数(session级)
cpu_per_session session占cpu时间 单位1100秒
sessions_per_user 允许户行session数
connect_time 允许连接时间 单位分钟
idle_time 连接空闲少时间动断开 单位分钟
logical_reads_per_session 读块数
private_sga 户够SGA中私空间数 单位bytes
 
(call级)
cpu_per_call 次(1100秒)调cpu时间
logical_reads_per_call 次调够读块数
*
 
alter profile profile_name limit cpu_per_call 1000 logical_reads_per_call 10
 
desc dbms_resouce_manager资源理器包
 
*获取资源信息表视图*
select * from dba_usersdba_profiles
 
###### Managing users ############
 
show parameter os
 
create user testuser1 identified by kxf_001
 
grant connectcreatetable to testuser1
 
alter user testuser1 quota 10m on tablespace_name
 
*创建户*
create user user_name identified by password default tablespace tablespace_name temporary tablespace tablespace_name quota 15m on tablespace_name password expire
 
*数库级设定缺省时表空间*
alter database default temporary tablespace tablespace_name
 
*制定数库级缺省表空间*
alter database default tablespace tablespace_name
 
*创建os级审核户需知道os_authent_prefix表示oracleos口令应前缀'OPS'参数值值意设置*
create user user_name identified by externally default OPStablespace_name tablespace_name temporary tablespace tablespace_name quota 15m on tablespace_name password expire
 
*修改户表空间限额回滚表空间时表空间允许授予限额*
alter user user_name quota 5m on tablespace_name
 
*删户删级联户(户象象CASCADE象起删)*
drop user user_name [CASCADE]
 
*户表空间什限额*
desc dba_ts_quotasselect * from dba_ts_quotas where username''
 
*改变户缺省表空间*
alter user user_name default tablespace tablespace_name
 
######### Managing Privileges #############
 
grant create tablecreate session to user_name
 
grant create any table to user_name revoke create any table from user_name
 
*授予权限语法public 标识户with admin option允许权限授予第三者权限*
grant system_privs[] to [userrolepublic][] [with admin option]
 
select * from vpwfile_users
 
* O7_dictionary_accessiblity参数True时标识select any table时包括系统表select 否包含系统表缺省false*
show parameter O7
 
* O7_dictionary_accessiblity静态参数动态改变加scopespfile次启动时生效*
alter system set O7_dictionary_accessiblitytrue scopespfile
 
*授予象中某字段权限select 某表中某字段权限*
grant [object_privs(column)][] on object_name to userrolepublic with grant option
 
*oracle允许授予select某列权限授insert update某列权限*
grant insert(column_name1column_name2) on table_name to user_name with grant option
 
select * from dba_sys_privssession_privsdba_tab_privsuser_tab_privsdba_col_privsuser_col_privs
 
*dbosnone 审计记录 数库操作系统审计 缺省none*
show parameter audit_trail
 
*启动表select动作*
audit select on usertable_name by session
 
*by sessionsession中发出command记录次by accesscommand记录*
audit [create table][selectupdateinsert on object by sessionaccess][whenever successfulnot successful]
 
desc dbms_fga进步设计dbms_fgs包
 
*取消审计*
noaudit select on usertable_name
 
*查审计信息*
select * from all_def_audit_optsdba_stmt_audit_optsdba_priv_audit_optsdba_obj_audit_opts
 
*获取审计记录*
select * from dba_audit_traildba_audit_existsdba_audit_objectdba_audit_sessiondba_audit_statement
 
########### Managing Role #################
 
create role role_name grant select on table_name to role_name grant role_name to user_name set role role_name
 
create role role_name
create role role_name identified by password
create role role_name identified externally
 
set role role_name 激活role
set role role_name identified by password
 
alter role role_name not identified
alter role role_name identified by password
alter role role_name identified externally
 
grant priv_name to role_name [WITH ADMIN OPTION]
grant update(column_name1col_name2) on table_name to role_name
grant role_name1 to role_name2
 
*建立default role户登录时缺省激活default role*
alter user user_name default role role_name1role_name2
alter user user_name default role all
alter user user_name default role all except role_name1
alter user user_name default role none
 
set role role1 [identified by password]role2
set role all
set role except role1role2
set role none
 
revoke role_name from user_name
revoke role_name from public
 
drop role role_name
 
select * from dba_rolesdba_role_privsrole_role_privsdba_sys_privsrole_sys_privsrole_tab_privssession_roles
 
########### Basic SQL SELECT ################
 
select col_name as col_alias from table_name
 
select col_name from table_name where col1 like '_o' '_'匹配单字符
 
*字符函数(右边截取字段中包含某字符左边填充某字符固定位数右边填充某字符固定位数)*
select substr(col135)instr(col2'g')LPAD(col310'')RPAD(col410'') from table_name
 
*数字函数(右左位四舍五入取整取余)*
select round(col12)trunc(col2)mod(col3) from table_name
 
*日期函数(计算两日期间相差星期两日期间相隔月某月份加月某日期日期
某日期月日期某日期月分四舍五入某日期月份进行取整)*
select (sysdatecol1)7 weekmonths_between(sysdatecol1)add_months(col12)next_day(sysdate'FRIDAY')last_day(sysdate)
round(sysdate'MONTH')trunc(sysdate'MONTH') from table_name
 
*NULL函数(expr1空取expr2expr1空取expr2否取expr3expr1expr2返回空)*
select nvl(expr1expr2)nvl2(expr1expr2expr3)nullif(expr1expr2) from table_name
 
select column1column2column3 case column2 when '50' then column2*11
when '30' then column2*21
when '10' then column320
else column3
end as ttt
from table_name case函数
 
select table1col1table2col2 from table1
[CROSS JOIN table2] | 笛卡连接
[NATURAL JOIN table2] | 两表中名列连接
[JOIN table2 USING (column_name)] | 两表中名列中某列列连接
[JOIN table2
ON (table1col1table2col2)] |
[LEFT|RIGHT|FULL OUTER JOIN table2 相(+)(+)连接全外连接
ON (table1col1table2col2)] SQL 1999中JOIN语法
 
example
select col1col2 from table1 t1
join table2 t2
on t1col1t2col2 and t1col3t2col1
join table3 t3
on t2col1t3col3
 
select * from table_name where col1 < any (select col2 from table_name2 where continue group by col3)
 
select * from table_name where col1 < all (select col2 from table_name2 where continue group by col3)
 
insert into (select col1col2col3 form table_name where col1> 50 with check option) values (value1value2value3)
 
MERGE INTO table_name table1
USING table_name2 table2
ON (table1col1table2col2)
WHEN MATCHED THEN
UPDATE SET
table1col1table2col2
table1col2table2col3

WHEN NOT MATCHED THEN
INSERT VALUES(table2col1table2col2table2col3) 合语句
 
##################### CREATEALTER TABLE #######################
 
alter table table_name drop column column_name drop column
 
alter table table_name set unused (col1col2)设置列效较快
alter table table_name drop unused columns删设效列
 
rename table_name1 to table_name2 重命名表
 
comment on table table_name is 'comment message'表放入注释信息
 
create table table_name
(col1 int not nullcol2 varchar2(20)col3 varchar2(20)
constraint uk_test2_1 unique(col2col3))) 定义表中约束条件
 
alter table table_name add constraint pk_test2 primary key(col1col2) 创建键
 
*建立外键*
create table table_name (rid intname varchar2(20)constraint fk_test3 foreign key(rid) references other_table_name(id))
 
alter table table_name add constraint ck_test3 check(name like 'K')
 
alter table table_name drop constraint constraint_name
 
alter table table_name drop primary key cascade级联删键
 
alter table table_name disableenable constraint constraint_name约束暂时效
 
*删列级联删列约束条件*
alter table table_name drop column column_name cascade constraint
 
select * from user_constraintsuser_cons_columns约束条件相关视图
 
############## Create Views #####################
 
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[alias])]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY [CONSTRAINT constraint_name]] 创建视图语法
 
example Create or replace view testview as select col1col2col3 from table_name 创建视图
*名*
Create or replace view testview as select col1sum(col2) col2_alias from table_name
*创建复杂视图*
Create view view_name (alias1alias2alias3alias4) as select dcol1min(ecol1)max(ecol1)avg(ecol1) from table_name1 etable_name2 d where ecol2dcol2 group by dcol1
*update修改数时必须满足视图col1>10条件满足改变*
Create or replace view view_name as select * from table_name where col1>10 with check option
 
*改变视图值简单视图update语法修改表数复杂视图定改函数group by distinct等列*
update view_name set col1value1
 
*TOPN分析*
select [column_list]rownum from (select [column_list] from table_name order by TopN_column) where rownum 
*找出某列三条值记录*
example select rownum as rank col1 col2 from (select col1 col2 from table_name order by col2 desc) where rownum<3
 
############# Other database Object ###############
 
CREATE SEQUENCE sequence_name [INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCEL | NOCYCLE}]
[{CACHE n | NOCACHE}] 创建SEQUENCE
 
example
CREATE SEQUENCE sequence_name INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE
 
select * from user_sequences 前户记录sequence视图
 
select sequence_namenextvalsequence_namecurrval from dualsequence引
 
alter sequence sequence_name INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE 修改sequence改变起始序号
 
drop sequence sequence_name 删sequence
 
CREATE [PUBLIC] SYNONYM synonym_name FOR object 创建义词
 
DROP [PUBLIC] SYNONYM synonym_name删义词
 
CREATE PUBLIC DATABASE LINK link_name USEING OBJECT创建DBLINK
 
select * from object_name@link_name 访问远程数库中象
 
*union 操作两集合交集部分压缩数排序*
select col1col2col3 from table1_name union select col1col2col3 from table2_name
 
*union all 操作两集合交集部分压缩数排序*
select col1col2col3 from table1_name union all select col1col2col3 from table2_name
 
*intersect 操作求两集合交集重复数进行压缩排序*
select col1col2col3 from table1_name intersect select col1col2col3 from table2_name
 
*minus 操作集合减压缩两集合减重复记录 数排序*
select col1col2col3 from table1_name minus select col1col2col3 from table2_name
 
*EXTRACT 抽取时间函数 例抽取前日期中年*
select EXTRACT(YEAR FROM SYSDATE) from dual
*EXTRACT 抽取时间函数 例抽取前日期中月*
select EXTRACT(MONTH FROM SYSDATE) from dual
 
########################## 增强 group by 子句 #########################
 
select [column] group_function(column)
from table
[WHERE condition]
[GROUP BY [ROLLUP] group_by_expression]
[HAVING having_expression]
[ORDER BY column] ROLLUP操作字group by子句字段右左进行聚合
 
example
*结果起象col1做计*
select col1col2sum(col3) from table group by rollup(col1col2)
*复合rollup表达式*
select col1col2sum(col3) from table group by rollup((col1col2))
 
select [column] group_function(column)
from table
[WHERE condition]
[GROUP BY [CUBE] group_by_expression]
[HAVING having_expression]
[ORDER BY column] CUBE操作字完成ROLLUP功外ROLLUP结果集右左聚合
 
example
*结果起象col1做计col2做计算总计*
select col1col2sum(col3) from table group by cube(col1col2)
*复合rollup表达式*
select col1col2sum(col3) from table group by cube((col1col2))
*混合rollupcube表达式*
select col1col2col3sum(col4) from table group by col1rollup(col2)cube(col3)
 
*GROUPING(expr)函数查select语句种字段聚合取值01*
select [column] group_function(column)GROUPING(expr)
from table
[WHERE condition]
[GROUP BY [ROLLUP] group_by_expression]
[HAVING having_expression]
[ORDER BY column]
 
example
select col1col2sum(col3)grouping(col1)grouping(col2) from table group by cube(col1col2)
 
*grouping sets操作group by结果集先col1求col2求结果集起*
select col1col2sum(col3) from table group by grouping sets((col1)(col2))
 

文档香网(httpswwwxiangdangnet)户传

《香当网》用户分享的内容，不代表《香当网》观点或立场，请自行判断内容的真实性和可靠性！
该内容是文档的文本内容，更好的格式请下载文档