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

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

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

数据库的归档重新复习，关于归档的参数设置可以通过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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6517/viewspace-145511/