表空间

1.添加数据库表空间

create tablespace student
datafile 'J:/my project/oracle/work/student.dbf'
size 1m
autoextend on
create tablespace teacher
datafile 'J:/my project/oracle/work/teacher.dbf'
size 1m
autoextend on
create tablespace manager
datafile 'J:/my project/oracle/work/manager.dbf'
size 1m
autoextend on
create tablespace worker
datafile 'J:/my project/oracle/work/worker.dbf'
size 1m
autoextend on

2. 为已经有的表 加空间文件

alter tablespace student
add datafile 'J:/my project/oracle/work/student1.dbf'
size 1m
autoextend on
alter tablespace teacher
add datafile 'J:/my project/oracle/work/teacher1.dbf'
size 1m
autoextend on

3. 创建临时表空间

create temporary tablespace tmp001
tempfile 'j:/my project/oracle/work/tmp001.dbf'
size 5m
autoextend on

create temporary tablespace temp002
tempfile 'j:/my project/oracle/work/temp002.dbf'
size 5m
autoextend on

4. 手工改变文件的大小

alter database datafile 'j:/my project/oracle/work/student.dbf'
resize 2m
alter database datafile 'j:/my project/oracle/work/teacher.dbf'
resize 2m
alter database datafile 'j:/my project/oracle/work/manager.dbf'
resize 2m

5. 添加表空间文件

alter tablespace student
add datafile 'j:/my project/oracle/work/student2.dbf'
size 1m
autoextend on

6. 数据文件命名

alter tablespace student offline

alter tablespace student
rename datafile 'j:/my project/oracle/work/student.dbf' to 'j:/my project/oracle/work/student3.dbf'
alter tablespace student online

7. 添加用户名

create user peter
identified by peter
grant connect to peter

8. 修改用户密码

alter user peter
identified by "abc"

9.修改用户密码为111111

alter user peter
identified by "111111"

10.使用在第一题中建立的表空间student为默认表空间分别创建用户stud_ajh 密码为stud_ajh

create user stud_ajh
identified by stud_ajh
default tablespace student

11.使用在第一题中建立的表空间student为默认表空间,以tmp001表空间为临时表空间分别创建用户teacher_ajh 密码为abc.

create user teacher_ajh
identified by abc
temporary tablespace tmp001

12.使用用户ajh来连接oracle数据库，看是否成功。

grant connect to stud_ajh

13.使用system用户为ajh授权Connect，再重复12的操作，检查是否成功。

grant connect to stud_ajh

14.当使用ajh建立到数据库后，运行语句select * from tab;检查是否有记录，语句是否可以执行?

可以执行

15.输入create table account(acct varchar2(20);看能否创建表，原因是什么?

可以

16.数据库有用的表

select * from user_all_tables
select * from user_tablespaces
select * from all_all_tables
select * from dba_

17.删除用户stud,当它有对象是应如何删除呢？(cascade),请操作一遍

drop user stud_ajh cascade

18.检查当前用户是什么

show user