Oracle作业1

1.使用命令创建一个数据库MYtest

//设定实例标识符：
SET ORACLE_SID=mytest
//用dbca创建数据库实例mytest
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname mytest -sid mytest -responseFileNO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration LOCAL

2.创建表空间test_space

create tablespace test_space
logging
datafile 'e:/test_space01.dbf'
size 50m 
reuse autoextend on
next 10m maxsize 200m
extent management local;

3.创建用户名test

create user test identified by test123
default tablespace test_space
temporary tablespace temp;
//添加权限：
grant dba to test;
alter user test default role dba;
grant unlimited tablespace to test;
alter user test quota unlimited on test_space;

4.创建一个表“my_test1”,方案为test 字段如下

create table mytest1
(
id number(11) not null, 
    name varchar2(20) not null,
    sex char(2) not null,
    constraint pk_id primary key(id)
)

5.使用过滤器，只显示my_命名的表

6.对“my_test1”添加几列，列如下（通命令面操作）

alter table mytest1
add
(
    QQ varchar2(11) not null,
    email varchar2(20) not null,
    tel varchar2(11) not null,
    address varchar2(20) not null
)

7.修改address列名为“my_address” ,类型改为nvarchar2(50)

alter table mytest1 rename column address to
my_address;
alter table mytest1 modify my_address
nvarchar2(50);

8.“my_test1”删除address和tel列

alter table mytest1
drop column my_address;
alter table mytest1
drop column tel;

9.删除“my_test1”

drop table mytest1;

10.删除表空间test_space

drop tablespace TEST_SPACE;

11.删除用户test

drop user test;

12.删除数据库

set oracle_sid=mydb
sqlplus /nolog
conn /as sysdba
select instance_name from v$instance;
startup restrict force mount;
alter system enable restricted session; 
drop database;