Oracle职业直通车-06 Oracle的内存结构与后台进程

Oracle实例

SGA(System Global Area)

  • SGA区包括Oracle实例需要的一系列内存组件，用于存放共享的数据信息和数据控制信息
    • Database Buffer Cache
    • Redo Log Buffer
    • Shared Pool Library Cache、Data dictionary Cache
    • Large Pool
    • Streams Pool
    • Fixed SGA
  • 这些内存信息被所有进程所共享(server process,background process)
SQL> show sga

Total System Global Area 4375998464 bytes
Fixed Size- - - 2260328 bytes
Variable Size- -  838861464 bytes    # shared pool
Database Buffers-    3523215360 bytes
Redo Buffers- -    11661312 bytes
Oracle职业直通车

阅读全文 >>

Oracle职业直通车-05 数据字典&数据库的备份和恢复

创建一张表，在表上创建一个索引，查询表，索引各自分配了多少个extents,多少个数据块以及共占空间大小(bytes)

conn lyj/lyj
drop table t purge;
create table t as select * from all_objects;
create index ind_t_oid on t(object_id);

col segment_name for a20
select segment_name,extents,blocks,bytes/1024/1024 size_m
  from user_segments
  where segment_name='T';

SEGMENT_NAME            EXTENTS     BLOCKS     SIZE_M
-------------------- ---------- ---------- ----------
T                            23       1024          8

select segment_name,extents,blocks,bytes/1024/1024 size_m
  from user_segments
  where segment_name='IND_T_OID';

SEGMENT_NAME            EXTENTS     BLOCKS     SIZE_M
-------------------- ---------- ---------- ----------
IND_T_OID                    17        256          2
Oracle职业直通车

阅读全文 >>

Oracle职业直通车-02 从最简单的SQL语句开始

教材第二章课后作业 1，2，3，4题

创建一查询，显示与BLAKE在同一部门工作的雇员的项目和受雇日期，但是BLAKE不包含在内。

SQL> conn scott/scott
Connected.
SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

select ENAME,JOB,to_char(HIREDATE,'YYYY-mm-dd') as HIREDATE from emp 
  where DEPTNO = (select DEPTNO from emp where ENAME='BLAKE')
    and ENAME<>'BLAKE';

ENAME      JOB       HIREDATE
---------- --------- ----------
ALLEN      SALESMAN  1981-02-20
WARD       SALESMAN  1981-02-22
MARTIN     SALESMAN  1981-09-28
TURNER     SALESMAN  1981-09-08
JAMES      CLERK     1981-12-03
Oracle职业直通车

阅读全文 >>

Oracle职业直通车-01 轻松带你走进Oracle数据库的世界

使用sqlplus 启动和关闭数据库

$ sqlplus / as sysdba

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 4375998464 bytes
Fixed Size                  2260328 bytes
Variable Size             905970328 bytes
Database Buffers         3456106496 bytes
Redo Buffers               11661312 bytes
Database mounted.
Database opened.

创建用户test，密码test

create user test identified by test default tablespace users;
Oracle职业直通车