oracle11g常用命令Word格式.docx
- 文档编号:22198911
- 上传时间:2023-02-03
- 格式:DOCX
- 页数:14
- 大小:18.58KB
oracle11g常用命令Word格式.docx
《oracle11g常用命令Word格式.docx》由会员分享,可在线阅读,更多相关《oracle11g常用命令Word格式.docx(14页珍藏版)》请在冰豆网上搜索。
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>
execute
dbms_logmnr_d.build('
oradb.ora'
\oracle\oradb\log'
c.sql>
dbms_logmnr_add_logfile('
\oracle\oradata\oradb\redo01.log'
dbms_logmnr.new;
d.sql>
dbms_logmnr.add_logfile('
\oracle\oradata\oradb\redo02.log'
dbms_logmnr.addfile;
e.sql>
dbms_logmnr.start_logmnr(dictfilename=>
'
\oracle\oradb\log\oradb.ora'
f.sql>
select*from
v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters
v$logmnr_logs;
g.sql>
executedbms_logmnr.end_logmnr;
第二章:
表空间管理
1.createtablespaces
createtablespacetablespace_namedatafile
\oracle\oradata\file1.dbf'
size100m,
\oracle\oradata\file2.dbf'
size100mminimumextent550k[logging/nologging]
defaultstorage(initial500knext500kmaxextents500pctinccease0
[online/offline][permanent/temporary][extent_management_clause]2.locallymanagedtablespace
createtablespaceuser_datadatafile
\oracle\oradata\user_data01.dbf'
size500mextentmanagementlocaluniformsize10m;
3.temporarytablespace
createtemporarytablespacetemptempfile
\oracle\oradata\temp01.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
\oracle\oradata\app_data01.dbf'
size200m
autoextendonnext10mmaxsize500m;
9.changethesizefodatafilesmanually
alterdatabasedatafile'
\oracle\oradata\app_data.dbf'
resize200m;
10.Movingdatafiles:
altertablespace
altertablespaceapp_datarenamedatafile
\oracle\app_data.dbf'
11.movingdatafiles:
alterdatabase
第三章:
表
1.createatable
createtabletable_name(columndatatype,columndatatype]....sql>
tablespacetablespace_name[pctfreeinteger][pctusedinteger]sql>
[initransinteger][maxtransinteger]
storage(initial200knext200kpctincrease0maxextents50sql>
[logging|nologging][cache|nocache]
2.copyanexistingtable
createtabletable_name[logging|nologging]assubquery
3.createtemporarytable
createglobaltemporarytablexay_tempasselect*fromxay;
oncommitpreserverows/oncommitdeleterows
4.pctfree=(averagerowsize-initialrowsize*100/averagerowsizepctused=100-pctfree-(averagerowsize*100/availabledataspace5.changestorageandblockutilizationparameter
altertabletable_namepctfree=30pctused=50storage(next500ksql>
minextents2maxextents100;
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_quantityon
summit.item(quantity-quantity_shipped;
2.createaB-treeindex
create[unique]indexindex_nameontable_name(column,..asc/desctablespace
tablespace_name[pctfreeinteger][initransinteger][maxtransinteger]
[logging|nologging][nosort]storage(initial200knext200kpctincrease0
maxextents50;
3.pctfree(index=(maximumnumberofrows-initialnumberof
rows*100/maximumnumberofrows
4.creatingreversekeyindexes
createuniqueindexxay_idonxay(areversepctfree30storage(initial200k
next200kpctincrease0maxextents50tablespaceindx;
5.createbitmapindex
createbitmapindexxay_idonxay(apctfree30storage(initial200knext200k
pctincrease0maxextents50tablespaceindx;
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(7constraintxay_idprimarykeydeferrable
usingindexstorage(initial100knext100ktablespaceindx;
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,empfile=c:
\emp.dmplog=exp.logcompress=ndirect=y
2.usingimport
$impscott/tigertables(dept,empfile=emp.dmplog=imp.logignore=y3.transportingatablespace
altertablespacesales_tsreadonly;
$expsys/..file=xay.dmptransport_tablespace=ytablespace=sales_tstriggers=nconstraints=n
$copydatafile
$impsys/..file=xay.dmptransport_tablespace=y
datafiles=(/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(30returnboolean
3.createaprofile:
passwordsetting
createprofilegrace_5limitfailed_login_attempts3
password_lock_timeunlimitedpassword_life_time30
password_reuse_time30password_verify_functionverify_functionsql>
password_grace_time5;
4.alteringaprofile
alterprofiledefaultfailed_login_attempts3
password_life_time60password_grace_time10;
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
createuserjunckyidentifiedbyoracledefaulttablespaceuserssql>
temporarytablespacetempquota10mondatapasswordexpiresql>
[accountlock|unlock][profileprofilename|default];
2.changeuserquotaontablespace
alteruserjunckyquota0onusers;
3.dropauser
dropuserjuncky[cascade];
4.monitoruser
view:
dba_users,dba_ts_quotas
第十章:
managingprivileges
1.systemprivileges:
view=>
system_privilege_map,dba_sys_privs,session_privs
2.grantsystemprivilege
grantcreatesession,createtabletomanagers;
grantcreatesessiontoscottwithadminoption;
withadminoptioncangrantorrevokeprivilegefromanyuserorrole;
3.sysdbaandsysoperprivileges:
sysoper:
startup,shutdown,alterdatabaseopen|mount,alterdatabasebackupcontrolfile,
altertablespacebegin/endbackup,recoverdatabase
alterdatabasearchivelog,restrictedsession
sysdba:
sysoperprivilegeswithadminoption,createdatabase,recoverdatabaseuntil
4.passwordfilemembers:
view:
v$pwfile_users
5.O7_dictionary_accessibility=truerestrictionaccesstoviewortablesinotherschema
6.revokesystemprivilege
revokecreatetablefromkaren;
sq
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle11g 常用命令