原创oracle 体系架构和管理用SQL用SQL管理ORACLE数据库1222V15.docx
《原创oracle 体系架构和管理用SQL用SQL管理ORACLE数据库1222V15.docx》由会员分享，可在线阅读，更多相关《原创oracle 体系架构和管理用SQL用SQL管理ORACLE数据库1222V15.docx（52页珍藏版）》请在冰豆网上搜索。
原创oracle体系架构和管理用SQL用SQL管理ORACLE数据库1222V15
用SQL语句管理Oracle数据库
目录
1表空间管理5
1.1表空间生命周期管理5
1.1.1创建表空间5
1.1.2将表空间分配给用户5
1.1.3修改表空间相关信息6
1.1.4删除用户和表空间6
1.1.5查询表空间信息7
1.2撤销表空间关联7
1.2.1了解undo表空间参数配置7
1.2.2撤销表空间（undo）信息查询7
1.2.3切换撤销表空间7
1.2.4删除撤销表空间8
1.3表空间信息查询8
1.3.1统计表空间信息和文件的关系8
1.3.2表空间利用率统计8
1.3.3表空间空闲空间8
1.3.4查看表空间的使用者9
2表管理9
2.1表所占用空间信息9
2.1.1查询表所占空间9
2.1.2统计表的区间数和表容量9
2.1.3统计记录平均长度9
2.2表结构信息10
2.2.1表结构10
2.2.2查看表的简单信息10
2.2.3查看表所在表空间10
2.2.4查看所有表的信息，包括注释10
2.2.5查看表所有字段的详细信息10
2.3索引管理11
2.4分区管理实例11
2.4.1创建分区11
2.4.2查询表分区信息13
2.4.3索引分区信息13
2.4.4查询分区表内容13
2.4.5复合分区13
2.5记录管理14
2.5.1行连接、行迁移及其消除14
2.6oracle 锁表查询16
2.6.1查看被锁的表16
2.6.2查看连接的进程17
2.6.3杀掉进程17
2.7表误删除的闪回操作：
17
2.7.1闪回到删除前17
2.7.2闪回到指定时间17
2.7.3相关的回收站信息17
2.8表在表空间之间移动17
3性能管理18
3.1访问数据高速缓冲区的命中率18
3.2查看当前执行的sql语句18
3.3等待事件管理18
3.3.2跟踪当前系统等待的进程，等待类型的语句18
3.4使用V$SQL_PLAN查看全表扫描的语句19
3.5查询保存在内存中的SQL语句20
3.6系统参数和文件的管理20
3.7统计SGA的信息20
3.8配置SGA参数优化：
预编译与共享池21
3.8.1步骤1：
创建表23
3.8.2步骤2：
执行3种不同的sql23
3.8.3清洗共享池24
3.8.4查询一下共享池内部做此操作的记录24
3.8.5执行代码段1，并查询共享池24
3.9统计数据缓冲区的使用情况25
3.9.1找到热数据块26
3.9.2完全缓冲数据库26
3.10共享池操作27
4SQL语句优化相关知识27
4.1查看长时间执行的SQL语句27
4.1.1命令行：
查看参数：
27
4.1.2命令行：
修改参数：
28
4.1.3命令行：
收集统计信息28
4.1.4执行sql语句：
28
4.2怎样估算SQL执行的I/O数28
4.3索引管理29
4.3.1查询指定表的索引所占空间29
4.3.2创建索引29
4.3.3删除索引29
4.4Insertinto优化29
4.4.1优化方法1：
删除索引，插入，重建索引29
4.4.2优化方法2：
29
4.5其他优化方法29
5数据库管理30
5.1和其他数据库连接30
6用户管理31
6.1创建用户31
6.2用户权限管理31
6.2.1授权31
6.2.2修改密码32
6.2.3操作：
收回系统权限32
6.2.4操作：
赋予对象权限32
6.2.5操作：
收回对象权限32
6.3角色管理32
6.3.1创建角色32
6.3.2将角色赋给用户32
6.3.3复杂角色管理：
角色嵌套32
6.3.4操作：
角色的改变33
6.3.5操作：
角色权限查询33
6.3.6给角色赋权限33
6.3.7删除角色33
6.4查询用户33
6.4.1问题排查33
6.5删除用户34
6.6锁定和解锁用户34
7会话管理34
7.1查询长时间执行的会话34
7.2通过sql_trace和tkprof获知某session执行的sql语句34
8普通SQL语句34
8.1日期类型操作34
8.1.1生成一段连续日期34
8.2Rownum使用技巧35
8.2.1复杂rownum使用技巧35
8.2.2一个范例37
8.3分支语句（case）41
8.4统计分析用sql41
8.4.1分析函数应用实例41
9还原和备份42
9.1导入工具imp42
9.1.1imp步骤：
42
9.1.2导入工具imp非交互式命令行方式的例子42
9.2导出工具exp42
10Oracle安装44
10.1在windows7环境下的安装44
11oracle中LAG（）和LEAD（）等分析统计函数的用法44
表空间管理
表空间生命周期管理
创建表空间
简单表空间创建
createtablespacetestspacedatafile'c:
\testspace.dbf'size50m
autoextendonnext10mmaxsizeunlimited;
创可扩展的表空间
2013/1/16：
注意：
表空间上的表每次扩展128K
createtablespacetp1datafile'c:
\tpq'size1M
EXTENTMANAGEMENTLOCALUNIFORMSIZE128K;
创建2个文件的表空间
--创建表空间，含2个数据文件；
createtablespacetp2datafile'c:
\tp21.dbf'size1M,'c:
\tp22.dbf'SIZE2m；
select*fromdba_data_files
创建撤销表空间
createundotablespacemyundodatafile'c:
\myundo.dbf'size30M
autoextendon;
将表空间分配给用户
alteruserylresuserquotaunlimitedonylres;
修改表空间相关信息
查询默认表空间
--查询默认表空间：
SELECTUSERNAME,DEFAULT_TABLESPACEFROMDBA_USERSwhereusername='用户名（大写）';
修改默认表空间
alteruserMYUSER（用户名）defaulttablespaceTP1;
修改系统默认表空间
alterdatabasedefaulttablespacetp1;
修改系统表空间，使之自动扩展
altertablespacetp1
ADDDATAFILE'C:
\TP11'SIZE1m
autoextendonnext512K;
修改表空间大小
alterdatabasedatafile'c:
\myundo.dbf'resize10M;
增加用户在指定表空间的资源：
Alterusertestuserquota5Mon表空间名
删除用户和表空间
dropuserylresusercascade;
droptablespaceylresincludingcontentsanddatafiles;
查询表空间信息
查看用户可以使用的表空间和默认表空间
select*fromuser_tablespaces;查看用户可以使用的表空间；
查询默认表空间：
SELECTUSERNAME,DEFAULT_TABLESPACEFROMDBA_USERSwhereusername='用户名（大写）';
查询指定表空间在不同用户间的限额分配情况
selectD.tablespace_name,D.username,D.bytes/1024,D.max_bytes/1024/1024,D.blocks,D.max_blocksfromdba_ts_quotasD
select*fromdba_ts_quotas
撤销表空间关联
了解undo表空间参数配置
showparameterundo
撤销表空间（undo）信息查询
--2.5撤销表空间使用信息查询
selectto_char（min（begin_time）,'dd-mon-yyyyhh24:
mi:
ss'）"开始时间",
to_char（min（end_time）,'dd-mon-yyyyhh24:
mi:
ss'）"结束时间",
sum（undoblks）"使用undo总块",
sum（txncount）"事务执行总数",
max（maxquerylen）as"最长查询（秒）",
max（maxconcurrency）"最高并发数",
sum（Ssolderrcnt）"出错1555总数",
sum（nospaceerrcnt）"无可用空间总数"
fromv$undostat;
切换撤销表空间
altersystemsetundo_tablespace='MYUNDO'
删除撤销表空间
droptablespacemyundoincludingcontentsanddatafiles;
表空间信息查询
统计表空间信息和文件的关系
select*fromsys.dba_data_filest
表空间利用率统计
SELECTUPPER（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（SELECTTABLESPACE_NAME,
ROUND（SUM（BYTES）/（1024*1024）,2）TOTAL_BYTES,
ROUND（MAX（BYTES）/（1024*1024）,2）MAX_BYTES
FROMSYS.DBA_FREE_SPACE
GROUPBYTABLESPACE_NAME）F,
（SELECTDD.TABLESPACE_NAME,
ROUND（SUM（DD.BYTES）/（1024*1024）,2）TOT_GROOTTE_MB
FROMSYS.DBA_DATA_FILESDD
GROUPBYDD.TABLESPACE_NAME）D
WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME
ORDERBYF.TABLESPACE_NAME;
表空间空闲空间
SELECT*FROMDBA_FREE_SPACEF
WHEREF.TABLESPACE_NAME='TP1';
查看表空间的使用者
如何知道一个表空间的空间都被谁占用？
select*fromdba_segmentsd
whered.tablespace_name='TP1';
selectd.tablespace_name,sum（bytes/1024）K,SUM（BYTES/1024/1024）Mfromdba_segmentsd
whered.tablespace_name='TP1'
groupbyd.tablespace_name;
表管理
表所占用空间信息
查询表所占空间
selecta.file_name,b.extent_id,b.bytes/1024/1024fromdba_data_filesa,dba_extentsbwherea.file_id=b.file_idandsegment_name='DW_TRANS_FACT_HOUR';
selectsum（b.bytes/1024/1024）,b.segment_type,b.segment_name
fromdba_data_filesa,dba_extentsb
wherea.file_id=b.file_idandfile_name='C:
\TESTSPACE.DBF'
groupbyb.segment_name,b.segment_type
orderbysum（b.bytes/1024/1024）desc
SELECTA.FILE_NAME,B.EXTENT_ID,B.BYTES/1024/1024
FROMDBA_DATA_FILESA,DBA_EXTENTSB
WHEREA.FILE_ID=B.FILE_ID
ANDSEGMENT_NAME='Z_BASE_FINTRANS1';
统计表的区间数和表容量
SELECTCOUNT（EXTENT_ID）,SUM（BYTES/1024/1024）,SUM（BLOCKS）FROMDBA_EXTENTSWHERESEGMENT_NAME='MCHNT';
统计记录平均长度
selectavg_row_len,U.MAX_TRANS,U.MAX_EXTENTS,U.MIN_EXTENTSfromuser_tablesUwheretable_name='DW_TRANS_FACT_HOUR';
SELECTCOUNT（*）FROMDW_TRANS_FACT_HOUR;
表结构信息
表结构
Descaaaa（表名）
查看表的简单信息
select*fromtab;
查看表所在表空间
--这个语句可以查看刚才创建的表放在那个表空间中，结果是方在默认的表空间，而不是刚才创建的表空间
select*fromuser_tables;
查看所有表的信息，包括注释
SELECTa.table_name,mentsFROMuser_tab_commentsa,user_tablesb
WHEREa.table_name=b.table_name
查看表所有字段的详细信息
SELECTUSER_TAB_COLS.TABLE_NAMEas表名,
USER_TAB_COLS.COLUMN_NAMEas列名,
USER_TAB_COLS.DATA_TYPEas数据类型,
USER_TAB_COLS.DATA_LENGTHas长度,
USER_TAB_COLS.NULLABLEas是否为空,
USER_TAB_COLS.COLUMN_IDas列序号,
user_col_mentsas备注
FROMUSER_TAB_COLS
innerjoinuser_col_commentson
user_col_comments.TABLE_NAME=USER_TAB_COLS.TABLE_NAME
anduser_col_comments.COLUMN_NAME=USER_TAB_COLS.COLUMN_NAME
whereUSER_TAB_COLS.Table_Name='AAC'
索引管理
分区管理实例
创建分区
createtablespaceTS_MCHNT_TP_1
DATAFILE'C:
\TP1.ora'size50M
AUTOEXTENDonNEXT20MMAXSIZEUNLIMITED;
createtablespaceTS_MCHNT_TP_2
DATAFILE'C:
\TP2.ora'size50M
AUTOEXTENDonNEXT20MMAXSIZEUNLIMITED;
createtablespaceTS_MCHNT_TP_3
DATAFILE'C:
\TP3.ora'size50M
AUTOEXTENDonNEXT20MMAXSIZEUNLIMITED;
createtablespaceTS_MCHNT_TP_4
DATAFILE'C:
\TP4.ora'size50M
AUTOEXTENDonNEXT20MMAXSIZEUNLIMITED;
createtableA_PARTION
（
MCHNT_KEYVARCHAR2（50）notnull,
MCHNT_CDVARCHAR2（15）,
ACQ_INS_ID_CDVARCHAR2（8）,
MCHNT_NMVARCHAR2（255）,
CONN_MDCHAR
（1）,
MCHNT_STCHAR
（1）,
ADDRVARCHAR2（100）,
CONTACT_PERSONVARCHAR2（20）,
PHONEVARCHAR2（30）,
ZIP_CODECHAR（6）,
EMAILVARCHAR2（40）,
FAX_NOVARCHAR2（20）,
JOIN_DTDATE,
SETTLE_INS_ID_CDCHAR（8）,
IS_IC_CARDCHAR
（1）,
FEE_TYPECHAR
（1）,
DISCNUMBER（9,4）,
MIS_MCHNT_INCHAR
（1）,
FRN_MCHNT_INCHAR
（1）,
PROF_INS_ID_CDVARCHAR2（20）,
TERM_NUMNUMBER（5）,
AREA_NMVARCHAR2（20）,
IMPORTANT_FLAGNUMBER
（1）,
AREA_CDVARCHAR2（20）,
PUB_FLAGCHAR
（1）,
SRVMER_TYPECHAR
（2）,
CHNLCHAR
（2）,
PUB_NAMEVARCHAR2（40）,
WAY_CDVARCHAR2（3）,
REC_UPT_TSDATE,
REC_CRT_TSDATE,
MCHNT_TPCHAR（4）,
MCHNT_TP_NMVARCHAR2（80）,
TH_5_TYPESNUMBER
（2）,
NAME_5_TYPESVARCHAR2（30）,
TH_6_TYPESNUMBER
（2）,
NAME_6_TYPESVARCHAR2（30）,
TH_9_TYPESNUMBER
（2）,
NAME_9_TYPESVARCHAR2（30）,
TH_16_TYPESNUMBER
（2）,
NAME_16_TYPESVARCHAR2（30）,
TH_17_TYPESNUMBER
（2）,
NAME_17_TYPESVARCHAR2（30）,
TH_18_TYPESNUMBER
（2）,
NAME_18_TYPESVARCHAR2（30）,
TH_22_TYPESNUMBER
（2）,
NAME_22_TYPESVARCHAR2（30）,
UP_FLAGCHAR
（1）,
UP_ATNUMBER（4）,
DISC_DESCVARCHAR2（30）,
DISC_TPVARCHAR2
（2）,
MCHNT_CD_LOCVARCHAR2（30）,
CITY_CDCHAR（4）,
CITYVARCHAR2（20）,
COUNTYVARCHAR2（20）,
IS_IMPORTVARCHAR2
（1）notnull,
IS_FROM_ROW_FLOWNUMBER
（1）
）
PARTITIONBYlist（city_CD）
（PARTITIONcity3310VALUES（'3310'）TABLESPACETS_MCHNT_TP_1,
PARTITIONcity3380VALUES（'3380'）TABLESPACETS_MCHNT_TP_2,
PARTITIONcity3330VALUES（'3330','3450'）TABLESPACETS_MCHNT_TP_3,
PARTITIONcityOTHERVALUES（'3350','3370','3360','3430','3410','3420','1310'）TABLESPACETS_MCHNT_TP_4
）
查询表分区信息
user_part_tables：
记录分区的表的信息;
user_tab_partitions：
记录表的分区的信息。
索引分区信息
select*
fromuser_ind_partitions
orderbypartition_position;
查询分区表内容
select*fromdinya_testpartition（part_01）;
复合分区
Oracle提供了以下几种分区类型：
范围分区（range）；
哈希分区（hash）；
列表分区（list）；
范围－哈希复合分区（range-hash）；
范围－列表复合分区（range-list）。
Create table testA
（
Sale_Area varchar2（100） not null,
Sale_Date date not null
）
Partition by range（Sale_Date） subpartition by list（Sale_Area）
（
partition sale_date1 values less than （to_date（'2004-01-01','yyyy-mm-dd'
）） tablespace ts_Date1
（
subpartition s1 values （'广州'） ,
.
.
.
subpartition s10 values（'顺德'）
）,
.
.
.
升级会员 