培训日志

cooldog

select * from user_ts_quotas;
select * from user_users;

create table emp (
empno number(7),
ename varchar2(20),
sal  number(7,2),
depno number(4)
)

insert into emp values (1001,'GARY',2000,10);

commit;

create table emp1 as select * from emp;

alter table emp add (hire_date date);

alter table emp modify (sal number(10,2));

alter table emp rename column sal to salary

alter table emp drop column salary;

rename emp to employees;

create table dept (
deptno number(4) ,
dname varchar2(20) not null,
loc varchar2(20),
constraint dept_pk primary key (deptno)


insert into dept values(20,'hr','gz');


create table emp (
  empno number(7),
  ename varchar2(20) not null,
  email varchar2(50),
  sal  number(7,2),
  deptno number(2),
  constraint emp_pk primary key (empno),
  constraint emp_email_uk unique (email),
  constraint emp_dep_fk foreign key (deptno) references dept(deptno),
  constraint emp_sal_ck  check(sal >=0)
  )

alter table emp modify (sal not null);

alter table emp drop constraint emp_sal_ck;

select * from user_constraints where table_name='EMP'

ALTER TABLE DEPT MOVE;

ALTER TABLE DEPT MOVE TABLESPACE USERS;

ALTER TABLE DEPT ENABLE ROW MOVEMENT;

ALTER TABLE DEPT SHRINK SPACE;

ALTER TABLE DEPT DISABLE ROW MOVEMENT;

cooldog

alter tablespace users offline immediate;
select tablespace_name,status from dba_tablespaces;
select * from v$recover_file;
select * from v$recovery_log;
recover datafile 4
recover tablespace users;


STARTUP MOUNT
RECOVER DATABASE
ALTER DATABASE OPEN;


insert into  t1 values(sysdate);
create table t1 (a date);



NLS_DATE_FORMAT=yyyy-mm-dd-hh24:mi:ss

recover database until cancel using backup controlfile;

alter database open resetlogs;

SHOW PARAMETER control_file_record_keep_time;

ALTER SYSTEM SET control_file_record_keep_time=14;

rman target /

report schema;

show all;

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

CONFIGURE RETENTION POLICY CLEAR;

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'F:\BACKUP\%U.bak' MAXPIECESIZE 2G;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

backup datafile 5;

backup tablespace users;


backup database;


backup archivelog all;

backup database plus archivelog delete all input;

list backupset summary;

list backupset 16;

list backup of tablespace users;

delete backupset 1;

delete archivelog until time 'sysdate-1';

report obsolete;

delete obsolete;


  backup as compressed backupset database;



  backup incremental level 0 database;


   backup incremental level 2 database;

   backup incremental level 2 cumulative database;


   show parameter db_recovery_file_dest

   alter system set db_recovery_file_dest='f:\backup';

   alter system set db_recovery_file_dest_size=10g;
  

  alter system set log_archive_dest_1='location=use_db_recovery_file_dest';

   select * from v$recovery_file_dest;

  delete archivelog all;

   sql ' alter tablespace users offline immediate';
  
   restore datafile 5;

   restore tablespace users;

    shutdown abort

   startup mount

run {
set until time 'sysdate';
restore database;
recover database;
alter database open resetlogs;
}

exp system/sys full=y file=full.dmp

exp system/sys owner=gary file=gary.dmp

exp -help

imp system/sys file=full.dmp fromuser=gary touser=wei

alter user wei default tablespace users quota unlimited on users;

grant connect,resource to wei;

create directory mydir as 'f:\expdata';

grant read,write on directory mydir to system;

expdp system/sys directory=mydir dumpfile=full.dmp full=y job_name=job1 parallel=2

expdp system/sys attach=job1
   

expdp system/sys directory=mydir dumpfile=gary.dmp schemas=gary job_name=job1 parallel=2

impdp system/sys dumpfile=full.dmp directory=mydir schemas=gary remap_schema=gary:wei TABLE_EXISTS_ACTION=truncate

cooldog

select * from user_ts_quotas;
select * from user_users;

create table emp (
empno number(7),
ename varchar2(20),
sal  number(7,2),
depno number(4)
)

insert into emp values (1001,'GARY',2000,10);

commit;

create table emp1 as select * from emp;

alter table emp add (hire_date date);

alter table emp modify (sal number(10,2));

alter table emp rename column sal to salary

alter table emp drop column salary;

rename emp to employees;

create table dept (
deptno number(4) ,
dname varchar2(20) not null,
loc varchar2(20),
constraint dept_pk primary key (deptno)


insert into dept values(20,'hr','gz');


create table emp (
  empno number(7),
  ename varchar2(20) not null,
  email varchar2(50),
  sal  number(7,2),
  deptno number(2),
  constraint emp_pk primary key (empno),
  constraint emp_email_uk unique (email),
  constraint emp_dep_fk foreign key (deptno) references dept(deptno),
  constraint emp_sal_ck  check(sal >=0)
  )

alter table emp modify (sal not null);

alter table emp drop constraint emp_sal_ck;

select * from user_constraints where table_name='EMP'

ALTER TABLE DEPT MOVE;

ALTER TABLE DEPT MOVE TABLESPACE USERS;

create index emp_sal_idx on emp(sal);

create index emp_dept_sal on emp(deptno,sal);
ALTER TABLE DEPT ENABLE ROW MOVEMENT;

ALTER TABLE DEPT SHRINK SPACE;

create index emp_ename_idx on emp(upper(ename));

select * from user_indexes where table_name='EMP';

select * from user_ind_columns where table_name='EMP';

DROP INDEX EMP_DEPT_SAL;

ALTER INDEX EMP_EMAIL_UK COALESCE;

ALTER INDEX EMP_EMAIL_UK REBUILD TABLESPACE USERS;

CREATE BITMAP INDEX EMP_DEPT_IDX ON EMP(DEPTNO);

create or replace view emp_view_10 as
select * from emp where deptno=10


create or replace view emp_view_s
as
select deptno,count(*) c,avg(sal) a
from emp group by deptno


create or replace view emp_view_s
as
select deptno,count(*) c,avg(sal) a
from emp group by deptno
with read only;

select view_name,text from user_views;

grant create sequence to gary;

create sequence my_seq_1 start with 10 increment by 2 maxvalue 5000 ;

select my_seq_1.nextval from dual;
select my_seq_1.currval from dual;

insert into emp values(my_seq_1.nextval,'gary',
'garywei'||my_seq_1.currval,1000,10)
/


select * from user_sequences;

grant create synonym to gary;

create synonym employees for emp;

grant select on emp to wei;

create synonym emp for gary.emp;

select * from user_synonyms;

grant create public synonym to gary;
grant drop public synonym to gary;


create public synonym emp for emp;

select * from all_synonyms where synonym_name='EMP';

cooldog

select * from user_ts_quotas;
select * from user_users;

create table emp (
empno number(7),
ename varchar2(20),
sal  number(7,2),
depno number(4)
)

insert into emp values (1001,'GARY',2000,10);

commit;

create table emp1 as select * from emp;

alter table emp add (hire_date date);

alter table emp modify (sal number(10,2));

alter table emp rename column sal to salary

alter table emp drop column salary;

rename emp to employees;

create table dept (
deptno number(4) ,
dname varchar2(20) not null,
loc varchar2(20),
constraint dept_pk primary key (deptno)


insert into dept values(20,'hr','gz');


create table emp (
  empno number(7),
  ename varchar2(20) not null,
  email varchar2(50),
  sal  number(7,2),
  deptno number(2),
  constraint emp_pk primary key (empno),
  constraint emp_email_uk unique (email),
  constraint emp_dep_fk foreign key (deptno) references dept(deptno),
  constraint emp_sal_ck  check(sal >=0)
  )

alter table emp modify (sal not null);

alter table emp drop constraint emp_sal_ck;

select * from user_constraints where table_name='EMP'

ALTER TABLE DEPT MOVE;

ALTER TABLE DEPT MOVE TABLESPACE USERS;

create index emp_sal_idx on emp(sal);

create index emp_dept_sal on emp(deptno,sal);
ALTER TABLE DEPT ENABLE ROW MOVEMENT;

ALTER TABLE DEPT SHRINK SPACE;

create index emp_ename_idx on emp(upper(ename));

select * from user_indexes where table_name='EMP';

select * from user_ind_columns where table_name='EMP';

DROP INDEX EMP_DEPT_SAL;

ALTER INDEX EMP_EMAIL_UK COALESCE;

ALTER INDEX EMP_EMAIL_UK REBUILD TABLESPACE USERS;

CREATE BITMAP INDEX EMP_DEPT_IDX ON EMP(DEPTNO);

create or replace view emp_view_10 as
select * from emp where deptno=10


create or replace view emp_view_s
as
select deptno,count(*) c,avg(sal) a
from emp group by deptno


create or replace view emp_view_s
as
select deptno,count(*) c,avg(sal) a
from emp group by deptno
with read only;

select view_name,text from user_views;

grant create sequence to gary;

create sequence my_seq_1 start with 10 increment by 2 maxvalue 5000 ;

select my_seq_1.nextval from dual;
select my_seq_1.currval from dual;

insert into emp values(my_seq_1.nextval,'gary',
'garywei'||my_seq_1.currval,1000,10)
/


select * from user_sequences;

grant create synonym to gary;

create synonym employees for emp;

grant select on emp to wei;

create synonym emp for gary.emp;

select * from user_synonyms;

grant create public synonym to gary;
grant drop public synonym to gary;


create public synonym emp for emp;

select * from all_synonyms where synonym_name='EMP';

lsnrctl status
lsnrctl start
lsnrctl stop

alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=CWGZT200-03)(PORT=1526)))';
alter system register;

alter system set service_names=mydb,mydb1;

cooldog

create table t1 (a int,b varchar2(10))  tablespace mytbs;

create index t1_idx on t1(a) tablespace mytbs;

select tablespace_name,table_name from dba_tables
where table_name like 'T%'

select tablespace_name,table_name,index_name from dba_indexes
where table_name like 'T%'

select * from dba_tablespaces

select * from dba_data_files;

select * from dba_temp_files;

select tablespace_name,sum(bytes)/1024/1024 m
from dba_data_files group by tablespace_name;

select tablespace_name,sum(bytes)/1024/1024 m
from dba_free_space group by tablespace_name

CREATE GLOBAL TEMPORARY TABLE t2 (a int,b int);

create global temporary table GARY.t3 (a int,b int) on commit preserve rows;

select * from dba_rollback_segs

alter system set undo_tablespace=myundo;

alter system set undo_retention=10800;

create table t4 (a int,b int) tablespace mytbs;

insert into t4 select * from t4;

select * from dba_tables where table_name='T4';



select * from dba_segments where segment_name='T4'

select * from dba_extents where segment_name='T4'


alter system set db_16k_cache_size=24m;

create tablespace mytbs1 datafile 'f:/mytbs11.dbf' size 100m blocksize 16k

select * from database_properties where property_name like '%CHARA%'

CREATE USER "GARY" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON "USERS" ACCOUNT UNLOCK
GRANT "CONNECT" TO "GARY"

SELECT * FROM DBA_USERS;

SELECT * FROM DBA_TS_QUOTAS;

ALTER USER GARY IDENTIFIED BY "123GARYWEI";

ALTER USER GARY ACCOUNT LOCK;

ALTER USER GARY ACCOUNT UNLOCK;

ALTER USER GARY QUOTA UNLIMITED ON SYSTEM;

DROP USER GARY CASCADE;

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='GARY';

SELECT * FROM USER_SYS_PRIVS ;

REVOKE CREATE TABLE FROM GARY;

cooldog

create table t1 (a int,b varchar2(10))  tablespace mytbs;

create index t1_idx on t1(a) tablespace mytbs;

select tablespace_name,table_name from dba_tables
where table_name like 'T%'

select tablespace_name,table_name,index_name from dba_indexes
where table_name like 'T%'

select * from dba_tablespaces

select * from dba_data_files;

select * from dba_temp_files;

select tablespace_name,sum(bytes)/1024/1024 m
from dba_data_files group by tablespace_name;

select tablespace_name,sum(bytes)/1024/1024 m
from dba_free_space group by tablespace_name

CREATE GLOBAL TEMPORARY TABLE t2 (a int,b int);

create global temporary table GARY.t3 (a int,b int) on commit preserve rows;

select * from dba_rollback_segs

alter system set undo_tablespace=myundo;

alter system set undo_retention=10800;

create table t4 (a int,b int) tablespace mytbs;

insert into t4 select * from t4;

select * from dba_tables where table_name='T4';



select * from dba_segments where segment_name='T4'

select * from dba_extents where segment_name='T4'


alter system set db_16k_cache_size=24m;

create tablespace mytbs1 datafile 'f:/mytbs11.dbf' size 100m blocksize 16k

select * from database_properties where property_name like '%CHARA%'

CREATE USER "GARY" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON "USERS" ACCOUNT UNLOCK
GRANT "CONNECT" TO "GARY"

SELECT * FROM DBA_USERS;

SELECT * FROM DBA_TS_QUOTAS;

ALTER USER GARY IDENTIFIED BY "123GARYWEI";

ALTER USER GARY ACCOUNT LOCK;

ALTER USER GARY ACCOUNT UNLOCK;

ALTER USER GARY QUOTA UNLIMITED ON SYSTEM;

DROP USER GARY CASCADE;

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='GARY';

SELECT * FROM USER_SYS_PRIVS ;

REVOKE CREATE TABLE FROM GARY;

GRANT ALL ON A TO WEI;

SELECT * FROM USER_TAB_PRIVS;

GRANT UPDATE(A) ON "GARY"."A" TO "WEI"

select * from user_role_privs;

select * from session_privs;

select * from role_sys_privs;
select * from role_tab_privs;
select * from role_role_privs;

alter system set resource_limit=true;

utlpwdmg.sql

sqlplus / as sysdba
@ utlpwdmg.sql
select * from v$pwfile_users;
grant sysdba to gary;
revoke sysdba from gary;

alter system set remote_login_passwordfile=shared scope=spfile;

orapwd file=PWDmydb.ora password=sys

[ 本帖最后由 cooldog 于 2008-1-6 15:02 编辑 ]

cooldog

sqlplus as sysdba
实例的参数

pfile  txt  
spfile binary sql指令编辑
$oracle_home/

windows:  database\init<sid>.ora
unix   :  dbs/init<sid>.ora


start if spfile exists it uses spfile ; otherwise it use pfile; the last option is init.ord

sql>show parameter spfile

sql>create pfile from spfile;
sql>create pfile='d:\wjpfile.ora' from spfile
sql> shwo parameter db_cache_size=128m
sql> startup force
alter system set db_cache_siez=120m scope=spfile ;
                                    scope=memory;
                                    scope=both

sga+pga < 70%
sga < 40$
sga  manual

sga_max_size

pga_aggregate_target

oerr ORA 600
oerr ORA 11010

select * from user_indexes;
select * from user_names;

SQL> alter system set control_files ='e:\oracle\product\10.2.0\oradata\orcl\control.01.ctl','e:\orac
le\product\10.20\oradata\orcl\control.02.ctl'
'e:\oracle\product\10.20\oradata\orcl\control.02.ctl'


select username from dba_users;
alter user scott account unlock;
select * from user_tables;
select * from user_indexes;
select * from user_constraints;
select * from user_views;
select * from user_sequences;
select * from user_synonyms;
select owner,count(*) from dba_tables group by owner;

select paddr,machine,program,username from v$session;

select * from v$process where addr='2EE48AAC';

select table_name from dict where table_name like '%TABLE%';

1158/em