ocp培训笔记

shutdown normal
shutdown transactional
shutdown immediate
shutdown abort

startup nomount
alter database mount;
alter database open;

password file:
$ORACLE_HOME/dbs/orapw
orapwd file=$ORACLE_HOME/dbs/orapworcl password=sys force=y

conn / as sysdba
alter user sys identified by oracle;

password

select * from v$pwfile_users;

grant sysdba to scott;
revoke sysdba from scott;

parameter file:
$ORACLE_HOME/dbs
init.ora
init.ora

startup pfile='/tmp/1.ora'

spfile.ora
spfile.ora

select distinct ISSPECIFIED from v$spparameter;

select name,ISSYS_MODIFIABLE from v$system_parameter where ISSYS_MODIFIABLE='FALSE';

alter system set parameter_name=value scope=spfile;

select name,ISSYS_MODIFIABLE from v$system_parameter where ISSYS_MODIFIABLE!='FALSE';

alter system set parameter_name=value;

pga_aggregate_target

oracle9i:sga_max_size=512m
oracle10g:sga_target=480m

oracle11g:memory_max_size
oracle11g:memory_target

shared_pool_size
db_cache_size
log_buffer
large_pool_size
java_pool_size
streams_pool_size

alter system set sga_target=0;
alter system set shared_pool_size=112m;
alter system set db_cache_size=148m;

alter system set sga_max_size=300m scope=spfile;

conn / as sysdba
create pfile='/tmp/1.ora' from spfile;
create spfile from pfile;

startup pfile='/tmp/1.ora'

dbrac1.undo_tablespace='UNDOTBS1'
dbrac2.undo_tablespace='UNDOTBS2'

backgroud process:
select * from v$bgprocess where paddr<>'00';

controlfile:1-8
select name from v$controlfile;

show parameter control_files

alter system set control_files=
  '/u01/app/oracle/oradata/orcl/control01.ctl',
  '/u01/app/oracle/oradata/orcl/control02.ctl',
  '/u01/app/oracle/oradata/orcl/control03.ctl',
  '/u01/app/oracle/oradata/orcl/control04.ctl'
scope=spfile;

shutdown immediate

host cp /u01/app/oracle/oradata/orcl/control03.ctl /u01/app/oracle/oradata/orcl/control04.ctl

startup

tablespace & datafile:
create tablespace demo datafile '/u01/app/oracle/demo01.dbf' size 10m;

create temporary tablespace temp02 tempfile '/u01/app/oracle/temp02.dbf' size 10m;

create undo tablespace undo02 datafile '/u01/app/oracle/undo02.dbf' size 10m;

create bigfile tablespace testbig datafile '/u01/app/oracle/testbig.dbf' size 10m;

create tablespace test datafile '/u01/app/oracle/test.dbf' size 100m uniform. size 10m;

create table scott.test tablespace users as select * from scott.emp where 1=0;

insert into scott.test select * from scott.emp;

select e.segment_name,file_id,e.block_id,e.blocks,e.bytes/1024 kb
from dba_extents e
where  e.segment_name='TEST'
and e.owner='SCOTT';

alter table scott.test allocate extent (size 1m datafile '/u01/app/oracle/oradata/orcl/users01.dbf');

alter table scott.test deallocate unused;

truncate table scott.test;

drop table scott.test purge;

16 --&gt 64k
63 ---&gt 1m
120 ---&gt 8m
   ----&gt 16m

alter tablespace users offline;
alter tablespace users online;
alter tablespace users read only;
alter tablespace users read write;

drop tablespace test including contents and datafiles cascade constraints;

alter database datafile '/u01/app/oracle/demo01.dbf' autoextend on next 10m maxsize 100m;

alter database datafile '/u01/app/oracle/demo01.dbf' resize 20m;
alter tablespace demo add datafile '/u01/app/oracle/demo02.dbf' size 20m;

datafile '/dev/vg00/rdemo01_lv' 4095m;

1.alter tablespace demo offline;
2.host mv /u01/app/oracle/demo01.dbf /u01/app/oracle/oradata/orcl/demo01.dbf

3.alter tablespace demo rename datafile '/u01/app/oracle/demo01.dbf' to '/u01/app/oracle/oradata/orcl/demo01.dbf';

4.alter tablespace demo online;

1.shutdown immediate
 startup mount

2.host mv /u01/app/oracle/demo02.dbf /u01/app/oracle/oradata/orcl/demo02.dbf

3. alter database rename file '/u01/app/oracle/demo02.dbf' to '/u01/app/oracle/oradata/orcl/demo02.dbf';

4.alter database open;

select TABLESPACE_NAME,EXTENT_MANAGEMENT from dba_tablespaces;

create user test
identified by test
default tablespace users
temporary tablespace temp
quota 10m on users
password expire;

grant create session to test with admin option;

sho parameter os_authent_prefix
select osuser from v$session where username is not null;

create user ops$oracle
identified externally
default tablespace users
temporary tablespace temp
quota 10m on users;

grant create session to ops$oracle;
revoke ,,,, from ,,,,;

select distinct PRIVILEGE from dba_sys_privs;

grant select on scott.emp to test with grant option;
revoke select on scott.emp from test;

select * from session_privs;

create role r1;
create role r2;

grant create session to r1;
grant create table,create view to r2;

grant r1 to demo;
grant r2 to demo;

alter user demo default role r1;
alter user demo default role all except r2;

set role r1;
set role all;

create table test (id number,name varchar2(20));

select dbms_metadata.get_ddl('TABLE','EMP') FROM dual;

create global temporary table temp01
--on commit delete rows
as select * from scott.emp;

create global temporary table temp01
on commit preserve rows
as select * from scott.emp;

alter table scott.e move tablespace system;

create index i_e_ename on e (ename);

alter index i_e_ename coalesce;
alter index i_e_ename rebuild tablespace indx;

create or replace force view v30
as select empno,ename,sal,deptno from emp where deptno=30
with check option;
--with read only;

AAAMfM  AAE  AAAAAg  AAB
OOOOOO  FFF  BBBBBB  RRR

select dbms_rowid.ROWID_OBJECT(rowid) OOOOOO,
       dbms_rowid.ROWID_RELATIVE_FNO(rowid) fff,
       dbms_rowid.ROWID_BLOCK_NUMBER(rowid) bbbbbb,
       dbms_rowid.ROWID_ROW_NUMBER(rowid) rrr
from scott.emp
where empno=7839;

create sequence s1 start with 7936;
select * from user_sequences;

select s1.currval from dual;
select s1.nextval from dual;

create synonym emp for scott.emp;
drop synonym emp;
create public synonym emp for scott.emp;

grant create database link to scott;

create database link link_233_scott
using 'orcl233';

create database link link_233_scott
connect to scott identified by tiger
using 'orcl233';

select * from emp@link_233_scott;

create synonym test233 for test233@link_233_scott;

insert into emp values (7936,'ALVIN','APP',7839,SYSDATE,1000,null,20);

insert into emp(empno,ename) values (7937,'alice');

insert into emp select * from etst233 where ;

update emp set empno=7840,deptno=30 where empno=7839;

delete emp where deptno=30;

delete emp;

create directory expdir as '/u01/app/oracle/expbk';
grant read,write on directory expdir to scott;

expdp scott/ttt directory=expdir
impdp scott/ttt DIRECTORY=expdir TABLES=e

-----/u01/app/oracle/input.txt---
LOAD DATA
INFILE *
INTO TABLE D
REPLACE
FIELDS TERMINATED BY ','
(DEPTNO,DNAME,LOC)
BEGINDATA
40,SALSE,BEIJING

-----/u01/app/oracle/input.txt---
LOAD DATA
INFILE '/u01/app/oracle/1.txt'
INTO TABLE D
REPLACE
FIELDS TERMINATED BY ','
(DEPTNO,DNAME,LOC)
BEGINDATA
40,SALSE,BEIJING

---/u01/app/oracle/1.txt---
40,salse,beijing
50,driver,shenzhen

sqlldr userid=scott/ttt control=/u01/app/oracle/input.txt log=/u01/app/oracle/sqlldr.log

set serveroutput on
declare
  v_ename emp.ename%type;
  v_sal emp.sal%type;
  v_empno number :=&p_empno;
begin
  select ename,sal into :g_ename,:g_sal
  from emp where empno=v_empno;
  dbms_output.put_line(v_ename||' '||v_sal);
end;
/

var g_ename varchar2(20)
var g_sal number

create or replace function tax (p_sal in number)
return number
is
begin
  return (p_sal*0.02);
end;
/

declare
  cursor c1 is select ename,sal from scott.emp;
  r1 c1%rowtype;
begin
  open c1;
  loop
    fetch c1 into r1;
      exit when c1%notfound;
      dbms_output.put_line(chr(10)||r1.ename||' '||r1.sal);
  end loop;
  close c1;
end;
/

begin
  for r1 in (select ename,sal from scott.emp) loop
    dbms_output.put_line(chr(10)||r1.ename||' '||r1.sal);
  end loop;
end;
/

audit:
show parameter audit_trail
alter system set audit_trail=xml scope=spfile;
show parameter audit_file_dest

alter system set audit_trail=db scope=spfile;
aud$

sql>audtit table by scott; --(create & drop & truncate table)
sql>audit insert table by scott by access;
sql>audit insert table by scott by session;
sql>audit select any table;

sql>noaudtit table by scott;

sql>truncate table aud$;

create profile p1 limit FAILED_LOGIN_ATTEMPTS 2;
alter user scott profile p1;

alter profile p1 limit
PASSWORD_LIFE_TIME 1/1440
PASSWORD_GRACE_TIME 1/1440
/

alter system set shared_servers=5;
alter system set shared_server_sessions=20;

alter system set dispatchers='(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.234)(dispatchers=5))' scope=spfile;

lsnrctl stop
lsnrctl start
lsnrctl services

v$sysstat

create table b_stat as select NAME,VALUE from v$sysstat where name like '%redo size%';

create
insert
update
delete

create table e_stat as select NAME,VALUE from v$sysstat where name like '%redo size%';

select b.name,e.value-b.value value from b_stat b,e_stat e
where b.name=e.name;

@?/rdbms/admin/awrrpt
@?/rdbms/admin/addmrpt
@?/rdbms/admin/ashrpt

exec dbms_scheduler.run_job('GATHER_STATS_JOB');
exec dbms_scheduler.disable('GATHER_STATS_JOB');
exec dbms_scheduler.enable('GATHER_STATS_JOB');

exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'E',cascade=>true);

begin
dbms_stats.gather_schema_stats(
ownname=>'SCOTT',
estimate_percent=>20,
degree=>8,
cascade=>true);
end;
/

ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []

set transaction isolation level SERIALIZABLE;

alter table e enable row movement;
flashback table e to timestamp (sysdate-15/1440);

exec dbms_flashback.ENABLE_AT_TIME(sysdate-15/1440);
select * from scott.e;
exec dbms_flashback.disable();

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/40011/viewspace-674048/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/40011/viewspace-674048/