ITPub博客

--create shcema 组合create table /ceate view /grant语句，可以回滚CREATE SCHEMA AUTHORIZATION scottCREATE TABLE dept (deptno NUMBER(3,0) PRIMARY KEY,dname VARCHAR2(15),loc VARCHAR2(25))CREATE TABLE emp (empno NUMBER(5,0) PRIMARY KEY,ename VARCHAR2(15) NOT NULL,job VARCHAR2(10),mgr NUMBER(5,0),hiredate DATE DEFAULT (sysdate),sal NUMBER(7,2),comm NUMBER(7,2),deptno NUMBER(3,0) NOT NULLCONSTRAINT dept_fkey REFERENCES dept)CREATE VIEW sales_staff ASSELECT empno, ename, sal, commFROM empWHERE deptno =

--asm automatic storage managementdisk group failure disk groupredundancy: normal(2 mirroring) high(3 mirroring) extent(unprotect)--disk failure rebalance 调整 ASM_POWER_LIMIT 参数来调整rebanlance的速度V$ASM_OPERATION --监控rebalance的速度ASM_DISKSTRING --/dev/rdsk/* /dev/rdsk/*s[34]ALTER DISKGROUP...MOUNTALTER DISKGROUP...ADD DISK% sqlplus /nologSQL> CONNECT / AS sysdbaConnected to an idle instance.SQL> STARTUPASM instance startedcrsctl check cssdcrsctl localconfigV$ASM_DISKGROUPV$ASM_DIS

--oracle managed filesDB_CREATE_FILE_DESTDB_CREATE_ONLINE_LOG_DEST_NDB_RECOVERY_FILE_DESTDB_RECOVERY_FILE_DEST = '/u01/oradata'DB_RECOVERY_FILE_DEST_SIZE = 20GDB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oradata'DB_CREATE_ONLINE_LOG_DEST_2 = '/u03/oradata'总结：oracle managed files多用在开发或者测试环境，管理方便。包括各种文件 data file,control file,redo log file,archive log,backup pieces.设置file dest；然后设置dest size限制，create file的时候只要制定路径即可，不需要命名（不过不建议这么自动命名，建议文件

--undo作用1、Roll back transactions when a ROLLBACK statement is issued2、Recover the database3、Provide read consistencyAnalyze data as of an earlier point in time by using Oracle Flashback Query4、Recover from logical corruptions using Oracle Flashback features--init 参数UNDO_MANAGEMENT auto/manualUNDO_TABLESPACE undotbs_1UNDO_RETENTION in secondsalter tablespace *** RETENTION GUARANTEE--Migrating to Automatic Undo ManagementDECLAREutbsiz_in_MB NUMBER;BEGINutbsiz_in_MB := DBMS_UNDO_ADV.RBU_

Using Multiple TablespacesAssigning Tablespace Quotas to Usersalter tablespace tt read only;-- read writealter tablespace tt rename to readonlySQL> alter tablespace readonly offline; Tablespace altered SQL> SQL> alter tablespace readonly online; Tablespace alteredDBA_TABLESPACESUSER_TABLESPACESV$TABLESPACEDBA_TABLESPACE_GROUPSCREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf'SIZE 50MTABLESPACE GROUP group1;ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;DB_nK_

archived log对应redo log，archive log文件大小稍微比redo小一些，大小基本一致，除非在redo log未填满的情况下手工切换在线日志归档，归档日志才会小。参数LOG_ARCHIVE_MAX_PROCESSES是动态的，不需要修改，如果修改：ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3;多路归档成功与否：参数LOG_ARCHIVE_MIN_SUCCEED_DESTLOG_ARCHIVE_MIN_SUCCEED_DEST=n (n>=1)MANDATORY >=1MANDATORY OPTIONALREOPENif (LOG_ARCHIVE_MIN_SUCCEED_DEST < MANDATORY ) LOG_ARCHIVE_MIN_SUCCEED_DEST 此参数忽略end if;MANDATORY + OPTIONAL &gt

Oracle DBA职责及日常工作分析DBA 职责及日常工作职责:1.安装和升级数据库服务器,以及应用程序工具构建和配置网络环境.2.熟悉数据库系统的存储结构预测未来的存储需求,制订数据库的存储方案.3.根据开发人员设计的应用系统需求创建数据库存储结构.4.根据开发人员设计的应用系统需求创建数据库对象5.根据开发人员的反馈信息,在必要的时候修改数据库的结构6.管理数据库的用户维护数据库的安全性7.控制和监控用户对数据库的存取访问8.监控和优化数据库的性能9.制定数据库备份计划,灾难出现时对数据库信息进行恢复10.备份和恢复数据库11.联系数

提交完成标志：Only when all redo recordsassociated with a given transaction are safely on disk in the online logs is the userprocess notified that the transaction has been committed.■ If archiving is disabled (the database is in NOARCHIVELOG mode), a filled redo logfile is available after the changes recorded in it have been written to the datafiles.■ If archiving is enabled (the database is in ARCHIVELOG mode), a filled redo log fileis available to LGWR after the changes recorded in it have b

SQL> startup restrict;ORACLE 例程已经启动。Total System Global Area 524288000 bytesFixed Size 1249968 bytesVariable Size 205524304 bytesDatabase Buffers 310378496 bytesRedo Buffers 7135232 bytes数据库装载完毕。数据库已经打开。SQL> alter system disable restricted session;系统已更改。SQL>restrict 只有管理员能操作database，其他用户连接不上，restrict用于维护数据库时使用[@more@]

1、备份控制文件：ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';ALTER DATABASE BACKUP CONTROLFILE TO TRACE;2、损坏 重建CREATE CONTROLFILESET DATABASE prodLOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log','/u01/oracle/prod/redo01_02.log'),GROUP 2 ('/u01/oracle/prod/redo02_01.log','/u01/oracle/prod/redo02_02.log'),GROUP 3 ('/u01/oracle/prod/redo03_01.log','/u01/oracle/prod/redo03_02.log')RESETLOGSDATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,'/u01/oracle/prod/rbs01.dbs' SIZE

shutdown 执行过程 是否需要恢复 normal 不允许新的连接 不需要 等待用户断开连接 immediate 不允许新的连接 不需要 不允许新的事务 回滚未提交的事务 隐式断开连接，并回滚 transactional 不允许新的连接 不需要 不允许新的事务 活动事务完成后 隐式断开连接 abort 不允许新的连接 需要 不允许新的

select ksppinm,ksppdesc from x$ksppi where ksppinm like 'db%'结果如下1 dbwr_io_slaves DBWR I/O slaves2 db_file_name_convert datafile name convert patterns and strings for standby/clone db3 db_block_buffers Number of database blocks cached in memory4 db_block_checksum store checksum in db blocks and check during reads5 db_block_size Size of database block in bytes6 db_cache_size Size of DEFAULT buffer pool for standard block size buffers7 db_2k_cache_size Size of cache for 2K buffer

特别是ENABLE NOVALIDATE；可以不计前嫌，不验证之前的数据Constraint States■ ENABLE ensures that all incoming data conforms to the constraint■ DISABLE allows incoming data, regardless of whether it conforms to theconstraint■ VALIDATE ensures that existing data conforms to the constraint■ NOVALIDATE means that some existing data may not conform to the constraintIn addition:■ ENABLE VALIDATE is the same as ENABLE. The constraint is checked and isguaranteed to hold for all rows.■ ENABLE NOVALIDA

1)、简单分区rangelisthash2）、复合分区range listrange hashexample：1）、list partitioncreate table aaa_partitioned(id number,xm varchar2(100),location varchar2(100),note varchar2(100),constraint pk_aaa_partitioned primary key (id)) PARTITION BY LIST (location) ( PARTITION aaa_partitioned_beijing VALUES ('beijing'), PARTITION aaa_partitioned_shanghai VALUES ('shanghai'), PARTITION aaa_partitioned_tianjin VALUES ('tianjin'), PARTITION aaa_partitioned_chongqing VALUES ('chongqing'), PART

SQL> select count(*) from aaa_partitioned_table; COUNT(*)---------- 1000000 Executed in 0.375 seconds SQL> truncate table aaa_partitioned; Table truncated Executed in 2.172 seconds SQL> insert /*+ APPEND */ into aaa_partitioned nologging select * from aaa_partitioned_table; 1000000 rows inserted Executed in 29 seconds SQL> commit; Commit complete Executed in 0 seconds[@more@]

说明：冷备份，就是关闭db，物理copy数据文件、日志文件、控制文件、归档文件。rem ***** COLDBACKUP ORACLE DATABASE ON WINDOWS NT *****set ShutDownFile=C:ShutDown.SQLecho connect internal/oracle > %ShutDownFile%echo shutdown immediate >> %ShutDownFile%echo startup >> %ShutDownFile%echo shutdown normal >> %ShutDownFile%echo exit >> %ShutDownFile%rem ***** SHUTDOWN THE DATABASE *******set oracle_sid=dbprimsvrmgrl @%ShutDownFile%net stop OracleStart%oracle_sid%net stop OracleS

一、oracle startup 分3步1)nomount:启动实例，也就是加载sga和启动oracle后台进程2)mount：打开加载control file；把instrance和database关联起来3)open：加载db file；redo file等，并进行一致性检查startup;==startup nomount;alter database mount;alter database open;二、oracle shutdown 也分分3步1）close：把sga buffer写入db file、 redo file。关闭db redo file2）unmount：取消instance和database的关联，关闭control file3）shutdown：释放sga内存，关闭oracle后台进程。shutdown normalshutdown immediate;shutdown abort;[

1）private SQL area2）Session Memory（hold a session’s variables (logon information) and other information related to the session.）3）SQL Work Areas重要参数和动态性能视图PGA_AGGREGATE_TARGET 用来代替SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE.OPEN_CURSORS 每个会话打开的游标数，不需要太大，关键是应用程序要及时关闭游标。V$SYSSTATV$SESSTATV$PGASTATV$SQL_WORKAREAV$SQL_WORKAREA_ACTIVE[@more@]

SGA1、Followed components can be managed auto.1)shared pool(sql pl/sql execute, dictionary cache)2)buffer cache(data buffer read from /write to disk；使用两个list管理：write list/LRU list.)The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list.3)redo buffer pool( redo buffer write to disk，log_buffer一般设置1M到3M,见注1)4)l

Creating a reverse key index, compared to a standard index, reverses the bytes of eachcolumn indexed (except the rowid) while keeping the column order. Such an arrangement can help avoid performance degradation with Real Application Clusters where modifications to the index are concentrated on a small set of leaf blocks. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index.把索引的字段每个字段反转，反转索引的目的就是消除热点块，提高并发区别于DES