--查询表空间占用情况
select a.a1 表空间名称,
c.c2 类型,
c.c3 区管理,
b.b2 / 1024 / 1024 表空间大小M,
(b.b2 - a.a2) / 1024 / 1024 已使用M,
substr((b.b2 - a.a2) / b.b2 * 100, 1, 5) 利用率
from (select tablespace_name a1, sum(nvl(bytes, 0)) a2
from dba_free_space
group by tablespace_name) a,
(select tablespace_name b1, sum(bytes) b2
from dba_data_files
group by tablespace_name) b,
(select tablespace_name c1, contents c2, extent_management c3
from dba_tablespaces) c
where a.a1 = b.b1
and c.c1 = b.b1;
SELECT T.TABLESPACE_NAME,
D.FILE_NAME,
D.AUTOEXTENSIBLE,
D.BYTES/1024/1024/1024,
D.MAXBYTES/1024/1024/1024,
D.STATUS
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME, FILE_NAME;
--数据表空间扩展
alter tablespace VBM_DATA01 add datafile 'E:\APP\LC\PRODUCT\11.2.0\DBHOME_1\DATABASE\GUIHOUSPACE9201.DBF' size 200M autoextend on next 100M maxsize 3000M;
select round(sum(BYTES)/1024/1024/1024,2)||'G' from user_segments where segment_name like 'ALM_RES_PMT_9999';
select round(sum(BYTES)/1024/1024/1024,2)||'G' from user_segments where segment_name like 'ALM_RES_PMT_RATE_9999';
select round(sum(BYTES)/1024/1024/1024,2)||'G' from user_segments where segment_name like 'MAS_PORT_9999_alm';
select * from dba_data_files;
select * from dba_temp_files;
--临时表空间扩展
alter tablespace VBM_DATA01 add datafile '/data/orcl/oradata/VBM_DATA010.DBF' size 200M autoextend on next 100M maxsize 10000M;
--172.36.1.40:1521/testdb
select * from user_users
--锁表
select l.SESSION_ID,o.OWNER,o.OBJECT_NAME from v$locked_object l, dba_objects o where l.OBJECT_ID = o.OBJECT_ID;
select t1.USERNAME,t1.SID,t1.SERIAL#,t1.LOGON_TIME,t1.MACHINE from v$locked_object t, v$session t1 where t.SESSION_ID = t1.SID order by t1.LOGON_TIME;
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=371
--杀死进程
alter system kill session '15,53453';
alter system kill session '5,34903';
alter system kill session '147,145';
alter system kill session '587,57007';
--查询正在执行的sql
SELECT b.sid oracleID,
b.username 登录Oracle用户名,
b.serial#,
spid 操作系统ID,
paddr,
sql_text 正在执行的SQL,
b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
AND b.sql_hash_value = c.hash_value
--sql执行进度
SELECT SE.SID,
OPNAME, ---简要说明
-- TARGET,---操作对象
-- TARGET_DESC,--目标对象说明
START_TIME, ---开始操作时间
LAST_UPDATE_TIME,
TIME_REMAINING, ---预计完成剩余秒
TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK, --SOFAR 迄今为止完成的工作量,TOTALWORK 总工作量
ELAPSED_SECONDS ELAPSED, ---从操作开始总花费时间(秒)
ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
SQL_TEXT
FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
AND SL.SID = SE.SID
AND SOFAR != TOTALWORK
ORDER BY START_TIME;
--查询表名及相关字段
select A.TABLE_NAME as "表名",
C.COMMENTS AS "表说明",
A.COLUMN_ID AS "字段序号",
A.COLUMN_NAME AS "字段名",
B.COMMENTS AS "字段说明",
A.DATA_TYPE AS "字段数据类型",
A.DATA_LENGTH AS "数据长度",
A.DATA_PRECISION AS "整数位",
A.DATA_SCALE AS "小数位"
from USER_TAB_COLUMNS A left join user_col_comments B
on A.TABLE_NAME = B.table_name and A.COLUMN_NAME = B.column_name
left join USER_TAB_COMMENTS C
on A.TABLE_NAME = C.table_name
order by A.TABLE_NAME, A.COLUMN_ID
--查看表占用的空间
select t.segment_name,
t.segment_type,
sum(t.bytes / 1024 / 1024) "占用空间(M)",
OWNER
from dba_segments t
where t.segment_type = 'TABLE' --and t.segment_name like '%MAS_PORT%'
group by OWNER, t.segment_name, t.segment_type
order by sum(t.bytes / 1024 / 1024) desc;
--查看正在等待的会话
select *
from v$active_session_history h
where sample_time > trunc(sysdate)
and session_state = 'WAITING'
and exists (select *
from v$sql s
where upper(s.sql_text) like '%insert%'
and s.sql_id = h.sql_id)
order by sample_time desc;
--新增表空间
create tablespace VBM_DATA01 datafile '/oradata/ALM/vbm_data02.dbf' size 200M autoextend on next 100M maxsize 30000M;
alter tablespace VBM_DATA01 add datafile '/oradata/ALM/vbm_data02.dbf' size 200M autoextend on next 100M maxsize 30000M;
alter database datafile '/oradata/ALM/vbm_data01.dbf' resize 50000M
--删除用户,及级联关系也删除掉
drop user vbm_rapm_ts_alm cascade;
--删除表空间,及对应的表空间文件也删除掉
drop tablespace GUIZHOUSPACE92 including contents and datafiles cascade constraint;
--删除表空间文件
alter tablespace GUIZHOUSPACE92 drop datafile 'E:\APP\LC\PRODUCT\11.2.0\DBHOME_1\DATABASE\GUIHOUSPACE92.DBF';
--查询默认表空间
select * from user_users;
--默认表空间
alter user vbm_rapm_yh_alm default tablespace VBM_DATA01;
--12c
alter user c##vbm_rapm_ts_alm default tablespace VBM_DATA01;
select * from dba_directories
--新建用户 11g
create user vbm_rapm_yh_alm identified by 1;
--12c
create user c##vbm_rapm_ts_alm identified by vbmrisk;
--授权用户 11g
grant connect, resource to vbm_rapm_yh_alm;
--12c
grant connect, resource to c##vbm_rapm_ts_alm;
-- 11g
grant dba to vbm_rapm_yh_alm;
-- 12c
grant dba to c##vbm_rapm_ts_alm;
--查询字符集
select userenv('language') from dual;
--数据泵导入:
impdp vbm_mas/1@172.36.1.40:1521/testdb remap_schema=vbm_mas:vbm_mas REMAP_TABLESPACE=VBM_DATA01:VBM_DATA01 directory=DUMP_DIR dumpfile=vbm_mas_20200408.dmp logfile=vbm_mas_20200408.log table_exists_action=REPLACE transform=segment_attributes:n;
--数据泵导出:
expdp vbm_rapm_ts_alm/vbmrisk@10.5.102.145:1521/alm dumpfile=vbm_rapm_0917.dmp directory=DATA_PUMP_DIR logfile=vbm_rapm_0917.log version=11.2
--tables=VBM_RAPM_GZ20200317.Mas_Curve_Data
--version=11.2 --指定版本
impdp vbm_rapm_ts_alm/vbmrisk@orcl remap_schema=vbm_rapm_ts_alm:vbm_rapm_ts_alm REMAP_TABLESPACE=VBM_DATA01:VBM_DATA01 directory=GUIZHOU92_DATA dumpfile=vbm_rapm_0917.dmp logfile=vbm_rapm_0917.log table_exists_action=REPLACE transform=segment_attributes:n
select file_name,tablespace_name,bytes from dba_data_files;
imp vbm_rapm_ts_alm/1@192.168.1.51:1521/orcl
impdp vbmprp/1 transform=segment_attributes:n dumpfile=rptprp.dmp directory=GUIZHOU92_DATA remap_schema=rptprp:vbmprp;
select * from dba_directories
--查看数据库版本
select * from v$version
--第一步:启用行迁移
alter table ROMP_SYS_MENU enable row movement;
--第二步:闪回表到120分钟前
flashback table romp_sys_function to timestamp systimestamp -interval '30' minute;
/*重启数据库监听*/
--(1) 以oracle身份登录数据库,命令:su -oracle
--(2) 进入Sqlplus控制台,命令:sqlplus /nolog
--(3) 以系统管理员登录,命令:connect / as sysdba
--(4) 启动数据库,命令:startup
--(5) 如果是关闭数据库,命令:shutdown immediate
--(6) 退出sqlplus控制台,命令:exit
--(7) 进入监听器控制台,命令:lsnrctl
--(8) 启动监听器,命令:start
--(9) 退出监听器控制台,命令:exit
/*重启数据库实例*/
--(1) 切换需要启动的数据库实例:export ORACLE_SID=C1
--(2) 进入Sqlplus控制台,命令:sqlplus /nolog
--(3) 以系统管理员登录,命令:connect / as sysdba
--(4) 如果是关闭数据库,命令:shutdown abort
--(5) 启动数据库,命令:startup
--(6) 退出sqlplus控制台,命令:exit