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
