Oracle作业1

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

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

2.创建表空间test_space

create tablespace test_spaceloggingdatafile \'e:/test_space01.dbf\'size 50mreuse autoextend onnext 10m maxsize 200mextent management local;

3.创建用户名test

create user test identified by test123default tablespace test_spacetemporary 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 mytest1add(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 tomy_address;alter table mytest1 modify my_addressnvarchar2(50);

8.“my_test1”删除address和tel列

alter table mytest1drop column my_address;alter table mytest1drop column tel;

9.删除“my_test1”

drop table mytest1;

10.删除表空间test_space

drop tablespace TEST_SPACE;

11.删除用户test

drop user test;

12.删除数据库

set oracle_sid=mydbsqlplus /nologconn /as sysdbaselect instance_name from v$instance;startup restrict force mount;alter system enable restricted session;drop database;