ITPub博客

@A机create table test1 as select OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,status,CREATED from user_objects;delete from test1 where object_id is null;commit;alter table test1 add constraint pk_OBJECT_ID primary key (OBJECT_ID) using index;Create snapshot log on test1;@B机create database link lnk_dwisd connect to etl identified by Loadlif5 using 'isddw';Create snapshot sn_test1 as Select * From test1@lnk_dwisd;Alter snapshot sn_test1 refresh fast Start with sysdate+1/1440 next sysdate+1/144;SQL> set s

闲来无事,想算一下我们的dw有多少数据量(不看最大表记录条数，或总共有多少记录行),只看segment占用了多少bytesSQL> select sum(BLOCKS) from seg$;SUM(BLOCKS)----------- 1.9352E+10由于是8k的block,算下来有144.183635711669921875TB这不包括cf,redo,archive.基于oracle rac这个量级的数据量在亚洲不知能否进前10名? [@more@]

在ASM中，如果数据字典数据有不一致的地方，可能会引一系列问题:1. datafile resize时无法从asm diskgroup分配空间 diskgroup中的disk的free space显示是假的,实际上可能并不均衡,所以无法分配空间2,在diskgroup上做rebalance操作时，可能会导致asm instance中断.所以，有必要定期检查metadata是否有不一致的地方.check_diskgroup_clauseThe check_diskgroup_clause lets you verify the internal consistency of Automatic Storage Management disk group metadata. The disk group must be mounted. Automatic Storage Management di

SQL> alter database tempfile '/oracle/app/oradata/bic/temp01.dbf' resize 33G;alter database tempfile '/oracle/app/oradata/bic/temp01.dbf' resize 33G*ERROR at line 1:ORA-01144: File size (4325376 blocks) exceeds maximum of 4194303 blocks$oerr ora 114401144, 00000, "File size (%s blocks) exceeds maximum of %s blocks"// *Cause: Specified file size is larger than maximum allowable size value.// *Action: Specify a smaller size.在db_block_size=8192时，最大块数是4194303 大概是32GBSQL>

When you create or resize tempfiles, they are not always guaranteed allocationof disk space for the file size specified. On certain file systems (for example,UNIX) disk blocks are allocated not at file creation or resizing, but before theblocks are accessed.Caution: This enables fast tempfile creation and resizing;however, the disk could run of space later when the tempfiles areaccessed.在创建或resize后，接下来做一个大的排序操作，让空间得到分配，这样真正到了用的时候就不会花时间在allocate上了. 并

Delete, update两类语句可以增加block中的free space释放的空间在下面的条件下可以被随后的insert使用1) insert与释放空间的delete/update在同一个事务中,insert在后2) insert与delete/update在不同事务中, 只有当delete/update所在事务提交后，insert才能使用释放的空间block中释放的空间可能不连续, oracle在下面两个条件满足时会愈合块中碎片1) update/insert语句尝试使用这个块在存储 a new row piece，而这个块包含有足够的free space(算上连续空间与碎片空间)2) 因为不连续,这个new row piece无法insert进来这种做的原因是为了性能考

Free space can be managed automatically or manually.Free space can be managed automatically inside database segments. The in-segment free/used space is tracked using bitmaps, as opposed to free lists. Automatic segment-space management offers the following benefits:.Ease of useBetter space utilization, especially for the objects with highly varying row sizesBetter run-time adjustment to variations in concurrent accessBetter multi-instance behavior in terms of performance/space utilizationYou spe

Although you can allocate additional extents, the blocks themselves are allocated separately. If you allocate an extent to a specific instance, the blocks are immediately allocated to the free list. However, if the extent is not allocated to a specific instance, then the blocks themselves are allocated only when the high water mark moves. The high water mark is the boundary between used and unused space in a segment.分配extent时,blocks不是马上分配,分成两种情况:1, 如果extent分配到具体的instance(r