SELECT * FROM DBA_USERS;--目前使用者所有管理中的使用者
SELECT * FROM ALL_USERS;--所有使用者資訊
SELECT * FROM USER_USERS;--目前使用者資訊
SELECT * FROM ALL_ALL_TABLES WHERE OWNER='APPS';
select table_name from user_tables;--目前使用者所有的表
select * from v$session;
select * from user_tab_privs;--當前用戶表權限
select * from user_sys_privs;--當前用戶系統權限
select * from user_role_privs;--目前使用者權限
select username,default_tablespace from user_users;--當前用戶預設表空間
CREATE TABLESPACE TS_APPS datafile 'D:\ORACLE11\oracledata\test\data_1.dbf' size 2000M;
--eg:
create tablespace space_64
Datafile 'spac_6401.dbf' size 10M,'space_6402.dbf'size 5M
Default storage
(
initial initValue next nextValue maxextents unlimited pctincrease 20
)online;
--Nextvalue是當資料去的資料塊不夠使用時每次配置設定的資料塊數量
alter user "ROOTS" identified by "ROOTS";
--eg:
Create user fengjie_fans
Identified by 64draglong
Default tablespace space_64
Temporary tablespace space_64;
alter user apps identified by apps;--修改密碼(apps1:用戶名,apps2:密碼)
GRANT ALL PRIVILEGES to apps;
GRANT CONNECT,RESOURCE TO apps;
--GRANT/revoke 權限 ON 範圍 TO 用戶;
SELECT UID FROM DUAL;--目前使用者id
SELECT USER FROM DUAL;--目前使用者資訊
SELECT USERENV('isdba'),USERENV('sessionid'),USERENV('TERMINAL'),USERENV('LANGUAGE') FROM DUAL;--使用者資訊
select sys_context('userenv','isdba') from dual;
-----2、轉義
**/
select '''',' '' ','name'||'''','name''''',
'hh24"小時""mi""分"""ss"秒"',
to_char(sysdate,'hh24"小時"mi"分"ss"秒"') from dual;
--起別名時,別名存在空格,需要使用雙引號
select d_part "dept part" from dept;
select d_part 'dept part' from dept;--錯誤示範
restore database/tablespace/datafile/controlfile/archivelog
recover database/tablespace/datafile
backup [email protected] tag='text';
--獲取當前用戶的funtion資訊
select * from user_objects where object_type='function';
select ora_sysevent from dual;--獲取系統操作類型(drop\create\alter。。等系統操作)
--獲取函數calc_quarter_hour的源碼
select dbms_metadata.get_ddl('function','calc_quarter_hour') from dual;--ora-00316
--檢視日志資訊
select * from v$logfile;
select * from v$sqlarea;
-----3、lock table
select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid
order by b.logon_time;
alter system kill SESSION '6,12065';
COMMIT;