笔记:
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;
SELECT total.tablespace_name,
Round(total.MB, 2) AS Total_MB,
Round(total.MB - free.MB, 2) AS Used_MB,
Round(( 1 - free.MB / total.MB ) * 100, 2)
|| ‘%’ AS Used_Pct
FROM (SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_data_files
GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name;
查看表空间位置
select *from dba_data_files
SELECT
b.FILE_NAME ,
a.tablespace_name “表空间名”,
total “表空间大小”,
free “表空间剩余大小”,
(total - free) “表空间使用大小”,
total / (1024 * 1024 * 1024) “表空间大小(G)”,
free / (1024 * 1024 * 1024) “表空间剩余大小(G)”,
(total - free) / (1024 * 1024 * 1024) “表空间使用大小(G)”,
round((total - free) / total, 4) * 100 “使用率 %”
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT d.FILE_NAME, tablespace_name, SUM(bytes) total
FROM dba_data_files d
GROUP BY tablespace_name,FILE_NAME) b
WHERE a.tablespace_name = b.tablespace_name;
创建表空间
/*第1步:创建临时表空间 */
create temporary tablespace kc_temp
tempfile ‘C:\app\Administrator\oradata\orcl\kc_temp.dbf’
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*第2步:创建数据表空间 */
create tablespace kc
logging
datafile ‘C:\app\Administrator\oradata\orcl\kc.dbf’
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
——————————————————————————————————
create tablespace bappr ----创建表空间
datafile ‘E:\app\51674\oradata\orcl\bappr.dbf’ —表空间路径
size 2048M autoextend on next 50m maxsize 20480M —表空间初始大小2G 自动增长50m 表空间最大20G
extent management local ----本地管理(本地存储)
segment space management auto ----自动管理表空间
临时表空间
–select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
–alter database tempfile ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF’ resize 2048M;
–drop tablespace temp including contents and datafiles cascade constraints;
create user jx63 identified by “1” default tablespace bappr temporary tablespace temp;—创建用户
grant dba,connect,resource to jx63; ----赋予用户权限
——————————————————————————————————
通过数据泵导入导出数据
expdp czb6_online/1@orcl directory=EXPNC_DIR dumpfile=czb6_online.dmp logfile=czb6_online.log
create user czb6_online identified by 1 default tablespace bappr temporary tablespace temp;—创建用户
grant dba,connect,resource to czb6_online;
create directory EXPNC_DIR as ‘d:\back’; ----创建本地包路径
select * from dba_directories;----查询是否创建成功
grant read,write on directory EXPNC_DIR to czb6_online;—赋予权限
impdp czb6_online/1@orcl directory=EXPNC_DIR dumpfile=czb6_online.dmp logfile=czb6_online.log
impdp czb63/1@orcl REMAP_SCHEMA = CZB63_CURRENCY:czb63 directory=EXPNC_DIR dumpfile=czb63_currency_200420.dmp logfile=czb63_currency_200420.log version=10.2.0.1.0
impdp CZB63_CURRENCY/1 directory=DATA_PUMP_DIR dumpfile=CZB63_CURRENCY-200424.dmp version=10.2.0.1.0
1、select * from all_directories 找到目录,选择一个目录名
2、把dmp文件直接放在目录下
3、impdp czb6_online/1@orcl directory=EXPNC_DIR dumpfile=czb6_online.dmp logfile=czb6_online.log
impdp czb6_online/1@orcl (数据库用户/密码@实例名) directory=EXPNC_DIR (目录名)dumpfile=czb6_online.dmp(数据库文件) logfile=czb6_online.log(数据库日志)
获取uuid
wmic csproduct list full | findstr UUID
数据库文件扩容
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;
select b.file_id 文件ID,
b.tablespace_name 表空间,
b.file_name 物理文件名,
b.bytes 总字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
order by b.tablespace_name
扩表空间:
alter database datafile ‘F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATA_1.DBF’ resize 23000M
增加表空间文件:
alter tablespace venn add datafile ‘/opt/oracle/db01/app/oracle/oradata/OSSORCL/venn02.dbf’
size 10M
autoextend on next 100M
maxsize 500M ;
监听文件增加配置:
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
(SID_NAME = orcl)
)
—导入文件需要用户to
含义为从将表空间从DEV_ODI_USER转移为JJE_ODI_USER
remap_tablespace=DEV_ODI_USER:JJE_ODI_USER
–将用户to到另外一个用户下
EMAP_SCHEMA = CZB63_CURRENCY:czb63