Oracle数据库实验笔记

SQL> alter system set sga_max_size=200M scope=spfile;

系统已更改。

SQL> startup force ORACLE 例程已经启动。

Total System Global Area 230686720 bytes Fixed Size 1248236 bytes Variable Size 75498516 bytes Database Buffers 146800640 bytes Redo Buffers 7139328 bytes 数据库装载完毕。 数据库已经打开。 SQL> show parameter sga

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 220M sga_target big integer 220M SQL> alter system set sga_max_size=300M scope=spfile;

系统已更改。

SQL> startup force ORACLE 例程已经启动。

Total System Global Area 314572800 bytes Fixed Size 1248768 bytes Variable Size 159384064 bytes Database Buffers 146800640 bytes Redo Buffers 7139328 bytes 数据库装载完毕。 数据库已经打开。 SQL> show parameter sga

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 300M

sga_target big integer 220M SQL> alter system set sga_target=290M;

系统已更改。

SQL> show parameter sga

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 300M sga_target big integer 292M SQL> select * from v$sgainfo;

NAME BYTES RES -------------------------------- ---------- ---

Fixed SGA Size 1248768 No Redo Buffers 7139328 No Buffer Cache Size 222298112 Yes Shared Pool Size 67108864 Yes Large Pool Size 4194304 Yes Java Pool Size 4194304 Yes Streams Pool Size 0 Yes Granule Size 4194304 No Maximum SGA Size 314572800 No Startup overhead in Shared Pool 37748736 No Free SGA Memory Available 8388608

已选择11行。

SQL> select COMPONENT,CURRENT_SIZE from v$sga_dynamic_components where CURRENT_S IZE <> 0;

COMPONENT CURRENT_SIZE ------------------------------ ------------

shared pool ########## large pool ########## java pool ########## DEFAULT buffer cache ########## SQL>

*****************************表空间**************************************

SQL> create user u1 identified by u1 2 default tablespace users --10g 3 temporary tablespace temp --9i 4 quota 10M on users 5 password expire 6 SQL>

SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users whe

re username='U1';

未选定行

SQL> create user u1 identified by u1 2 default tablespace users --10g 3 temporary tablespace temp --9i 4 quota 10M on users 5 password expire 6 ;

用户已创建。

SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users wher e username='U1';

USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ TEMPORARY_TABLESPACE ------------------------------

U1 USERS TEMP

SQL> select temporary_tablespace from dba_users where username='SCOTT';

TEMPORARY_TABLESPACE ------------------------------ TEMP

SQL> create temporary tablespace temp2 tempfile 'D:\\oracle\\product\\10.2.0\\oradat a\\orcl\\TEMP02.DBF' size 10M;

表空间已创建。

SQL> alter user scott temporary tablespace temp2;

用户已更改。

SQL> create temporary tablespace temp03 tempfile 'D:\\oracle\\product\\10.2.0\\orada ta\\orcl\\TEMP.DBF' size 10M;

表空间已创建。

SQL> select name from V$datafile; NAME

--------------------------------------------------------------------------------

D:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\SYSTEM01.DBF D:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\UNDOTBS01.DBF D:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\SYSAUX01.DBF D:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\USERS01.DBF D:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\EXAMPLE01.DBF

SQL> create temporary tablespace temp04 tempfile 'D:\\test\\temp1.dbf' size 10M;

表空间已创建。

SQL> select TABLESPACE_NAME,CONTENTS from dba_tablespaces;

TABLESPACE_NAME CONTENTS ------------------------------ ---------

SYSTEM PERMANENT UNDOTBS1 UNDO SYSAUX PERMANENT TEMP TEMPORARY USERS PERMANENT EXAMPLE PERMANENT TEMP2 TEMPORARY