oracle11g常用命令.docx
- 文档编号:14008198
- 上传时间:2023-06-20
- 格式:DOCX
- 页数:14
- 大小:18.96KB
oracle11g常用命令.docx
《oracle11g常用命令.docx》由会员分享,可在线阅读,更多相关《oracle11g常用命令.docx(14页珍藏版)》请在冰点文库上搜索。
oracle11g常用命令
第一章:
日志管理
1.forcinglogswitches
sql>altersystemswitchlogfile;
2.forcingcheckpoints
sql>altersystemcheckpoint;
3.addingonlineredologgroups
sql>alterdatabaseaddlogfile[group4]
sql>('/disk3/log4a.rdo','/disk4/log4b.rdo'size1m;
4.addingonlineredologmembers
sql>alterdatabaseaddlogfilemember
sql>'/disk3/log1b.rdo'togroup1,
sql>'/disk4/log2b.rdo'togroup2;
5.changesthenameoftheonlineredologfile
sql>alterdatabaserenamefile'c:
/oracle/oradata/oradb/redo01.log'sql>to'c:
/oracle/oradata/redo01.log';
6.droponlineredologgroups
sql>alterdatabasedroplogfilegroup3;
7.droponlineredologmembers
sql>alterdatabasedroplogfilemember'c:
/oracle/oradata/redo01.log';8.clearingonlineredologfiles
sql>alterdatabaseclear[unarchived]logfile'c:
/oracle/log2a.rdo';9.usinglogmineranalyzingredologfiles
a.intheinit.oraspecifyutl_file_dir=''
b.sql>executedbms_logmnr_d.build('oradb.ora','c:
\oracle\oradb\log';
c.sql>executedbms_logmnr_add_logfile('c:
\oracle\oradata\oradb\redo01.log',sql>dbms_logmnr.new;
d.sql>executedbms_logmnr.add_logfile('c:
\oracle\oradata\oradb\redo02.log',sql>dbms_logmnr.addfile;
e.sql>executedbms_logmnr.start_logmnr(dictfilename=>'c:
\oracle\oradb\log\oradb.ora';
f.sql>select*fromv$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameterssql>v$logmnr_logs;
g.sql>executedbms_logmnr.end_logmnr;
第二章:
表空间管理
1.createtablespaces
sql>createtablespacetablespace_namedatafile
'c:
\oracle\oradata\file1.dbf'size100m,
sql>'c:
\oracle\oradata\file2.dbf'size100mminimumextent550k[logging/nologging]
sql>defaultstorage(initial500knext500kmaxextents500pctinccease0
sql>[online/offline][permanent/temporary][extent_management_clause]2.locallymanagedtablespace
sql>createtablespaceuser_datadatafile
'c:
\oracle\oradata\user_data01.dbf'
sql>size500mextentmanagementlocaluniformsize10m;
3.temporarytablespace
sql>createtemporarytablespacetemptempfile
'c:
\oracle\oradata\temp01.dbf'
sql>size500mextentmanagementlocaluniformsize10m;
4.changethestoragesetting
sql>altertablespaceapp_dataminimumextent2m;
sql>altertablespaceapp_datadefaultstorage(initial2mnext2mmaxextents999;
5.takingtablespaceofflineoronline
sql>altertablespaceapp_dataoffline;
sql>altertablespaceapp_dataonline;
6.read_onlytablespace
sql>altertablespaceapp_datareadonly|write;
7.dropingtablespace
sql>droptablespaceapp_dataincludingcontents;
8.enableingautomaticextensionofdatafiles
sql>altertablespaceapp_dataadddatafile'c:
\oracle\oradata\app_data01.dbf'size200m
sql>autoextendonnext10mmaxsize500m;
9.changethesizefodatafilesmanually
sql>alterdatabasedatafile'c:
\oracle\oradata\app_data.dbf'resize200m;
10.Movingdatafiles:
altertablespace
sql>altertablespaceapp_datarenamedatafile'c:
\oracle\oradata\app_data.dbf'
sql>to'c:
\oracle\app_data.dbf';
11.movingdatafiles:
alterdatabase
sql>alterdatabaserenamefile'c:
\oracle\oradata\app_data.dbf'sql>to'c:
\oracle\app_data.dbf';
第三章:
表
1.createatable
sql>createtabletable_name(columndatatype,columndatatype]sql>tablespace
tablespace_name[pctfreeinteger][pctusedinteger]sql>[initransinteger][maxtransinteger]
sql>storage(initial200knext200kpctincrease0maxextents50sql>[logging|nologging][cache|nocache]
2.copyanexistingtable
sql>createtabletable_name[logging|nologging]assubquery
3.createtemporarytable
sql>createglobaltemporarytablexay_tempasselect*fromxay;oncommitpreserverows/oncommitdeleterows
4.pctfree=(averagerowsize-initialrowsize*100/averagerowsizepctused=100-pctfree-(averagerowsize*100/availabledataspace5.changestorageandblockutilizationparameter
sql>altertabletable_namepctfree=30pctused=50storage(next500ksql>minextents2maxextents100;
6.manuallyallocatingextents
sql>altertabletable_nameallocateextent(size500kdatafile
'c:
/oracle/data.dbf';
7.movetablespace
sql>altertableemployeemovetablespaceusers;
8.deallocateofunusedspace
sql>altertabletable_namedeallocateunused[keepinteger]
9.truncateatable
sql>truncatetabletable_name;
10.dropatable
sql>droptabletable_name[cascadeconstraints];
11.dropacolumn
sql>altertabletable_namedropcolumncommentscascadeconstraintscheckpoint1000;
altertabletable_namedropcolumnscontinue;
12.markacolumnasunused
sql>altertabletable_namesetunusedcolumncommentscascadeconstraints;
altertabletable_namedropunusedcolumnscheckpoint1000;altertableordersdropcolumnscontinuecheckpoint1000
data_dictionary:
dba_unused_col_tabs
第四章:
索引
1.creatingfunction-basedindexes
sql>createindexsummit.item_quantityonsummit.item(quantity-quantity_shipped;
2.createaB-treeindex
sql>create[unique]indexindex_nameontable_name(column,..asc/desctablespace
sql>tablespace_name[pctfreeinteger][initransinteger][maxtransinteger]
sql>[logging|nologging][nosort]storage(initial200knext200kpctincrease0sql>maxextents50;
3.pctfree(index=(maximumnumberofrows-initialnumberofrows*100/maximumnumberofrows
4.creatingreversekeyindexes
sql>createuniqueindexxay_idonxay(areversepctfree30storage(initial200k
sql>next200kpctincrease0maxextents50tablespaceindx;
5.createbitmapindex
sql>createbitmapindexxay_idonxay(apctfree30storage(initial200knext200ksql>pctincrease0maxextents50tablespaceindx;
6.changestorageparameterofindex
sql>alterindexxay_idstorage(next400kmaxextents100;
7.allocatingindexspace
sql>alterindexxay_idallocateextent(size200kdatafile
'c:
/oracle/index.dbf';
8.alterindexxay_iddeallocateunused;
第五章:
约束
1.defineconstraintsasimmediateordeferred
sql>altersessionsetconstraint[s]=immediate/deferred/default;setconstraint[s]constraint_name/allimmediate/deferred;
2.sql>droptabletable_namecascadeconstraints
sql>droptablespacetablespace_nameincludingcontentscascadeconstraints
3.defineconstraintswhilecreateatable
sql>createtablexay(idnumber(7constraintxay_idprimarykeydeferrable
sql>usingindexstorage(initial100knext100ktablespaceindx;primarykey/unique/referencestable(column/check
4.enableconstraints
sql>altertablexayenablenovalidateconstraintxay_id;
5.enableconstraints
sql>altertablexayenablevalidateconstraintxay_id;
第六章:
LOAD数据
1.loadingdatausingdirect_loadinsert
sql>insert/*+append*/intoempnologging
sql>select*fromemp_old;
2.paralleldirect-loadinsert
sql>altersessionenableparalleldml;
sql>insert/*+parallel(emp,2*/intoempnologging
sql>select*fromemp_old;
3.usingsql*loader
sql>sqlldrscott/tiger\
sql>control=ulcase6.ctl\
sql>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
sql>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
sql>altertablespacesales_tsreadwrite;
4.checkingtransportset
sql>DBMS_tts.transport_set_check(ts_list
=>'sales_ts'..,incl_constraints=>true;
在表transport_set_violations中查看
sql>dbms_tts.isselfcontained为true是,表示自包含
第八章:
managingpasswordsecurityandresources
1.controllingaccountlockandpassword
sql>alteruserjunckyidentifiedbyoracleaccountunlock;
2.user_providedpasswordfunction
sql>function_name(useridinvarchar2(30,passwordinvarchar2(30,old_passwordinvarchar2(30returnboolean
3.createaprofile:
passwordsetting
sql>createprofilegrace_5limitfailed_login_attempts3
sql>password_lock_timeunlimitedpassword_life_time30
sql>password_reuse_time30password_verify_functionverify_functionsql>password_grace_time5;
4.alteringaprofile
sql>alterprofiledefaultfailed_login_attempts3
sql>password_life_time60password_grace_time10;
5.dropaprofile
sql>dropprofilegrace_5[cascade];
6.createaprofile:
resourcelimit
sql>createprofiledeveloper_proflimitsessions_per_user2
sql>cpu_per_session10000idle_time60connect_time480;
7.view=>resource_cost:
alterresourcecost
dba_Users,dba_profiles
8.enableresourcelimits
sql>altersystemsetresource_limit=true;
第九章:
Managingusers
1.createauser:
databaseauthentication
sql>createuserjunckyidentifiedbyoracledefaulttablespaceuserssql>temporarytablespacetempquota10mondatapasswordexpiresql>[accountlock|unlock][profileprofilename|default];
2.changeuserquotaontablespace
sql>alteruserjunckyquota0onusers;
3.dropauser
sql>dropuserjuncky[cascade];
4.monitoruser
view:
dba_users,dba_ts_quotas
第十章:
managingprivileges
1.systemprivileges:
view=>
system_privilege_map,dba_sys_privs,session_privs
2.grantsystemprivilege
sql>grantcreatesession,createtabletomanagers;
sql>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
sql>revokecreatetablefromkaren;
sql>revokecreates
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle11g 常用命令