OCP笔记

Oracle数据库内存结构

•shared pool

alter system set shared_pool_size=100m;

show parameter shared_pool_sizw;

•buffer cache

show parameter db_block_size;

show parameter db_cache_size;

show sga;

alter system set db_chche_size=200m

alter system set db_cache_advice=off; -- off on ready show parameter db_cache_advice;

show parameter log_buffer;

alter system set log_buffer=10m;

show parameter sort_area_size;

show sga;

show parameter sga_max_size;

archive log list;

shutdown immediate;--开归档

startup mount;

alter database archivelog;

show parameter db_recovery

数据操作语句DML

insert update detete

数据定义语句

create alter drop rename truncate

事务控制语句

commit rollback

数据控制语句

grant revoke

数据字典

数据字典视图：静态 dba(table,index,view,object) all user_ desc dba_object;

动态性能视图

v$fixed_table --该试图包含所有视图

oracle的网络连接

show paramerter service_name;

show parameter local_listener;

alter system set local_listener='(address_list=(address=(protocol=tcp) (host=192.168.1.5)(port=1522)))';

lsnrctl stop

lsnrctl start

lsnrctt status

oracle服务器的两种连接方式

•专有

•共享

共享涉及的参数

•dispatchers 配置进程信息，如协议、进程数量、端口

alter system set dispatchers='(protocol=tcp)(dispatchers=3)';

dispatchers='(address=(protocol=tcp)(host=172.168.2.1)(dis patchers=2))' alter system set max_dispatchers=5;

alter system set shared_servers=5;

alter system set max_shared_servers=20;--进程数量

alter system set shared_servers_sessions=100; --共享会话数量

show parameter sessions;

内存管理

alter system set memory_target=1000m scope=both;

show parameter area_size;

用户资源管理

create user jane identified by american default tablespace users tempor ary tablespace temp quota 10 on users password expire;

grant create session,resourceto jane;

alter user jane quota 20m on users;

alter user jane default tablesapce newtbs quota unlimited on system; alter user jane quota 0 on users;

drop user jane cascade;--cascade 删除用户相关对象

alter user scott identified by tiger account unlock;

用户和数据库模式

模式对象：表、触发器、约束、索引、视图、序列号、存储过程、同义词、用户定义的数据类型、函数、程序包

概要文件

create profile scott_prof limit sessions_per_user 10

cpu_per_session 10000

idle_time 10

connect_time 120;

select * from dba_profiles;

alter profile password_prof limit failed_login_attempts=3 password_life_time=60

password_grace_time=7;

drop profile password_prof;

控制文件和数据库的启动