--創建用戶① --GRANT（授權）REVOKE（回收許可權）--創建用戶create user 用戶名 identified by 密碼 GRANT CONNECT,RESOURCE TO 用戶;GRANT CREATE VIEW TO 用戶;GRANT CREATE SYNONYM TO 用戶; ...
--創建用戶①
--GRANT（授權）REVOKE（回收許可權）
--創建用戶
create user 用戶名 identified by 密碼
GRANT CONNECT,RESOURCE TO 用戶;
GRANT CREATE VIEW TO 用戶;
GRANT CREATE SYNONYM TO 用戶;
GRANT CREATE JOB TO 用戶;
GRANT SELECT ANY TABLE TO 用戶;
--
GRANT SELECT ON 表名/視圖名 TO 用戶/角色；
--Ⅰ給用戶分許可權Ⅱ創建角色，給用戶賦予角色，給角色分許可權
--創建用戶②
PROMPT =============================================================================================================
PROMPT =====Step One: 定義數據文件存儲路徑位置(該路徑需根據實際部署環境進行調整 important!!!)
PROMPT =====可查閱select * from dba_data_files;視圖查看數據文件存儲路徑.
PROMPT =============================================================================================================
define __DATAFILE_DESTINATION__ = '/Oradata/wzhipdb/wzhip/';
--jgptf替換
--create tablespace
CREATE TABLESPACE tbs_hip_jgptf DATAFILE '/Oradata/wzhipdb/wzhip/tbs_hip_jgptf.dbf' SIZE 512M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED;
--create temporary tablespace
CREATE TEMPORARY TABLESPACE tbs_hip_jgptf_temp tempfile '/Oradata/wzhipdb/wzhip/tbs_hip_jgptf_temp.dbf' SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;
--create user CREATE USER 用戶名 IDENTIFIED BY 密碼
CREATE USER yhpt_jgptf IDENTIFIED BY yhpt_jgptf_wz DEFAULT TABLESPACE tbs_hip_jgptf TEMPORARY TABLESPACE tbs_hip_jgptf_temp ACCOUNT UNLOCK;
ALTER USER yhpt_jgptf QUOTA UNLIMITED ON tbs_hip_jgptf;
--grant privs
GRANT CONNECT,RESOURCE TO yhpt_jgptf;
GRANT CREATE VIEW TO yhpt_jgptf;
GRANT CREATE SYNONYM TO yhpt_jgptf;
GRANT CREATE JOB TO yhpt_jgptf;
GRANT SELECT ANY TABLE TO yhpt_jgptf;
--同義詞
CREATE [PUBLIC] SYNONYM 同義詞名稱 FOR 資料庫對象；--（加public為公用，不加為私有進創建用戶可見）
--sql判斷數字字母漢字
一.包含
1.包含中文字元
select * from 表名 where 列名 like '%[吖-座]%'
2.包含英文字元
select * from 表名 where 列名 like '%[a-z]%'
3.包含純數字
select * from 表名 where 列名 like '%[0-9]%'
二.判斷
如：判斷中文
select * from dws_new_role where ascii(game_server) > 123
判斷數字
select * from dws_new_role where ascii(role_id) between 48 and 57
三.判斷sql是否為純數字
1：regexp_like(data,'^[0-9]+$')純數字。
2： 亦可用 regexp_like(data, '^[[:digit:]]+$');
3.判斷是否為3位純數字：regexp_like(data,'^[0-9]{3}$')
--nvl、case when
nvl((case when regexp_like(h.C_OP_ID,'^[0-9]+$') then h.C_OP_ID else '' end ),(select c_emp_id from x_user where c_id=h.C_OP_ID))
(case when regexp_like(h.C_OP_ID,'^[0-9]+$') then h.C_OP_ID else (select c_emp_id from x_user where c_id=h.C_OP_ID) end )
--結合and、or運算符
select *
FROM U_PAT a, U_REG b
WHERE b.C_PAT_ID = a.C_ID
AND (( '1' = '3' and a.c_name='桑豪' ) or
('2' = '3' and a.c_name='鄧軍' ) or
('3' = '3' and a.c_name='韋霞') )
select c.owner, c.table_name, c.* from dba_tab_columns c where c.column_name like '%C_UNIT_YF%';
--查詢關鍵詞/欄位在資料庫中哪些表中存在
declare
v_Sql varchar2(2000);
v_count number;
begin
for xx in (select t.OWNER, t.TABLE_NAME, t.COLUMN_NAME
from dba_tab_columns t
where t.OWNER = '密碼') loop
begin
v_Sql := 'select count(1) from ' || xx.owner || '.' || xx.table_name ||
' where ' || xx.column_name || ' like ''%查詢欄位/關鍵詞%'' ';
execute immediate v_Sql
into v_count;
if (v_count >= 1) then
dbms_output.put_line(xx.table_name || ':' || xx.column_name);
end if;
exception
when others then
null;
end;
end loop;
end;
--查詢表空間使用情況及剩餘
select 表空間名稱,
文件數量,
"文件總大小(G)",
"表空間文件剩餘大小(G)",
round(("文件總大小(G)" - "表空間文件剩餘大小(G)") / "文件總大小(G)", 4) * 100 as "使用率 %",
"ORACLE表空間剩餘大小(G)",
"ORACLE表空間使用大小(G)",
"ORACLE使用率 %"
from (SELECT a.tablespace_name "表空間名稱",
fileNumbers "文件數量",
fileNumbers * 32 "文件總大小(G)",
fileNumbers * 32 - round(total / (1024 * 1024 * 1024), 2) "表空間文件剩餘大小(G)",
total / (1024 * 1024 * 1024) "表空間大小(G)",
free / (1024 * 1024 * 1024) "ORACLE表空間剩餘大小(G)",
(total - free) / (1024 * 1024 * 1024) "ORACLE表空間使用大小(G)",
round((total - free) / total, 4) * 100 "ORACLE使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
count(*) as fileNumbers,
SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name) a
