oracle笔记一个称职的数据库管理员需要全面和深入了解ORACLE机制

vim/etc/sysctl.confkernel.shmmaxkernel.shmall=fs.aio-max-nrfs.file-max=kernel.shmmni=4096kernel.sem=25032000100net.ipv4.ip_local_port_range=9000net.core.rmem_default=262144net.core.rmem_max=net.core.wmem_default=262144net.core.wmem_max=visession vi/etc/security/limits.conforaclesoftnproc8192oraclehardnproc16384oraclesoftnofile32768oraclehardnofile65536passwdoraclehosts+su-oraclevi#Getthealiasesandfunctionsif[-f~/.bashrc];then.#UserspecificenvironmentandstartupprogramsexportexportORACLE_SID=orclexportexportexportLD_LIBRARY_PATH=$LD_LIBRARY_PATH:ORACLE_HOME/libexportNLS_LANG=AMERICAN_AMERICA.ZHS16GBKunsetUSERNAMEumaskaliassqlplus='rlwrapsqlplus/nolog'aliasrman='rlwraprman'exportcd一种命令createsqlplusnologSQL>conn/assysdba;Startupnomount; alterdatabasemount;（ alterdatabaseopen;（数据文件和redolog文件。如果找不到这两个文件将出现错误，必须进行恢复）Startupmount;（参数和控制文件）alterdatabaseopen;（数据库文件Shutdown Shutdown Shutdown Shutdowm Sql>selectsid,serial#,usernamefromSql>ALTERSYSTEMKILLSESSIONSelectaddr,statusfromstartupstartup=shutdownabort+startupmount=shutdownabort+startupLsnrctlstatusLsnrctlstartLsnrctl6、emctlstatusdbconsoleemctlstartemctlstop7、安装yum-yinstallreadline-make;makeinstall第二 ls–lls–l,selecttablespace_name,statusfromdba_tablespaces;select )||'M'MBfromselect*fromselect*fromv$logfile;SELECT*FROMV$CONTROLFILE;AltertablespaceAltertablespacedatareadonly;Altertablespacedatareadwrite;Droptablespacedataincludinground(((a.BYTES-b.BYTES)/a.BYTES)*100,2)percent_used

(selectTABLESPACE_NAME,sum(BYTES)BYTESfromdba_data_filesgroupbyTABLESPACE_NAME )a,(SelectTABLESPACE_NAME,sum(BYTES)BYTES,max(BYTES)largestfromdba__spaceGroupbyTABLESPACE_NAME)SGASQL>showparameterSQL>showSQL>select*from ps-ef|grepora_SQL>selectdirty,count(*)fromv$bhgroupbydirty;DCOUNT(*)- Systemaltered.SQL>selectdirty,count(*)fromv$bhgroupbydirty;DCOUNT(*)- Createuseru1identifiedbyu1defaulttablespacedata;Alteruseru1identifiedby123;Alteruserhridentifiedbyhraccountunlock;Dropuseru1cascade;SQL>connSQL>select*fromsession_privs;SQL>select*fromsession_roles;SQL>select*fromuser_sys_privs;SQL>grantcreateanytabletohr;SQL>select*fromSQL>revokecreateanytablefrom第三 ORACLE配置管Oracle客户端与服务器连接和数据传递是通过OracleNet协议实现的。它同时驻留在客户端和服务Connectusername/password@net_service_name 创建一个cpinit.ora$ORACLE_HOME/dbs/inittestdb.oraSTARTUPPFILE=$ORACLE_HOME/dbs/inittestdb.ora创建一个SQL>CREATESPFILEFROMPFILE;SQL>CREATEPFILEFROMSelectnamefromSQL>AltersystemSelectgroup#,sequence#,bytes,members,archived,statusfromv$log;Selectgroup#,status,type,memberfromv$logfile;AltersystemswitchaltersystemaltersystemflushSQL>selectm.group#,m.member,g.bytesfromv$loggjoinv$logfilem2onm.group#=g.group#orderbym.group#,m.member;Alterdatabaseaddlogfilegroup4’/u01/oracle/oradata/sales/redo04.log’size10M;Alterdatabasedroplogfilegroup4;SQL>select*fromGROUP#THREAD# BYTESMEMBERSARC 1101YES02101YES03111NOUNUSEDSQL>Archiveloglist;SQL>alterdatabasearchivelog;SQL>alterdatabaseopen;all_*:当前用户所能的对象的信息select*fromuser_tables;selecttable_namefromdictionarywheretable_namelikeSQL>select*fromSQL>select*fromSQL>ts,df,d.bytesfromv$tablespacetjoin2v$datafiledont.ts#=d.ts#;第四章SQL>selectcount(*)fromm;SQL>foriin1insertintomend;/SQL>selectcount(*)from mit 索引的分Createindexemp_ename_idxon 查看索DropindexSQL>colobject_nameformatSQL>createtablet(idint, char(1),namechar(10));SQL>selectobject_name,object_typefromuser_objects;SQL>foriininsertintotend/SQL>select*fromSQL>createindext_idx1onSQL>createindext_idx2on SQL>updatetset ='F'whereid>23andid<45;SQL>select*fromtwhereid>23andid<45;SQL>createbitmapindexmy_bit_idxont(SQL>alterindext_idx1SQL>alterindext_idx1rebuildSQL>alterindext_idx1 创建视Createorreceviewpen_viewasselect*fromorderwherestatus=’p’; Selectview_namefromuser_views;Dropviewemp_dept; OnOnCreatematerializedviewmtv_testBuildimmediateOnSelectd.dname,d.loc,e.ename,e.job,e.mgr,e.salFromdeptd,empeWhereDropmaterializedview Startwith10IncrementbyCache 序Insertintotoysvalues(toy_seq.nextval,’TWENTY’,25)InsertintotoysSelecttoy_seq.currvalfromAltersequencetoy_seqmaxvalue5000Selectdropsequence自身模式Createsynonym权限Createsynonymsy_empforCreatetable(sale_idnumber,product_idvarchar2(5),sales_datedatenotnull)Partitionbyrange(sales_date)(Partitionp1valueslessthen(to_date(‘2013-04-1’,’yyy-mm-dd’)),Partitionp2valueslessthen(to_date(‘2013-07-1’,’yyy-mm-dd’)),Partitionp3valueslessthen(to_date(‘2013-10-1’,’yyy-mm-dd’)),Partitionp4valueslessthen(to_date(‘2014-01-1’,’yyy-mm-dd’)),Partitionp5valueslessthen(maxvalue)Select*fromsalesSQL>Select*from SQL>Createuserrmanidentifiedbyrmandefaulttablespacermants;SQL>grantconnect,resource,recovery_catalog_ownertorman;$rmancatalog$rmantargetRMAN>registerdatabase;RMAN>Configuredevicetypediskparallelism5;RMAN>configuredefaultdevicetypetodisk;Allocatechannelch1devicetypedisk;Allocatechannelch2devicetypedisk;Allocatechannelch3devicetypedisk;}FormatRMAN>backupdatabaseplusarchivelogdeleteinput;Allocatechannelc1devicetypeBackupdatabaseformatRMAN>startupmount;RMAN>restoredatabase;RMAN>backupfilesperset=3tablespaceusers,system,sysaux;Allocatechannelc1devicetypeReleasechannelc1;}Sql‘altertablespaceusersofflineRestoretablespaceusers;Sql‘altertablespaceusersRMAN>backupdatafile

Allocatechannelc1devicetypeSql‘altertablespaceusersofflineRestoredatafileSql‘altertablespaceusers$impdpsystem/Oracle11g@neworcldirectory=dump_dirfumpfile=scott.dmptables=scott.dept,scott.empREMAP_SCHEMA=SCOTT:TOMSQL>altersystemsetSQL>AltersystemsetSQL>alterdatabaseflashbackon;SQL>alterdatabaseopen;SQL>showparameterdb_recovery_file; fromv$database;SQL>createtablet1(idnumber,namevarchar2(20));SQL>Insertintot1values(1,’database’);SQL>commit;SQL>flashbackdatabasetoscn735884;SQL>alterdatabseopenresetlogs;SQL>select*from SQL>selectsysdatefromSQL>inse