Oracle(D4C)-------ocp训练

查询数据库中失效的对象
SELECT object_name, object_type FROM DBA_OBJECTS WHERE status = 'INVALID';

重做日志:

查看所有重做日志的路径:
select GROUP#,member from v$logfile;
查看所有重做日志的状态:
select members,group#,status from v$log;
切换日志组:
alter system switch logfile;
为日志组增加一个日志文件:
alter database add logfile member '/data/u01/app/oracle/oradata/eskdb/redo01c.log' to group 1;从日志组删除一个日志文件:
alter database drop logfile member '/data/u01/app/oracle/oradata/eskdb/redo01c.log';新增加一个日志组:
alter database add logfile group 4 '/data/u01/app/oracle/oradata/eskdb/redo04a.log' size 16m;删除一个日志组:
alter database drop logfile group 4;

复用控制文件
alter system setcontrol_files='/data/u01/app/oracle/oradata/eskdb/control01.ctl','/home/oracle/control02.ctl','/data/u01/app/oracle/oradata/eskdb/control03.ctl' scope=spfile;
关闭数据库
拷贝已有控制文件
启动数据库

创建directory并赋予操作权限
create directory mydir as '/home/oracle/mydir';
grant read on directory mydir to hr;
grant write on directory mydir to hr;
导出一个用户下所有的对象
expdp hr/hr directory=mydir dumpfile=hr0829.dmp logfile=hr0829.log schemas=hr
导出一张表数据
expdp hr/hr directory=mydir dumpfile=hr_emp0829.dmp logfile=hr_emp0829.log tables=hr.employees导出表的部分数据
expdp hr/hr directory=mydir dumpfile=hr_dep0829.dmp logfile=hr_dep0829.log tables=hr.departmentsquery=departments:\"WHERE department_id\>100\"

导入:
impdp hr/hr directory=mydir dumpfile=hr0829.dmp logfile=hr0829.log full=y
导入到另一个对象中
impdp hr/hr directory=mydir dumpfile=hr0829.dmp logfile=hr0903_0829.log REMAP_SCHEMA=HR:HR0903导入到不同名的表中
impdp hr1217/hr1217 directory=mydir dumpfile=hr_emp1217.dmp logfile=hr_emp1217.log remap_schema=hr:hr1217REMAP_table=EMPLOYEES:EMP0903

可传输表空间(tts):
源服务器操作
create tablespace transporttest datafile '/home/oracle/transporttest01.dbf' size 100m;create table hr.transporttable tablespace transporttest as select * from dba_objects;EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('transporttest',true);

SELECT * FROM TRANSPORT_SET_VIOLATIONS;--没有记录说明是自包含的
alter tablespace transporttest read only;
expdp system/oracle directory=data_pump_dir dumpfile=transporttest.dmp logfile=transporttest.logtransport_tablespaces=transporttest
将数据文件和dump文件拷贝到目标服务器
cp /home/oracle/transporttest01.dbf /tmp/
drop tablespace transporttest including contents and datafiles;
select count(*) from hr.transporttable;
目标服务器导入表空间:
cp /tmp/transporttest01.dbf /home/oracle/
impdp system/oracle directory=data_pump_dir dumpfile=transporttest.dmp logfile=transporttest.logtransport_datafiles='/home/oracle/transporttest01.dbf'
select count(*) from hr.transporttable;
alter tablespace transporttest read write;

创建外部表:
create table extab_employees(employee_id number(4),first_name varchar2(20),last_name

varchar2(20),hire_date date)
organization external
(type oracle_loader default directory mydir access parameters(records delimited by newline fields

terminated by ',' missing field values are null
(employee_id,first_name,last_name,hire_date char date_format date mask "yyyymmdd")) location

('emp1.txt')) parallel reject limit unlimited ;
修改外部表:
alter table extab_employees location('emp1.txt','emp2.txt');

行链接和行迁移:
当一条行记录大于一个数据块大小时,会发生行链接。当一条行记录在更新后无法再保存在原有的数据块中,就必须要分配到另一个块中,此时发生的是行迁移,update会导致行迁移,insert和delete不会。
alter table xxx pctfree 50;--为将来的数据更新保留50%的空闲空间
alter table employees pctfree 50;
alter table xxx move [tablespacename];--重新创建表,可以消除行迁移,会阻塞dml操作
alter table employees move;
analyze table employees compute statistics;--检测行迁移和行链接段收缩:对段空间进行重新组织,不会阻塞dml操作
alter table departments enable row movement;
alter table departments shrink space cascade;

alter table departments disable row movement;查询创建对象使用的语句:
select dbms_metadata.get_ddl('TABLE','DEPARTMENTS') from dual;