Oracle教程:oracle 创建表空间,表,以及用户
来源:学生作业帮助网 编辑:作业帮 时间:2024/05/10 22:31:48 Oracle
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 240
UNDOTBS1 25
TEST_DATA 32
USERS 5
SYSTEM 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 TRIGGER
RESOURCE CREATE SEQUENCE
RESOURCE CREATE TYPE
RESOURCE CREATE PROCEDURE
RESOURCE CREATE CLUSTER
RESOURCE CREATE OPERATOR
RESOURCE CREATE INDEXTYPE
RESOURCE 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