How to calculate the actual size of a table

here are the example made by Toms kyte…there is the situation maybe lots of people had made the practice,u just stand on the shoulder of giants,and u need to read it carefully,that’s enough..

and thanks a lot, Tom,u did a lot to help countless person..

*********************************************************************************

for a discussion of the use of ANALYZE and DBMS_SPACE to this end.  Basically -- you can
find out how many blocks are allocated to the table (whether used or NOT), how many
blocks have never been used (subtract them from above) and on the blocks that are used --
the average free space.

For example, in the example from above that I linked to -- we see:

ops$tkyte@DEV8I.WORLD> create table t ( x int,
                                 y char(2000) default '*' )
  2  storage ( initial 40k next 40k minextents 5 )
  3  tablespace system;

Table created.

A table that will create ~2k rows for each row inserted.  makes it easy to do the
math

ops$tkyte@DEV8I.WORLD> insert into t (x) values ( 1 );
1 row created.

ops$tkyte@DEV8I.WORLD> analyze table t compute statistics;
Table analyzed.

ops$tkyte@DEV8I.WORLD> compute sum of blocks on report
ops$tkyte@DEV8I.WORLD> break on report
ops$tkyte@DEV8I.WORLD> select extent_id, bytes, blocks
  2    from user_extents
  3   where segment_name = 'T'
  4     and segment_type = 'TABLE'
  5  /

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         2      81920         10
         3     122880         15
         4     163840         20
         0      40960          5
         1      40960          5
                      ----------
sum                           55

ops$tkyte@DEV8I.WORLD> clear breaks
ops$tkyte@DEV8I.WORLD> select blocks, empty_blocks,
  2         avg_space, num_freelist_blocks
  3   from user_tables
  4  where table_name = 'T'
  5  /

    BLOCKS EMPTY_BLOCKS  AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
         1           53       6091                   1

Ok, the above shows us:

o we have 55 blocks allocated to the table
o 53 blocks are totally empty (above the HWM)
o 1 block contains data (the other block is used by the system)
o we have an average of about 6k free on each block used.

Therefore, our table

o consumes 1 block
o of which  1block * 8k blocksize - 1 block * 6k free = 2k is used for our data.

Now, lets put more stuff in there...


ops$tkyte@DEV8I.WORLD> insert into t (x)
  2  select rownum
  3    from all_users
  4   where rownum < 50
  5  /
49 rows created.

ops$tkyte@DEV8I.WORLD> analyze table t compute statistics;
Table analyzed.

ops$tkyte@DEV8I.WORLD> compute sum of blocks on report
ops$tkyte@DEV8I.WORLD> break on report
ops$tkyte@DEV8I.WORLD> select extent_id, bytes, blocks
  2    from user_extents
  3   where segment_name = 'T'
  4     and segment_type = 'TABLE'
  5  /

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         2      81920         10
         3     122880         15
         4     163840         20
         0      40960          5
         1      40960          5
                      ----------
sum                           55

ops$tkyte@DEV8I.WORLD> clear breaks
ops$tkyte@DEV8I.WORLD> select blocks, empty_blocks,
  2         avg_space, num_freelist_blocks
  3    from user_tables
  4   where table_name = 'T'
  5  /

    BLOCKS EMPTY_BLOCKS  AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
        19           35       2810                   3

Ok, the above shows us:

o we have 55 blocks allocated to the table (still)
o 35 blocks are totally empty (above the HWM)
o 19 blocks contains data (the other block is used by the system)
o we have an average of about 2.8k free on each block used.

Therefore, our table

o consumes 19 blocks of storage in total.
o of which  19 blocks * 8k blocksize - 19 block * 2.8k free = 98k is used for our data.

Given our rowsize, this is exactly what we expected.

size of table or index…all the objects in oracle

you can query the size of an object,such as table,index….from user_extents or dba_extents

because,a segment maybe cross muti extents,look at “TEST”,you need to sum all the bytes of each extent_id to get the total size of table “TEST”

SQL> select segment_name,segment_type,tablespace_name,extent_id,bytes from user_extents;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID      BYTES
—————————— —————— —————————— ———- ———-
DEPT                           TABLE              USERS                                   0      65536
EMP                            TABLE              USERS                                   0      65536
BONUS                          TABLE              USERS                                   0      65536
SALGRADE                       TABLE              USERS                                   0      65536
TEST                           TABLE              USERS                                   0      65536
TEST                           TABLE              USERS                                   1      65536
TEST                           TABLE              USERS                                   2      65536
TEST                           TABLE              USERS                                   3      65536
TEST                           TABLE              USERS                                   4      65536
TEST                           TABLE              USERS                                   5      65536
TEST                           TABLE              USERS                                   6      65536
TEST                           TABLE              USERS                                   7      65536
TEST                           TABLE              USERS                                   8      65536
TEST                           TABLE              USERS                                   9      65536
TEST                           TABLE              USERS                                  10      65536
TEST                           TABLE              USERS                                  11      65536
TEST                           TABLE              USERS                                  12      65536
TEST                           TABLE              USERS                                  13      65536
TEST                           TABLE              USERS                                  14      65536
TEST                           TABLE              USERS                                  15      65536
TEST                           TABLE              USERS                                  16    1048576
TEST                           TABLE              USERS                                  17    1048576
TEST                           TABLE              USERS                                  18    1048576
TEST                           TABLE              USERS                                  19    1048576
TEST                           TABLE              USERS                                  20    1048576
USERS                          TABLE              USERS                                   0      65536
PK_DEPT                        INDEX              USERS                                   0      65536
PK_EMP                         INDEX              USERS                                   0      65536

28 rows selected.

SQL> select sum(bytes) from user_extents where segment_name=’TEST’;

SUM(BYTES)
———-
6291456

it’s the same as INDEX…..

analyze the memory consumption of one sql

there will be a easy sample of memory consumption caculation

check the block size of your oracle,here are 8192bytes
SQL> show parameter db_block_size;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_block_size                        integer     8192

//check the execution plan of sql
SQL> select * from scott.emp;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH      CLERK           7902 17-DEC-80        800                    20
7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
7566 JONES      MANAGER         7839 02-APR-81       2975                    20
7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
7839 KING       PRESIDENT            17-NOV-81       5000                    10
7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7900 JAMES      CLERK           7698 03-DEC-81        950                    30
7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Elapsed: 00:00:00.03

Execution Plan
———————————————————-
Plan hash value: 3956160932

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
————————————————————————–

Statistics
———————————————————-
1  recursive calls
0  db block gets
8  consistent gets
0  physical reads
0  redo size
1415  bytes sent via SQL*Net to client
385  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
14  rows processed

//8 consistent gets,you can calculate the exact memory consumption by:
//8 x 8192bytes=65536bytes=64kb

rebuild the index

sometimes, the index u created in table maybe invaild and cause the problem in query the data of table, for example:
1.”select * from tb” will export the all the data of tb
but..
2. “select * from tb where id=’222′” will hang there and no data exported,there is the index created in colume “id”

most of time, it’s the invaild of index index_name created in id colume which case the situation,just follow this maybe help
“ALTER INDEX index_name REBUILD”..

–EOF—

Oracle Validated


oracle validated is great stuff to resolve the rpm package problems on oracle, here comes the infor from online doc:
1.3.4 About the Oracle Validated Configuration RPM
If the Linux distribution is Oracle Linux, or Red Hat Enterprise Linux, and you are an Unbreakable Linux customer, then you can complete most preinstallation configuration tasks by using the Oracle Validated Configurations Setup RPM, available from the Unbreakable Linux Network (ULN), or available on the Oracle Linux DVD disks.

When it is installed, the Oracle Validated Configuration RPM does the following:

•Automatically installs any additional packages needed for installing Oracle Grid Infrastructure and Oracle Database.

•Creates an oracle user, and creates the oraInventory (oinstall) and OSDBA (dba) groups for that user

•Sets and verifies sysctl.conf settings, system startup parameters, user limits, and driver parameters to values based on recommendations from the Oracle Validated Configurations program

srvctl cmd introduce

srvctl cmd introduce
srvctl can control the listener,instance,and services of the rac
(1)you can check the status of nodes by following cmd
oracle@rac2 crs$ *srvctl status nodeapps -n rac2*
VIP is running on node: rac2
GSD is running on node: rac2
Listener is running on node: rac2
ONS daemon is running on node: rac2
//*vip,gsd,listener,ons were called “nodeapps”*

(2)check the status of instance of one of the nodes(rac1)
*srvctl start|stop|status database -d*
oracle@rac2 crs$ *srvctl status instance -d rac -i rac1*
Instance rac1 is running on node rac1

(3)stop whole rac
//to stop the entire database named “rac”
oracle@rac2 crs$ *srvctl stop database -d rac*
//three resource options were *offline*,”instance”,”db”
oracle@rac2 crs$ crs_stat -t
Name Type Target State Host
————————————————————
*ora.rac.db application OFFLINE OFFLINE*
*ora….c1.inst application OFFLINE OFFLINE*
*ora….c2.inst application OFFLINE OFFLINE*
ora….C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora….C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2

(4)check the configuration of whole rac env
oracle@rac2 crs$ *srvctl config database -d rac -a*
rac1 rac1 /u01/app/oracle/product/10.2.0/db_1
rac2 rac2 /u01/app/oracle/product/10.2.0/db_1
DB_NAME: rac
ORACLE_HOME: /u01/app/oracle/product/10.2.0/db_1
SPFILE: /u01/oradata/RAC/spfilerac.ora
DOMAIN: null
DB_ROLE: null
START_OPTIONS: null
POLICY: AUTOMATIC
ENABLE FLAG: DB ENABLED

ORA-12520: Listeners Running on VIP Address in 10g or Newer RAC [ID 342419.1]


In this Document
Symptoms
Cause
Solution
References
——————————————————————————–
Applies to:
Oracle Net Services – Version: 10.1.0.3.0 to 11.1.0.7.0 – Release: 10.1 to 11.1
Information in this document applies to any platform.
This issue is limited to 10g through 11.1.0.7. The issue described here is fixed in 11.2.
Symptoms
The REMOTE_LISTENER parameter is set to an alias which is defined in the Tnsnames.ora file on both the nodes.
In the Tnsnames.ora file , the hostname is defined with proper domain name.
The IP / VIP and hostname are properly defined in the /etc/hosts file on both nodes.
On the server when trying to log on to database using SQL*Plus, you see the following error:
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of server
Cause
This is due to Unpublished BUG: 4338578 RAC-CRITICAL: INSTANCE DOESN’T USE VIP ENDPOINT TO REGISTER WITH LISTENER.
In any 10g and newer RAC setup, a problem exists where the instances are not registering correctly with the virtual IP address. They’re attempting to register against the physical ip address instead.
Solution
To implement the solution, please execute the following steps:
1. Set LOCAL_LISTENER for each instance in the cluster. Log in as a privileged user and issue:
SQL>Alter system set LOCAL_LISTENER=”(address=(protocol=tcp)(host=your_node)(port=1521))” scope=both sid=”;
Where “your_node” is the VIP for the host where the instance is running and “instance_name” is the unique instance name for this SID.
Issue this statement for all instances in the cluster using the correct values for host and SID.

ORA-12520: TNS:listener could not find available handler for requested type of server


1.listener problem
when I try to connect rac from the client using sqlplus / @tnsname as sysdba, I got this error
*ORA-12520: TNS:listener could not find available handler for requested type of server*
it was caused by configuration of listener and tnsnames.

*refer:Subject: Ora-12520 When listeners on VIP in 10g RAC Setup Doc ID: Note:342419.1*
//following the notes, it seems that you need to set local_listener in each node in spfiles(other dba agree with me)
//set the parameter on node rac1 in local_listener and you need to config them on each nodes
//remote_listener control the function of load balance,
//local_listener helps a lot in TAF
SQL> show parameter listener;

NAME TYPE VALUE
———————————— ———– ——————————
local_listener string LISTENER_RAC2
remote_listener string LISTENERS_RAC

//section of tnsnames.ora on server side
RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.149)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.150)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
)
)

LISTENERS_RAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.149)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.150)(PORT = 1521))
)

LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.128)(PORT = 1521)(IP = FIRST))
)
)

LISTENER_RAC2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.129)(PORT = 1521)(IP = FIRST))
)
)

RAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.150)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
(INSTANCE_NAME = rac2)
)
)

RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.149)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
(INSTANCE_NAME = rac1)
)
)

//listener.ora on server side
LISTENER_RAC2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.129)(PORT = 1521)(IP = FIRST))
)
)

LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.128)(PORT = 1521)(IP = FIRST))
)
)

rac failover test

use sqlplus to connect the rac
sqlplus sys/passwod@rac as sysdba

SQL> select instance_name from v$instance;

INSTANCE_NAME
——————————–
rac2

connect rac2 and shutdown the instance
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
//from the crs_stat -t, you can see the instance of rac2 was offline
oracle@rac2 admin$ crs_stat -t
Name Type Target State Host
————————————————————
ora.rac.db application ONLINE ONLINE rac1
ora….c1.inst application ONLINE ONLINE rac1
ora….c2.inst application OFFLINE OFFLINE
ora….C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora….C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2

//go to the session just created before,execute the select,you will get the instance rac1 which it fail over switch automatically from
//rac2 to rac1
SQL> select instance_name from v$instance;

INSTANCE_NAME
——————————–
rac1

//here are the configuration of tnsnames.ora,listener.ora on server side and the tnsnames.ora on client side
***************************************************************************
##listener.ora on shared NFS
LISTENER_RAC2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.129)(PORT = 1521)(IP = FIRST))
)
)

LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.128)(PORT = 1521)(IP = FIRST))
)
)

*********************************************************************************
#in tnsnames.ora,LISTENER_RAC1 and LISTENER_RAC2 were manul added, they are the entrance for local_listener=’LISTENER_RAC1′

RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.149)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.150)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
)
)

LISTENERS_RAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.149)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.150)(PORT = 1521))
)

LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.128)(PORT = 1521)(IP = FIRST))
)
)

LISTENER_RAC2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.129)(PORT = 1521)(IP = FIRST))
)
)

RAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.150)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
(INSTANCE_NAME = rac2)
)
)

RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.149)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
(INSTANCE_NAME = rac1)
)
)

*****************************************************
//following are the client side tnsnames.ora key section

*******************************************************
RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.149)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.150)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
*******************************************************

oracle单实例 ASM归档变更


#asmcmd这个东西非常好,简化了管理员对于ASM相关的操作

#ASM实例开启归档
ASM开启归档与普通的一样,只是归档日志的目录需要变化。
1.先查询好准备的归档日志的挂载点目录
ASMCMD> pwd
+DISKGROUP2/TEST/ARCH

2.更改命令
alter system set log_archive_dest_1=’LOCATION=+DISKGROUP2/ARCH’ scope=spfile

3.更改归档
SQL> alter system set log_archive_dest_1=’LOCATION=+DISKGROUP2/ARCH’ scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267044 bytes
Variable Size 109054620 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DISKGROUP2/test/arch
Oldest online log sequence 16
Next log sequence to archive 18
Current log sequence 18

//手工归档一次
SQL> alter system archive log current;

System altered.

//查看一下归档的效果,在+DISKGROUP2/ARCH下面有了手工归档出来的归档日志文件
ASMCMD> pwd
+DISKGROUP2/ARCH
ASMCMD> ls
1_19_740941867.dbf

//如果你在配置磁盘的时候配置了冗余,那么你会在如下目录找到一个备份,Redund便是冗余的提示信息,通常情况下会在磁盘组中创建如下
//的缺省目录,+$DISKGROUP_NAME/ORACLE_SID/ARCHIVELOG,当然如果你创建了一个数据文件,在平级的目录中也会出现DATAFILE的关键字目录,与
//ARCHIVELOG平级
ASMCMD> pwd
+DISKGROUP2/TEST/ARCHIVELOG/2011_01_28
ASMCMD> ls -lrt
Type Redund Striped Time Sys Name
ARCHIVELOG MIRROR COARSE JAN 28 13:00:00 Y thread_1_seq_18.258.741619145
ARCHIVELOG MIRROR COARSE JAN 28 13:00:00 Y thread_1_seq_19.259.741619671

ASMCMD> pwd
+DISKGROUP2/TEST
ASMCMD> ls
ARCH/
ARCHIVELOG/
DATAFILE/

PS:dba_extents是个不错的东西,dba可以用它查询某个用户对象的磁盘占用开销情况

SQL> select owner,segment_name,bytes from dba_extents where owner=’SCOTT’;

OWNER SEGMENT_NAME BYTES
—————————— —————————— ———-
SCOTT DEPT 65536
SCOTT EMP 65536
SCOTT BONUS 65536
SCOTT SALGRADE 65536
SCOTT PK_DEPT 65536
SCOTT PK_EMP 65536

IMP-00041


今天在客户那边做数据迁移的时候遇到一些问题,主要是:IMP-00041: 警告: 创建的对象带有编译警告
查了一下原因,大致如下,以后再做数据迁移的时候需要额外注意,尤其用户中有view对象的时候。
用户的环境是这样的,在库里有三个oracle的用户,其中一个用户中有几张视图是引用其他用户的,所以
在数据导入的时候,需要导入”独立性最好”的用户对象,什么是”独立性最好”?大概的意思是自相关,自己只和自己
打交道没有引用其他用户的对象。如果顺序错了,自然会出现”IMP-00041: 警告: 创建的对象带有编译警告”类似的错误,
可能是数据导入的过程中,用户有view对象,而view需要引用的对象在其他用户里。
除此之外,如果是夸用户的对象,还需要额外注意权限的问题。及时用户具备DBA的角色,在view中,仍需显示的
“grant select.. on…”来给用户授权,否则也会出现imp-00041,也许在你recompile之后,仍然无法解决,那么,那么
drop掉视图,重新创建吧。。
–EOF–

oracle priviledges


我们知道”系统权限”的信息,如果你用sys / as sysdba登陆的话,可以通过查询dba_sys_privs来查询,
而”角色”则可以通过dba_role_privs来查询,这两个是我比较熟悉的,也同时是经常被使用的。而例如某个用户
test被赋予了另外用的视图的访问权限,如何来查这些被授予的权限?可以通过dba_tab_privs来查询
SQL> desc dba_tab_privs;
名称 是否为空? 类型
—————————————————————– ——– ————————–
GRANTEE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
GRANTOR NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)

SQL> select * from dba_tab_privs where grantee=’USER’;
这样就能得到USER的对象权限,例如能够访问的表、视图等等

所以如下三个视图可以一起来记忆,主要就是涉及到广义的权限,系统权限,角色,对象权限
dba_sys_privs

dba_role_privs

dba_tab_privs

[zz]5种开源虚拟化技术推荐


5种开源虚拟化技术推荐
12人关注此资讯, 我要关注(收藏)(?) | 新闻投递 老枪 发布于: 2011年01月21日 (4评)

虚拟化现在已经成为主流技术,对于大企业来说。大玩家包括EMC、IBM、微软,当然他们致力于专利软件的推广。下面我们就看看有哪些不错的开源虚拟化软件。

1、 开源虚拟机 KVM
KVM

KVM (全称是 Kernel-based Virtual Machine) 是 Linux 下 x86 硬件平台上的全功能虚拟化解决方案,包含一个可加载的内核模块 kvm.ko 提供和虚拟化核心架构和处理器规范模块。

使用 KVM 可允许多个包括 Linux 和 Windows 每个虚拟机有私有的硬件,包括网卡、磁盘以及图形适配卡等。

2、 开源虚拟机 Xen

Xen 是一个开放源代码虚拟机监视器,由剑桥大学开发。它打算在单个计算机上运行多达100个满特征的操作系统。操作系统必须进行显式地修改(“移植”)以在Xen上运行(但是提供对用户应用的兼容性)。这使得Xen无需特殊硬件支持,就能达到高性能的虚拟化。

3、 Linux虚拟化技术 OpenVZ

OpenVZ是基于Linux内核和作业系统的操作系统级虚拟化技术。OpenVZ允许物理服务器运行多个操作系统,被称虚拟专用服务器(VPS,Virtual Private Server)或虚拟环境(VE, Virtual Environment)。

与VMware这种虚拟机和Xen这种半虚拟化技 术相比,OpenVZ的host OS和guest OS都必需是Linux(虽然在不同的虚拟环境里可以用不同的Linux发行版)。但是,OpenVZ声称这样做有性能上的优势。根据OpenVZ网站的 说法,使用OpenVZ与使用独立的服务器相比,性能只会有1-3%的损失。

OpenVZ是SWsoft, Inc.公司开发的专有软件Virtuozzo的基础。OpenVZ的授权为GPLv2。

OpenVZ由两部分组成,一个经修改过的操作系统核心与及用户工具。

4、开源虚拟机 VirtualBox

VirtualBox 是一款功能强大的 x86 虚拟机软件,它不仅具有丰富的特色,而且性能也很优异。更可喜的是,VirtualBox 于数日前走向开源,成为了一个发布在 GPL 许可之下的自由软件。

5、Lguest

http://lguest.ozlabs.org/lguest-logo.png

Lguest 是由IBM工程师Rusty Russell(澳大利亚开发者)发起的虚拟化项目,是一个只有5000行代码的精简hypervisor(虚拟机管理程序),它已经包括在最近版本的内核里了。和KVM相似,它支持 Intel和AMD芯片的最新虚拟化技术。但又与VMware公司的ESX Server不同,在Lguest创建的虚拟机里的操作系统知道自己是被虚拟出来的。所以在调用CPU周期时它可以直接向真正的硬件发出请求,而不是作为中间媒介而降低了效率,因此这种架构大大提高了效率。Lguest采用GPL授权。