嘗試以手工方式的形式建立使用者表空間及其資料檔案，建立使用者，分配許可權，並以使用者身份登陸，進行驗證。

同時複習了一下資料庫的歸檔（手工建立資料庫後，預設為非歸檔方式）

資料庫的歸檔重新複習，關於歸檔的引數設定可以透過alter system引數直接完成。

以系統管理員身份登陸資料庫

關閉資料庫

啟動資料庫到mount狀態

更改歸檔方式

檢視歸檔狀態

修改歸檔目錄

修改歸檔啟動方式

關閉資料庫

重新啟動資料庫

檢視歸檔狀態

歸檔聯機日誌

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

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.

Oracle學習系列—Oracle表空間和使用者的手工建立

相關文章