ORACLE10G常用查询命令Word文档格式.docx
- 文档编号:5280448
- 上传时间:2023-05-04
- 格式:DOCX
- 页数:65
- 大小:34.60KB
ORACLE10G常用查询命令Word文档格式.docx
《ORACLE10G常用查询命令Word文档格式.docx》由会员分享,可在线阅读,更多相关《ORACLE10G常用查询命令Word文档格式.docx(65页珍藏版)》请在冰点文库上搜索。
5.changesthenameoftheonlineredologfile
alterdatabaserenamefile'
c:
/oracle/oradata/oradb/redo01.log'
to'
/oracle/oradata/redo01.log'
;
6.droponlineredologgroups
alterdatabasedroplogfilegroup3;
7.droponlineredologmembers
alterdatabasedroplogfilemember'
8.clearingonlineredologfiles
alterdatabaseclear[unarchived]logfile'
/oracle/log2a.rdo'
9.usinglogmineranalyzingredologfiles
a.intheinit.oraspecifyutl_file_dir='
b.sql>
executedbms_logmnr_d.build('
oradb.ora'
oracleoradblog'
);
c.sql>
executedbms_logmnr_add_logfile('
oracleoradataoradbredo01.log'
dbms_logmnr.new);
d.sql>
executedbms_logmnr.add_logfile('
oracleoradataoradbredo02.log'
dbms_logmnr.addfile);
e.sql>
executedbms_logmnr.start_logmnr(dictfilename=>
'
oracleoradblogoradb.ora'
f.sql>
select*fromv$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters
v$logmnr_logs);
g.sql>
executedbms_logmnr.end_logmnr;
第二章:
表空间管理
1.createtablespaces
createtablespacets_namedatafile'
oracleoradatafile1.dbf'
size100m,
oracleoradatafile2.dbf'
size100mminimumextent550k[logging/nologging]sql>
defaultstorage(initial500knext500kmaxextents500pctinccease0)
[online/offline][permanent/temporary][extent_management_clause]
2.locallymanagedtablespace
createtablespaceuser_datadatafile'
oracleoradatauser_data01.dbf'
size500mextentmanagementlocaluniformsize10m;
3.temporarytablespace
createtemporarytablespacetemptempfile'
oracleoradatatemp01.dbf'
4.changethestoragesetting
altertablespaceapp_dataminimumextent2m;
altertablespaceapp_datadefaultstorage(initial2mnext2mmaxextents999);
5.takingtablespaceofflineoronline
altertablespaceapp_dataoffline;
altertablespaceapp_dataonline;
6.read_onlytablespace
altertablespaceapp_datareadonly|write;
7.dropingtablespace
droptablespaceapp_dataincludingcontents;
8.enableingautomaticextensionofdatafiles
altertablespaceapp_dataadddatafile'
oracleoradataapp_data01.dbf'
size200m
autoextendonnext10mmaxsize500m;
9.changethesizefodatafilesmanually
alterdatabasedatafile'
oracleoradataapp_data.dbf'
resize200m;
10.Movingdatafiles:
altertablespace
altertablespaceapp_datarenamedatafile'
oracleoradataapp_data.dbf
oracleapp_data.dbf'
11.movingdatafiles:
alterdatabase
第三章:
表
1.createatable
createtabletable_name(columndatatype,columndatatype]....)
tablespacetablespace_name[pctfreeinteger][pctusedinteger]
[initransinteger][maxtransinteger]sql>
storage(initial200knext200kpctincrease0maxextents50)
[logging|nologging][cache|nocache]
2.copyanexistingtable
createtabletable_name[logging|nologging]assubquery
3.createtemporarytable
createglobaltemporarytablexay_tempasselect*fromxay;
oncommitpreserverows/oncommitdeleterows
4.pctfree=(averagerowsize-initialrowsize)*100/averagerowsize
pctused=100-pctfree-(averagerowsize*100/availabledataspace)
5.changestorageandblockutilizationparameter
altertabletable_namepctfree=30pctused=50storage(next500kminextents2maxextents100);
6.manuallyallocatingextents
altertabletable_nameallocateextent(size500kdatafile'
/oracle/data.dbf'
7.movetablespace
altertableemployeemovetablespaceusers;
8.deallocateofunusedspace
altertabletable_namedeallocateunused[keepinteger]
9.truncateatable
truncatetabletable_name;
10.dropatable
droptabletable_name[cascadeconstraints];
11.dropacolumn
altertabletable_namedropcolumncommentscascadeconstraintscheckpoint1000;
altertabletable_namedropcolumnscontinue;
12.markacolumnasunused
altertabletable_namesetunusedcolumncommentscascadeconstraints;
altertabletable_namedropunusedcolumnscheckpoint1000;
altertableordersdropcolumnscontinuecheckpoint1000
data_dictionary:
dba_unused_col_tabs
第四章:
索引
1.creatingfunction-basedindexes
createindexsummit.item_quantityonsummit.item(quantity-quantity_shipped);
2.createaB-treeindex
create[unique]indexindex_nameontable_name(column,..asc/desc)tablespace
tablespace_name[pctfreeinteger][initransinteger][maxtransinteger]
[logging|nologging][nosort]storage(initial200knext200kpctincrease0maxextents50);
3.pctfree(index)=(maximumnumberofrows-initialnumberofrows)*100/maximumnumberofrows
4.creatingreversekeyindexes
createuniqueindexxay_idonxay(a)reversepctfree30storage(initial200knext200kpctincrease0maxextents50)tablespaceindx;
5.createbitmapindex
createbitmapindexxay_idonxay(a)pctfree30storage(initial200knext200kpctincrease0maxextents50)tablespaceindx;
6.changestorageparameterofindex
alterindexxay_idstorage(next400kmaxextents100);
7.allocatingindexspace
alterindexxay_idallocateextent(size200kdatafile'
/oracle/index.dbf'
8.alterindexxay_iddeallocateunused;
第五章:
约束
1.defineconstraintsasimmediateordeferred
altersessionsetconstraint[s]=immediate/deferred/default;
setconstraint[s]constraint_name/allimmediate/deferred;
2.sql>
droptabletable_namecascadeconstraints
droptablespacetablespace_nameincludingcontentscascadeconstraints
3.defineconstraintswhilecreateatable
createtablexay(idnumber(7)constraintxay_idprimarykeydeferrable
usingindexstorage(initial100knext100k)tablespaceindx);
primarykey/unique/referencestable(column)/check
4.enableconstraints
altertablexayenablenovalidateconstraintxay_id;
5.enableconstraints
altertablexayenablevalidateconstraintxay_id;
第六章:
LOAD数据
1.loadingdatausingdirect_loadinsert
insert/*+append*/intoempnologging
select*fromemp_old;
2.paralleldirect-loadinsert
altersessionenableparalleldml;
insert/*+parallel(emp,2)*/intoempnologging
3.usingsql*loader
sqlldrscott/tiger
control=ulcase6.ctl
log=ulcase6.logdirect=true
第七章:
reorganizingdata
1.usingexpoty
$expscott/tigertables(dept,emp)file=c:
emp.dmplog=exp.logcompress=ndirect=y
2.usingimport
$impscott/tigertables(dept,emp)file=emp.dmplog=imp.logignore=y
3.transportingatablespace
altertablespacesales_tsreadonly;
$expsys/..file=xay.dmptransport_tablespace=ytablespace=sales_ts
triggers=nconstraints=n
$copydatafile
$impsys/..file=xay.dmptransport_tablespace=ydatafiles=(/disk1/sles01.dbf,/disk2
/sles02.dbf)
altertablespacesales_tsreadwrite;
4.checkingtransportset
DBMS_tts.transport_set_check(ts_list=>
sales_ts'
..,incl_constraints=>
true);
在表transport_set_violations中查看
dbms_tts.isselfcontained为true是,表示自包含
第八章:
managingpasswordsecurityandresources
1.controllingaccountlockandpassword
alteruserjunckyidentifiedbyoracleaccountunlock;
2.user_providedpasswordfunction
function_name(useridinvarchar2(30),passwordinvarchar2(30),
old_passwordinvarchar2(30))returnboolean
3.createaprofile:
passwordsetting
createprofilegrace_5limitfailed_login_attempts3
password_lock_timeunlimitedpassword_life_time30
password_reuse_time30password_verify_functionverify_function
password_grace_time5;
4.alteringaprofile
alterprofiledefaultlimit
failed_login_attempts3
password_life_time60;
5.dropaprofile
dropprofilegrace_5[cascade];
6.createaprofile:
resourcelimit
createprofiledeveloper_proflimitsessions_per_user2
cpu_per_session10000idle_time60connect_time480;
7.view=>
resource_cost:
alterresourcecost
dba_Users,dba_profiles
8.enableresourcelimits
altersystemsetresource_limit=true;
第九章:
Managingusers
1.createauser:
databaseauthentication
createuserjunckyidentifiedbyoracledefault
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE10G 常用 查询 命令