oracle实验课作业

 

1，创建一个学生用户，并为之授予权限。

sqlplus system/sias
drop user stu01 cascade;
create user stu01 identified bystu01
default tablespce users
temporary tablesapce temp;
  create role r_stu;
grant create session,create cluster,create database link,
      create any index,create materialized view,create procedure,
      create sequence,create synonym,create table,crete trigger,
      create type,create view
    to r_stu;
grant r_stu to stu01;

 

conn stu01/stu01
column username format A10
select username,granted_role FROM user_role_privs;

conn system/sias
drop role r_stu;
drop user stu01 cascade;

2，管理表空间。

conn system/sias
drop tablespace data_ts1 INCLUDING CONTENTS;
create tablespace data_ts1
  datafile '%ORACLE_HOME%\database\data_ts1.dbf' SIZE 50M reuse
  UNIFORM SIZE 128K;

CONN system/sias
drop tablespace temp_ts1 INCLUDING CONTENTS;
create temporary tablespace temp_ts1
  tempfile '%ORACLE_HOME%\database\temp_ts1.dbf' SIZE 20M reuse
  uniform size128k;

CONN system/sias
drop user emp_mgr10 csscade;
create user emp_mgr10 identified by emp_mgr10pwd
 default tablespace data_ts1 temporary tablespace temp_ts1;
grant connnet to emp_mgr10;
connscott/tiger
alter user scott account unlock;
alter user scott identified by tiger;
grant select on emp TO emp_mgr10;

conn system/sias
drop tablespace undo_ts1 INCLUDING CONTENTS;
create undo tablespace undo_ts1
  datafile '%ORACLE_HOME%\database\undo_ts1.dbf' SIZE 50M REUSE;
alter system  set  undo_tablespace = undo_ts1;

conn system/sias
drop tablespace bigfile_ts1 including contents;
create bigfile tablespace bigfile_ts1
  datafile '%ORACLE_HOME%\database\bigfile_ts1.dbf' SIZE 50M REUSE;
alter user scott defaule tablespace bigfile_ts1;

set pagesize 30
col tablespace_name format A12
select tablespace_name,block_size,segment_space_management,
       status,contents,allocation_type,bigfile
 from dba_tablespaces;

conn system/sias
talter tablespace data_ts1
 add database '%ORACLE_HOME%\database\data_ts2.dbf' SIZE 1M;
alter datafile
  datafile '%ORACLE_HOME%\database\data_ts2.dbf' RESIZE 2M ;
alter database datafile '%ORACLE_HOME%\database\data_ts2.dbf'
  datoextend on next 1M maxsize 10M;

COL file_name FORMAT A55
SELECT file_id, file_name, tablespace_name
  FROM dba_data_files
  ORDER BY file_id;

conn system/sias
drop tablespace  temp_ts1
  including contents and datafiles
  cascade constraints;