仅做记录,就自己看看而已
//合并分区
ALTER TABLE EMS_STORAGE_FILE MERGE PARTITIONS EMS_STORAGE_FILE20160207, EMS_STORAGE_FILE20160208 INTO PARTITION EMS_STORAGE_FILE20160208 UPDATE INDEXES;
//杀session
select sid,serial#,PADDR,username from v$session where username='VISS35';
alter system kill session '447,103';
select a.spid,b.sid,b.serial#,b.username from v$process a,v$session b where a.addr=b.paddr and b.status='KILLED' and b.username='VISS35'
//表空间
select name, status from v$datafile where status='OFFLINE';
alter database datafile '/xxx/xxx/xxx.dbf' online;
alter database datafile 547 online;
alter tablespace my01 online;
alter tablespace test_mult_ws_20 offline; DROP TABLESPACE test_mult_ws_20 INCLUDING CONTENTS AND DATAFILES;
SELECT segment_name, segment_type, sum(bytes/1024/1024/1024)||'G' ,sum(blocks),sum(extents),count(*) FROM user_segments WHERE segment_name='EMS_STORAGE_FILE' group by segment_name, segment_type;
SELECT segment_name, segment_type, (bytes/1024/1024)||'M' ,blocks,extents FROM user_segments WHERE segment_name='EMS_STORAGE_FILE' ;
//mount
nid target=sys/oracle dbname=orcl setname=yes(dbid修改)
//rman备份恢复111
//
list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORCL 1443165364 PARENT 1 24-AUG-13
2 2 ORCL 1443165364 PARENT 925702 24-JUN-16
...
14 14 ORCL 1443165364 PARENT 1786506 28-JUN-16
15 15 ORCL 1443165364 CURRENT 1896462 28-JUN-16
reset database to incarnation 14;
restore database until scn 1896461;
recover database until scn 1896461;
alter database add logfile thread 1 group 1('/home/oracle/oracleDB/oradata/orcl/redo01.log') size 100m;
alter database add logfile thread 1 group 2('/home/oracle/oracleDB/oradata/orcl/redo02.log') size 100m;
alter database add logfile thread 1 group 3('/home/oracle/oracleDB/oradata/orcl/redo03.log') size 100m;
// 将Oracle数据库设置为归档模式
1)sql>shutdown normal/immediate;
2)sql>startup mount;
3)sql>alter database archivelog;
4)sql>alter database open;
5)archive log list;
select current_scn from v$database;
create or replace directory NAMExx as 'PATH';
GRANT CONNECT,RESOURCE TO xxx;
GRANT CREATE VIEW TO xxx;
GRANT CREATE TABLE TO xxx;
GRANT CREATE SEQUENCE TO xxx;
GRANT CREATE TABLESPACE TO xxx;
GRANT DROP TABLESPACE TO xxx;
GRANT ALTER TABLESPACE TO xxx;
GRANT EXECUTE ON DBMS_PIPE TO xxx;
GRANT CREATE ANY DIRECTORY to xxx;
GRANT DROP ANY DIRECTORY to xxx;
expdp viss35/viss35 directory=DIRECTORY tables=xx:xx,xx:xx access_method=external_table dumpfile=tttt2.dmp nologfile=y ;
impdp viss35/viss35 directory=DIRECTORY tables=xx:xx,xx:xx dumpfile=tttt2.dmp access_method=external_table table_exists_action=replace nologfile=y ;
SELECT s.sid, s.username, s.osuser, p.spid, s.program, s.module, s.event, s.status
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND p.spid IN ( '20903');
--活动信息
SELECT sql_text sql FROM v$process a, v$session b, v$sqlarea c WHERE a.addr = b.paddr AND b.sql_hash_value = c.hash_value;