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 procedure
pro_test_exception(vid in varchar) is
userName varchar();
begin
select name into userName from t_user where id=vid;
dbms_outputput_line(userName);
exception
when no_data_found then
dbms_outputput_line(沒有查到數據！);
when too_many_rows then
dbms_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_user
create 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 is
userRow t_user%rowtype;
cursor userRows is
select * from t_user;
begin
for userRow in userRows loop
dbms_outputput_line
(userRowId||||userRowName||||userRows%rowcount);
end loop;
end pro_test_cursor;
示例
create or replace procedure
pro_test_cursor_oneRow(vid in number) is
userRow t_user%rowtype;
cursor userCur is
select * from t_user where id=vid;
begin
open userCur;
fetch userCur into userRow;
if userCur%FOUND then
dbms_outputput_line
(userRowid||||userRowName);
end if;
close userCur;
end pro_test_cursor_oneRow;
record示例
create or replace
procedure pro_test_record(vid in varchar) is
type userRow is record(
id t_userid%type
name t_username%type
);
realRow userRow;
begin
select 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) is
userRow t_user%Rowtype;
begin
select * into userRow from t_user where id=vid;
dbms_outputput_line
(userRowid||||userRowname);
end pro_test_rowType;
From:http://tw.wingwit.com/Article/program/Oracle/201311/17660.html
