ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle学习系列—Oracle表空间和用户的手工创建

Oracle学习系列—Oracle表空间和用户的手工创建

原创 Linux操作系统 作者:bq_wang 时间:2007-03-26 00:00:00 0 删除 编辑

尝试以手工方式的形式创建用户表空间及其数据文件,创建用户,分配权限,并以用户身份登陆,进行验证。

同时复习了一下数据库的归档(手工创建数据库后,默认为非归档方式)


数据库的归档重新复习,关于归档的参数设置可以通过alter system参数直接完成。

以系统管理员身份登陆数据库

关闭数据库

启动数据库到mount状态

更改归档方式

查看归档状态

修改归档目录

修改归档启动方式

关闭数据库

重新启动数据库

查看归档状态

归档联机日志

SQL*Plus: Release 9.2.0.1.0 - Production on Sun Mar 18 11:58:12 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys/change_on_install as sysdba

Connected.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 105978600 bytes

Fixed Size 453352 bytes

Variable Size 79691776 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination C:/oracle/ora92/RDBMS

Oldest online log sequence 22

Current log sequence 24

SQL> alter system set log_archive_dest='c:/Oracle/ora92/database/archive';

System altered.

SQL> alter system set log_archive_start=true scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 105978600 bytes

Fixed Size 453352 bytes

Variable Size 79691776 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Disabled

Archive destination c:/Oracle/ora92/database/archive

Oldest online log sequence 22

Next log sequence to archive 24

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

创建用户表空间

增加用户表空间的数据文件

创建用户

为用户分配缺省表空间

为用户分配临时表空间

为用户分配权限

以用户身份登陆

以用户身份创建数据表

SQL> CREATE TABLESPACE mytablespace

2 DATAFILE 'c:/oracle/oradata/wbq/mydatablespace01.dbf' SIZE 50M

3 AUTOEXTEND ON NEXT 10M MAXSIZE 200M,

4 'c:/oracle/oradata/wbq/mydatablespace02.dbf' SIZE 50M

5 AUTOEXTEND ON NEXT 10M MAXSIZE 200M

6 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Tablespace created.

SQL> ALTER TABLESPACE mytablespace ADD

2 DATAFILE 'c:/oracle/oradata/wbq/mydatablespace03.dbf' SIZE 50M

3 AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

Tablespace altered.

SQL> CREATE USER wbq IDENTIFIED BY wbq;

User created.

SQL> alter user wbq identified by wbq

2 default tablespace mytablespace;

User altered.

SQL> alter user wbq identified by wbq

2 temporary tablespace temp;

User altered.

SQL> grant dba to wbq;

Grant succeeded.

SQL> grant connect to wbq;

Grant succeeded.

SQL> connect wbq/wbq ;

Error accessing PRODUCT_USER_PROFILE

Warning: Product user profile information not loaded!

You may need to run PUPBLD.SQL as SYSTEM

Connected.

SQL> create table test

2 (

3 id int,

4 name varchar2(20)

5 );

Table created.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6517/viewspace-145511/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    412
  • 访问量
    1104620