oracle 创建表空间表以及用户

2012-10-17来源 : 互联网

oracle要创建表要*先创建表空间，当然默认是有表空间的。而mysql创建表时，会自动创建表空间，myisam会自动建三个文件.MYD,.MYI,.frm。innodb呢，如果没有配置*立表空间的话，默认表空间ibdata1，oracle用户权限根着表空间走的，而mysql不是，mysql服务端安装好后，会自动带上一个mysql数据库，用户的权限是记录在mysql这个数据库的表中。在这方面二种数据库设计的理念不同。

1,创建临时表空间

1.SQL> create temporary tablespace test_temp 2. 2 tempfile '/opt/ora10/oradata/test/test_tmp.dbf' 3. 3 size 32m 4. 4 autoextend on 5. 5 next 32m maxsize 148m 6. 6 extent management local; 7. 8.Tablespace created. SQL> create temporary tablespace test_temp 2 tempfile '/opt/ora10/oradata/test/test_tmp.dbf' 3 size 32m 4 autoextend on 5 next 32m maxsize 148m 6 extent management local;

Tablespace created.2,创建表空间

1.SQL> create tablespace test_data 2. 2 logging 3. 3 datafile '/opt/ora10/oradata/test/test_data.dbf' 4. 4 size 32m 5. 5 autoextend on 6. 6 next 32m maxsize 148m 7. 7 extent management local; 8. 9.Tablespace created. SQL> create tablespace test_data 2 logging 3 datafile '/opt/ora10/oradata/test/test_data.dbf' 4 size 32m 5 autoextend on 6 next 32m maxsize 148m 7 extent management local;

Tablespace created.3,创建表用户，并表空间分配给用户

1.SQL> create user tank identified by tank 2. 2 default tablespace test_data 3. 3 temporary tablespace test_temp; 4. 5.User created. SQL> create user tank identified by tank 2 default tablespace test_data 3 temporary tablespace test_temp;

User created.4,查看创建的表空间

1.SQL> select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size 2. 2 from dba_tablespaces t, dba_data_files d where 3. 3 t.tablespace_name = d.tablespace_name group by t.tablespace_name; 4. 5.TABLESPACE_NAME TS_SIZE 6.------------------------------ ---------- 7.SYSAUX 240 8.UNDOTBS1 25 9.TEST_DATA 32 10.USERS 5 11.SYSTEM 480 SQL> select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size 2 from dba_tablespaces t, dba_data_files d where 3 t.tablespace_name = d.tablespace_name group by t.tablespace_name;

TABLESPACE_NAME TS_SIZE------------------------------ ----------SYSAUX 240UNDOTBS1 25TEST_DATA 32USERS 5SYSTEM 4805,查看临时表空间

1.SQL> select sum(bytes)/1024/1024 "temp size(M)" from dba_temp_files where tablespace_name='TEST_TMP'; 2. 3.temp size(M) 4.------------ SQL> select sum(bytes)/1024/1024 "temp size(M)" from dba_temp_files where tablespace_name='TEST_TMP';

temp size(M)------------6,将表空间的部分操作权限分配给用户

1.SQL> grant connect,resource to tank; 2. 3.Grant succeeded. SQL> grant connect,resource to tank;

Grant succeeded.7,resource这是一个权限组，有这个组就不要一个一个加了。

1.SQL> select GRANTEE,PRIVILEGE from DBA_SYS_PRIVS where GRANTEE ='RESOURCE'; 2. 3.GRANTEE PRIVILEGE 4.------------------------------ ---------------------------------------- 5.RESOURCE CREATE TRIGGER 6.RESOURCE CREATE SEQUENCE 7.RESOURCE CREATE TYPE 8.RESOURCE CREATE PROCEDURE 9.RESOURCE CREATE CLUSTER 10.RESOURCE CREATE OPERATOR 11.RESOURCE CREATE INDEXTYPE 12.RESOURCE CREATE TABLE 13. 14.8 rows selected. SQL> select GRANTEE,PRIVILEGE from DBA_SYS_PRIVS where GRANTEE ='RESOURCE';

GRANTEE PRIVILEGE------------------------------ ----------------------------------------RESOURCE CREATE TRIGGERRESOURCE CREATE SEQUENCERESOURCE CREATE TYPERESOURCE CREATE PROCEDURERESOURCE CREATE CLUSTERRESOURCE CREATE OPERATORRESOURCE CREATE INDEXTYPERESOURCE CREATE TABLE

8 rows selected.以下操作我都是能以tank这个用户进行操作的

9,创建表

1.create table test( 2.id number(10) not null primary key, 3.name varchar(2) null , 4.city number(1) null 5.); create table test(id number(10) not null primary key,name varchar(2) null ,city number(1) null);10,创建临时表

1.CREATE GLOBAL TEMPORARY TABLE test1 ( 2.id number(10) not null primary key, 3.name varchar(2) null , 4.city number(1) null 5.); CREATE GLOBAL TEMPORARY TABLE test1 (id number(10) not null primary key,name varchar(2) null ,city number(1) null);11,查看表

1.SQL> select distinct table_name from user_tables; 2. 3.TABLE_NAME 4.------------------------------ 5.TEST 6.TEST1 7. 8.//或者 9.SQL> select table_name from dba_tables where owner='TANK'; 10. 11.TABLE_NAME 12.------------------------------ 13.TEST 14.TEST1