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