-- 建立表空間 mytabs,10MB 初始大小
CREATE TABLESPACE mytabs DATAFILE 'C:\oracle\oradata\mydb\mytabs1.dbf' SIZE 10M;
-- 修改用戶預設表空間
ALTER USER zgl DEFAULT TABLESPACE mytabs;
-- 授予無限表空間配額
GRANT UNLIMITED TABLESPACE TO zgl;
一個表空間對應多個物理 .dbf 檔案。
CREATE OR REPLACE PROCEDURE pro_test_exception (vid IN VARCHAR2) IS
userName VARCHAR2(30);
BEGIN
SELECT name INTO userName FROM t_user WHERE id = vid;
DBMS_OUTPUT.PUT_LINE(userName);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('沒有查到資料!');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('返回了多行資料!');
END;
ALTER USER zgl ACCOUNT LOCK; -- 鎖定
ALTER USER zgl IDENTIFIED BY zgl11; -- 改密碼
ALTER USER zgl ACCOUNT UNLOCK; -- 解鎖
ALTER USER zgl DEFAULT TABLESPACE tt; -- 改預設表空間
CREATE USER qqq IDENTIFIED BY qqq123 DEFAULT TABLESPACE tt;
GRANT CONNECT TO qqq;
GRANT EXECUTE ON zgl.proc01 TO test;
GRANT CREATE USER TO zgl;
REVOKE CREATE USER FROM zgl;
CREATE ROLE myrole;
GRANT CONNECT TO myrole;
GRANT SELECT ON zgl.t_user TO myrole;
GRANT myrole TO test;
限制登入失敗次數、密碼複雜度、閒置時間等全域策略。
CREATE PUBLIC SYNONYM xxx FOR myuser.t_user;
CREATE SYNONYM t_user FOR myuser.t_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;
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_OUTPUT.PUT_LINE(userRow.Id || ',' || userRow.Name || ',' || userRows%ROWCOUNT);
END LOOP;
END;
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_OUTPUT.PUT_LINE(userRow.id || ',' || userRow.Name);
END IF;
CLOSE userCur;
END;
CREATE OR REPLACE PROCEDURE pro_test_record (vid IN VARCHAR2) IS
TYPE userRow IS RECORD (
id t_user.id%TYPE,
name t_user.name%TYPE
);
realRow userRow;
BEGIN
SELECT id, name INTO realRow FROM t_user WHERE id = vid;
DBMS_OUTPUT.PUT_LINE(realRow.id || ',' || realRow.name);
END;
CREATE OR REPLACE PROCEDURE pro_test_rowType (vid IN VARCHAR2) IS
userRow t_user%ROWTYPE;
BEGIN
SELECT * INTO userRow FROM t_user WHERE id = vid;
DBMS_OUTPUT.PUT_LINE(userRow.id || ',' || userRow.name);
END;