oracle user message

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与收回权限revoke
**/ 
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
recover
**/

restore database/tablespace/datafile/controlfile/archivelog

recover database/tablespace/datafile 

backup apps@MICtEST 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;