TableSpace
表空间 一个表空间对应多个数据文件(物理的dbf文件) 用语法方式创建tablespace用sysdba登陆 创建表空间mytabs大小为MB:
create tablespace mytabs datafile C:\oracle\oradata\mydb\mytabsdbf size M;alter user zgl default tablespace mytabs;把tabs做为zgl的默认表空间grant unlimited tablespace to zgl; 将操作表空间的权限给zgl
Exception 示例
create or replace procedurepro_test_exception(vid in varchar) isuserName varchar();beginselect name into userName from t_user where id=vid;dbms_outputput_line(userName);exceptionwhen no_data_found thendbms_outputput_line(没有查到数据！);when too_many_rows thendbms_outputput_line(返回了多行数据！); end pro_test_exception;
安全管理
以下语句以sysdba登陆 用户授权 alter user zgl account lock;锁定帐号 alter user zgl identified by zgl;修改用户密码 alter user zgl account unlock;解除帐号锁定 alter user zgl default tablespace tt;修改用户zgl的默认表空间为tt create user qqq identified by qqq default tablespace tt;创建用户
grant connect to qqq;给qqq授予connect权限 grant execute on zglproc to test;将过程zglproc授予用户test grant create user to zgl;给zgl授予创建用户的权限 revoke create user from zgl;解除zgl创建用户的权限
角色授权 create role myrole;创建角色myrole grant connect to myrole;给myrole授予connect权限 grant select on zglt_user to myrole;把查询zglt_user的权限授予myrole grant myrole to test;把角色myrole授予test用户
概要文件(配置文件) 全局设置可以在概要文件中设置登陆次数如超过这次数就锁定用户
Synonym
创建同义词示例
create public synonym xxx for myusert_usercreate synonym t_user for myusert_user select * from dba_synonyms where table_name=T_USER
跨数据库查询 create database link dblinkzgl connect to myuser identified by a using mydb Select * From t_user@dblinkzgl
course示例 示例
create or replace procedure pro_test_cursor isuserRow t_user%rowtype;cursor userRows isselect * from t_user;beginfor userRow in userRows loopdbms_outputput_line(userRowId||||userRowName||||userRows%rowcount);end loop; end pro_test_cursor;
示例
create or replace procedure pro_test_cursor_oneRow(vid in number) isuserRow t_user%rowtype;cursor userCur isselect * from t_user where id=vid;beginopen userCur;fetch userCur into userRow;if userCur%FOUND thendbms_outputput_line(userRowid||||userRowName);end if;close userCur; end pro_test_cursor_oneRow;
record示例 create or replace procedure pro_test_record(vid in varchar) istype userRow is record(id t_userid%typename t_username%type);realRow userRow;beginselect idname into realRow from t_user where id=vid;dbms_outputput_line(realRowid||||realRowname); end pro_test_record;
rowtype示例 create or replace procedure pro_test_rowType(vid in varchar) isuserRow t_user%Rowtype;beginselect * into userRow from t_user where id=vid;dbms_outputput_line(userRowid||||userRowname); end pro_test_rowType;
