DOS下的查FIND命令：
FIND /I /N "OPEN_CURSORS" SPFILETESTDB.ORA
SQL> alter system reset open_cursors SCOPE=spfile SID='*'
详见《spfile-reset.txt》
$export LANG=en_US.UTF-8
alter session set nls_language=american;
日期格式
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set nls_date_format='YYYY-MM-DD HH:MI:SS AM';
SELECT SYSDATE FROM DUAL;
alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24.MI.SSXFF';
alter session set NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24.MI.SSXFF';
SELECT SYSTIMESTAMP FROM DUAL;
启用/不启用闪回日志记录，在MOUNT状态下
alter database flashback on|off;
select FLASHBACK_ON from v$database;
select * from nls_database_parameters;
select * from nls_instance_parameters;
select * from nls_session_parameters;
SELECT * FROM V$NLS_VALID_VALUES
SELECT * FROM V$NLS_PARAMETERS
//关于回滚段
select distinct segment_type from dba_segments;
select segment_type,count(segment_type) from dba_segments group by segment_type;
select * from dba_rollback_segs;
show user //显示当前用户
select * from v$version;
ALTER USER xxx ACCOUNT LOCK|UNLOCK;
ALTER USER xxx PASSWORD EXPIRE;用xxx登录输入新口令
select * from v$pwfile_users;
031
show sga
show parameter sga_max_size //SGA最大值
show parameter shared_pool
show parameter db_cache
show parameter log_buffer
alter system set db_cache_size=xxM //更改
select * from v$bgprocess;
select * from v$datafile;
select * from v$controlfile;
select * from v$logfile;
select * from v$log;
select * from v$tempfile;
dbca
netca
netmgr
runInstaller
oemapp console
orapwd file=<fname> password=<password> entries=<users>
select * from v$pwfile_users; 查特权用户
select name,value from v$system_parameter; 查询系统参数
alter system set xxx=value scope=memory|spfile|both;
alter session set yyy=value;
create spfile=<fname> from pfile;
create pfile=<fname> from spfile;
create tablespace test1 datafile '/opt/ora9/oradata/test/test1.dbf' size 10m;
drop tablespace test1 including contents and datafiles;
startup [nomount|mount|open]
pfile=
restrict
recovery
force
startup nomout;
alter database mount;
alter database open [read only];
alter system enable restricted session;
alter system disable restricted session;
shutdown normal|transaction|immediate|abort
----05
select * from dba_users;
select * from dba_objects;
select * from dba_tables;
select * from all_tables;
select * from user_tables;
desc dictionary //数据字典
select * from dictionary
desc v$fixed_table //动态性能表
select * from v$fixed_table;
set serveroutput on
execute dbms_output.put_line('hello!')
-----6
select * from v$tablespace;
alter tablespace users begin backup;
select * from v$backup;
alter tablespace users end backup;
//显示已归档的日志
select * from v$archived_log;
//显示控制文件的信息
select * from v$controlfile;
select * from v$parameter where name like 'control%';
show parameter control;
show parameter control_files
select * from v$controlfile_record_section;
alter system set control_files=
'/opt/ora9/oradata/ora9i/control01.ctl',
'/opt/ora9/oradata/ora9i/control02.ctl',
'/opt/ora9/oradata/ora9i/control03.ctl',
'/opt/ora9/oradata/ora9i/control04.ctl'
scope=spfile;
show parameter spfile;
show parameter pfile;
startup pfile=/opt/ora9/product/9.2/dbs/initora9i.ora //注意这个目录是指本机。
备份控制文件的方法：
1、shutdown后，拷贝控制文件
2、在线备份:
alter database backup controlfile to '/opt/ora9/oradata/ora9i/control.bak';
或 alter database backup controlfile to trace; //在udump下产生了一个trace文件，该文件包含生成控制文件所需要的脚本。
-----7
alter system switch logfile; //强制日志switch
alter system checkpoint;
//增加日志组
alter database add logfile group 4
'/opt/ora9/oradata/LMOA/redo04.ora' size 10m;
alter database add logfile group 5
('/opt/ora9/oradata/LMOA/redo05A.ora','/opt/ora9/oradata/LMOA/redo05B.ora') size 10m;
//增加日志组的成员
alter database add logfile member
'/opt/ora9/oradata/LMOA/redo03B.ora' to group 3,
'/opt/ora9/oradata/LMOA/redo04B.ora' to group 4;
//删除日志组
alter database drop logfile group 4;
//删除日志组的成员
alter database drop logfile member '/opt/ora9/oradata/LMOA/redo04B.ora'
//清除在线日志
alter database clear logfile group 1;
alter database clear unarchived logfile group 1; //清除未归档日志，需马上作full backup
//更改日志文件
alter database rename file '/opt/ora9/oradata/test/redo01.log'
to '/opt/ora9/oradata/test/redo01a.log';
设置归档
shutdown immediate
startup mount
alter database archivelog/noarchivelog
alter database open;
archive log list;
full db backup
启动自动存档
[alter system] archive log start/stop;
手工归档所有日志
alter system archive log all;
查看已归档的日志文件
select * from v$archived_log;
日志挖掘
logmnr--1) set directory utl_file_dir
2) restartup
3) create directory file
dbms_logmnr_d.build
4) add/remove log file
dbms_logmnr.add_logfile
dbms_logmnr.remove_logfile
5) start logmnr
dbms_logmnr.start_logmnr
6) v$logmnr_content--sqlredo/sqlundo
-----8
//获取表空间等信息
select * from dba_tablespaces;
select * from v$tablespace;
select * from dba_data_files;
select * from v$datafile;
select * from dba_temp_files;
select * from v$tempfile;
//不包括临时文件
select t1.name,t2.name
from v$tablespace t1,v$datafile t2
where t1.ts#=t2.ts#;
//包括临时文件
select t1.name,t2.name
from v$tablespace t1,(select ts#,name from v$datafile union select ts#,name from v$tempfile) t2
where t1.ts#=t2.ts#;
select * from dba_rollback_segs; //系统回滚段
alter user licg quota 10m on users;
alter user licg quota unlimited on users;
create tablespace test
datafile '/opt/ora9/oradata/test/test.dbf' size 100M
autoextend on next 5M maxsize 200M;
create tablespace stk_ts
datafile '/opt/ora9/oradata/test/stk_ts01.dbf' size 20M
extent management local uniform size 512k/autoallocate;
create undo tablespace undo1
datafile '/opt/ora9/oradata/test/undo101.dbf' size 100m;
create temporary tablespace temp1
tempfile '/opt/ora9/oradata/test/temp101.dbf' size 50m
extent management local uniform size 10m;
alter database default temporary tablespace temp1;
alter tablespace xxx offline|online;
alter tablespace xxx read only;
drop tablespace xxx; 再删除OS文件
drop tablespace xxx including contents and datafiles;
//扩张
alter database datafile '/opt/ora9/oradata/test/test.dbf' resize 150M;
alter database datafile '/opt/ora9/oradata/test/test.dbf' autoextend on;
alter tablespace users add datafile '/opt/ora9/oradata/test/users02.dbf' size 10m;
//移动表空间
alter tablespace test offline;
移动OS文件
alter tablespace test
rename datafile '/opt/ora9/oradata/test/test.dbf'
to '/opt/ora9/oradata/test/test01.dbf';
alter tablespace test online;
//移动系统表空间
start mount;
移动OS文件
alter database
rename file '/opt/ora9/oradata/test/system01.dbf'
to '/opt/ora9/oradata/test/system.dbf';
alter database open;
-----9
扩展段
alter table test allocate extent(size 1m);
alter table test deallocate unused;
select * from dba_tablespaces;
select * from dba_data_files;
select * from dba_free_space;
select * from dba_segments;
select * from dba_extents;
//计算某对象占的空间
select sum(bytes) from dba_extents
where owner='SYS' and segment_name='TEST';
----10
alter tablespace users offline immediate;
recover datafile '/opt/ora9/oradata/test/users01.dbf';
或者recover datafile 5;
alter tablespace users online;
alter system set undo_tablespace=undo1 //可建多个UNDO表空间，但一个实例在任何时刻只能使用一个
execute dbms_flashback.enable_at_time('26-JAN-04:12:17:00 pm');
select * from xxx;
execute dbms_flashback.disable;
select end_time,begin_time,undoblks from v$undostat;
select * from dba_rollback_segs;
select * from v$rollname;
select * from v$rollstat;
select * from v$undostat;
select * from v$session;
select * from v$transaction;
----11
desc dbms_rowid
create global temporary table xxx
on commit delete|preserve rows
as select * from test;
alter table test allocate extent(size 500k,datafile '...');
alter table test deallocate unused [keep 2K];
alter table test move tablespace data1;
//建索引
create index testindex1
on test(id) tablespace users;
alter index testindex1 rebuild;
drop table test cascade constraint;
alter table test drop column yyy [cascade constraints];
alter table test set unused column yyy [checkpoint 1000];
alter table test drop unused columns [continue] [checkpoint 1000];
select * from dba_tables;
select * from dba_objects;
-----12
alter inde xxx coalesce;
analyze index xxx validate structure;
desc index_stats;
select * from user_indexes;
alter index xxx monitoring|nomonitoring usage;
select * from dba_indexes;
select * from dba_ind_columns;
select * from dba_ind_expressions;
select * from v$object_usage;
-----13
alter table authors
add constraint pk_1 primary key(au_id);
alter table authors
add constraint uk_1 unique(au_lanme,au_fname);
alter table sales
add constraint fk_sales foreign key(stor_id) references stores(stor_id);
alter table stores
add constraint ck_stores_1 check(stor_name like 'B%')
[enable novalidate];
alter table authors drop constraint pk_1;
select * from dba_constraints;
select * from dba_cons_columns;
-----14
alter user test account lock|unlock|password expire;
SQL>password
select * from dba_users;
selecT * frm dba_profiles;
-----15
create user test
identified by tttt
default tablespace users
temporary tablespace temp1
quota 15m on users
password expire;
alter user testuser quota 10m on temp;
select * from dba_users;
select * from dba_ts_quotas;
-----16
DBA_SYS_PRIVS lists system privileges granted to users and roles
SESSION_PRIVS lists the privileges that are currently available to the user
DBA_TAB_PRIVS lists all grants on all objects in the database
DBA_COL_PRIVS describes all object column grants in the database.
-----17
DBA_ROLES All roles that exist in the database
DBA_ROLE_PRIVS Roles granted to users and roles
ROLE_ROLE_PRIVS Roles that are granted to roles
DBA_SYS_PRIVS System privileges granted to users and roles
ROLE_SYS_PRIVS System privileges granted to roles
ROLE_TAB_PRIVS Object privileges granted to roles
SESSION_ROLES Roles that the user currently hasenabled
-----18
create tablespace testtb datafile 'D:\oracle\oradata\sales\testtb01.dbf' size 5M
AUTOEXTEND ON NEXT 2M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
alter tablespace testtb add datafile 'D:\oracle\oradata\sales\testtb02.dbf' size 5m;
alter database datafile 'D:\oracle\oradata\sales\testtb01.dbf' autoextend off;
alter database datafile 'D:\oracle\oradata\sales\testtb02.dbf' autoextend on;
select * from USER_OBJECTS WHERE status!='VALID';
select * from USER_INDEXES WHERE status!='VALID';
declare incr number := 1;
begin
update t1 set age=age+incr;
commit;
end;
create or replace procedure inc_age(incr number) as
begin
update t1 set age=age+incr;
commit;
end;
create or replace package numbers
as
function odd_even(v1 number) return varchar2;
procedure ins_ints(v1 in number);
end numbers;
----改日志文件大小-------------------------------------
alter database add logfile group 4 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\CTS\REDO04.LOG' size 100m;
alter database add logfile group 5 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\CTS\REDO05.LOG' size 100m;
alter system archive log current;
alter system archive log current;
alter system checkpoint;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database add logfile group 1 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\CTS\REDO01.LOG' size 100m;
alter database add logfile group 2 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\CTS\REDO02.LOG' size 100m;
alter database add logfile group 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\CTS\REDO03.LOG' size 100m;
alter system archive log current;
alter system archive log current;
alter system checkpoint;
alter database drop logfile group 4;
alter database drop logfile group 5;
-- MERGE 命令 ------------------------------------------
MERGE INTO test D
USING (SELECT id,name FROM test1 WHERE id <=18) S
ON (D.id = S.id)
WHEN MATCHED THEN UPDATE SET D.name = S.name DELETE WHERE (S.id >=5)
WHEN NOT MATCHED THEN INSERT (D.id, D.name) VALUES (S.id, S.name);
commit;
MERGE INTO test D
USING (SELECT id,name FROM test1 WHERE id <=18) S
ON (D.id = S.id)
WHEN MATCHED THEN UPDATE SET D.name = S.name WHERE (S.id >=5)
WHEN NOT MATCHED THEN INSERT (D.id, D.name) VALUES (S.id, S.name)
WHERE (S.id <=16);
commit;
MERGE INTO test D
USING (SELECT id,name FROM test1 WHERE id <=18) S
ON (D.id = S.id)
WHEN MATCHED THEN UPDATE SET D.name = S.name
WHEN NOT MATCHED THEN INSERT (D.id, D.name) VALUES (S.id, S.name);
commit;
delete test where id>10;
update test set name=id;
commit;
select * from test order by id;
select * from test1 order by id;