Oracle随筆筆記2

1.    建立同義詞

1>  拼接建立同義詞語句

SELECT 'CREATE SYNONYM ' || UPPER(TABLE_NAME) || ' FOR CGTAX_V_31.' || UPPER(TABLE_NAME) || ';' FROM USER_TABLES WHERE TABLE_NAME LIKE 'CG_COLLECT_%';

2>  拼接删除同義詞語句

select 'drop public synonym ' || synonym_name || ';' from dba_synonyms where synonym_name like '%CG_COLLECT_%';

3>  建立私有同義詞

CREATE SYNONYM CG_COLLECT_ATTR_MAPPING FOR CGTAX_V_31.CG_COLLECT_ATTR_MAPPING;

4>     查詢同義詞

select * from dba_synonyms where synonym_name like '%CG_COLLECT_%';

5>  示例：

CREATE [OR REPLACE] [PUBLIC] SYNONYM [ schema.] 同義詞名稱 FOR [ schema.] object [ @dblink ];

2.    查詢資料庫下所有使用者的資料大小

SELECT OWNER as "使用者名",

sum(BYTES) / 1024 / 1024 / 1024 as "所有表的大小(GB)"

FROM DBA_SEGMENTS

WHERE SEGMENT_NAME in (select t2.OBJECT_NAME

from dba_objects t2

where t2.OBJECT_TYPE = 'TABLE')

group by OWNER

• order by 2 desc;

3.    新增表空間資料檔案

1>  ALTER DATABASE DATAFILE 'C:\SDE.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

2>  ALTER TABLESPACE USERS AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

3>  ALTER DATABASE DATAFILE 'D:\DSINSTALL\ORACLE\ORADATA\ORCL\USERS02.DBF' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

4>  ALTER TABLESPACE USERS ADD DATAFILE 'D:\DSINSTALL\ORACLE\ORADATA\ORCL\USERS03.DBF' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

4.    查詢表空間資料大小

SELECT UPPER(F.TABLESPACE_NAME)"表空間名",

D.TOT_GROOTTE_MB "表空間大小(M)",

D.TOT_GROOTTE_MB-F.TOTAL_BYTES "已使用空間(M)",

TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99')||'%'"使用比",

F.TOTAL_BYTES "空閑空間(M)",

F.MAX_BYTES "最大塊(M)"

FROM(SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,

ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME)F,

 (SELECT DD.TABLESPACE_NAME,

ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME)D

WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME

ORDER BY 1;

5.    修改表空間資料檔案大小，優化存儲

select a.file#,

a.name,

a.bytes / 1024 / 1024 CurrentMB,

ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,

(a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,

'alter database datafile ''' || a.name || ''' resize ' ||

ceil(HWM * a.block_size) / 1024 / 1024 || 'M;' ResizeCmd

from v$datafile a,

(SELECT file_id, MAX(block_id + blocks - 1) HWM

FROM DBA_EXTENTS

GROUP BY file_id) b

where a.file# = b.file_id(+)

And (a.bytes - HWM * a.block_size) >0

and rownum < 10