ORACLE很重要的sql語句

select * from all_users;                 ##檢視所有使用者
select name from v$database;             ##檢視當前資料庫名
select * from v$instance;                ##檢視所有的資料庫例項
select username,password from dba_users; ##檢視當前例項中的使用者和密碼
select member from v$logfile;            ##檢視日誌檔案
select * from user_role_privs;           ##檢視當前使用者的角色
select username,default_tablespace from user_users; ##檢視當前使用者的預設表空間

查詢資料庫中所有列
select C.column_name,C.TABLE_NAME from dba_tab_columns C where wner=''

表複製
insert into table_a (id,name,age) select b.id,b.name,b.age from table_b;

檢視當前Job的執行計劃
select job,next_date,next_sec,failures,broken from user_jobs;

刪除一個job
begin
dbms_job.remove(46);--46為job號
end;

檢視當前庫的所有資料表：
select TABLE_NAME from all_tables;
select * from all_tables;
select table_name from all_tables where table_name like ‘u’;

建立使用者並賦予許可權
create user mpss
identified by "mpss12"
default tablespace TS_MPSS_DATA
temporary tablespace TEMP;

給使用者賦予許可權
grant connect to mpss;
grant resource,create session to mpss; 開發角色
grant create procedure to dbuser； ＃這些許可權足夠用於開發及生產環境

給使用者授權
grant dba to spms;--授予DBA許可權
grant unlimited tablespace to lxg;--授予不限制的表空間
grant select any table to lxg;--授予查詢任何表
grant select any dictionary to lxg;--授予 查詢 任何字典

刪除使用者
drop user mpss cascade;

Create the user （用sys執行）
create user xx identified by xx123 default tablespace lmss temporary tablespace TEMP profile DEFAULT;

Grant/Revoke role privileges （用sys執行）
grant connect to xx;

建立檢視給hy使用者（用leon使用者）
create or replace view view_tableName as
select column。。。 from table;
Grant/Revoke object privileges
grant select, update on RES_XIM_CARD to hy;

建立同義詞
create synonym RES_XIM_CARD for YY.RES_XIM_CARD ;

建表空間
CREATE TABLESPACE "TS_MPSS_DATA"
    LOGGING
    DATAFILE '/mpss/data/ts_mpss_data.dbf' SIZE 1024M
    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
建立臨時表空間
CREATE
    TEMPORARY TABLESPACE "SWVIP" TEMPFILE '/app/oracle/oradata/
    sworacle/SWVIP.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM.
    SIZE 1M
create tablespace TS_MPSS_DATA datafile '/mpss/data/ts_mpss_data.bdf ' size 1024m autoextend on ;

檢視錶空間
檢視錶空間大小
SELECT D.TABLESPACE_NAME "Name",
    TO_CHAR(((((A.BYTES - DECODE(F.BYTES, NULL, 0, F.BYTES)) / 1024 / 1024)) /(A.BYTES / 1024 /   1024))*100,'99,990.9') "used(%)",
TO_CHAR((DECODE(F.BYTES, NULL, 0, F.BYTES) / 1024 / 1024),'999,990.9') "Free (M)"
FROM SYS.DBA_TABLESPACES D, SYS.SM$TS_AVAIL A, SYS.SM$TS_FREE F
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME
AND F.TABLESPACE_NAME (+) = D.TABLESPACE_NAME;

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ;

檢視錶空間物理檔案的名稱及大小;
select tablespace_name, file_id, file_name,
    round(bytes/(1024*1024),0) total_space
    from dba_data_files
    order by tablespace_name;

檢視資料檔案放置的路徑
col file_name format a50
select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

檢視資料庫庫物件
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

用系統管理員，檢視當前資料庫有幾個使用者連線：
select username,sid,serial# from v$session;

擴表空間
alter tablespace G000 add datafile '/dev/vgbilling/
 

 原文地址 http://hi.baidu.com/firefly5002/blog/item/6e5b3e22257e134eac34de58.html
文章出處：DIY部落(http://www.diybl.com/course/7_databases/oracle/oraclejs/20091204/184062.html)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11536986/viewspace-621919/，如需轉載，請註明出處，否則將追究法律責任。