oracle基础

Oracle , Comments

Oracle Common Command

如果无法发现crs_*命令，应执行su - grid; . oraenv; +ASM
oracle 中不用引号或是双引号，不能是单引号

编辑相关

1
2
3
4
5
6
7
8
9
10
11
12
-- define editor for `ed`
sql> define _editor=vim
[oracle@localhost] oerr ora 00923
SQL> ! pwd
SQL> host pwd
SQL> ho shell-command
SQL> clear screen
SQL> cle scr
SQL> show user;
SQL> set linesize line_length;
SQL> set pagesize page_size;

开启归档模式

1
2
3
4
5
shutdown immediate;
startup mount;
alter database archivelog; -- 开启归档日志
alter database open; -- 开启数据库
archive log list; --查看归档日志状态

关闭归档模式

1
2
3
4
5
shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;
archive log list;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
-- show-related
show parameter param_name;
show parameter spfile
show parameter pfile
show parameter control%
show parameter control_
show parameter db_writer_processes
show parameter log_archive
show parameter db_recovery
show parameter db_name;(参数文件中唯一必须确定赋值的参数)
show parameter db_unique_name;
show parameter open_cursors;
alter system set open_cursors=400 scope=both;

processes; 进程数
audit_trail; 关闭审计功能
deferred_sement_creation; 关闭延迟段空间分配
undo_retention; undo保留期
max_dump_file_size;
parallel_max_servers; 并行子进程的数量上限
control_file_record_keep_time; 控制文件记录保留时间
result_cache_max_size; 关闭 result cache
event（28401 trace name context forever,level 1）; 关闭用户持续输入密码带来的延迟用户验证


alter system set event='28401 trace name context forever,level 1' scope=spfile;



NAME             TYPE  VALUE
------------------------------------ -----------
db_unique_name           string  myora

show parameter local_listener;
show parameter instance_name;
show parameter service_names;
show parameter db_create_file_dest;


-- alter-related
alter database archivelog;
alter database mount;
alter database open;
alter database close;
alter database dismount;


alter system register;

set linesize 999 999
set pagesize 999

-- select-related
select * from v$version;
select value from v$parameter where name = 'db_name';
alter system kill session 'sid,serial#';
select MEMBER from v$logfile;
select * from dictionary where table_name like 'V$SYS%';
select parallel from v$instance;
select protection_level from v$database;
select * from dba_streams_administrator;
select log_mode from v$database;
select program from v$process order by program;
select program from v$session order by program;
select owner,count(table_name) as COUNT,tablespace_name from dba_tables group by owner,tablespace_name
select segment_type,count(1) from dba_segments group by segment_type order by segment_type;
select member,bytes from v$log join v$logfile using (group#);

select count(*) from dba_tab_privs where grantee='PUBLIC';
select table_name from dba_tab_privs where grantee='PUBLIC' and privilege='EXECUTE' and table_name like 'UTL%'
select file_name,tablespace_name,round(bytes/1024/1024) as "SIZE" from dba_data_files;
select name from v$bgprocess where paddr<>'00';
select count(name) from v$bgprocess where paddr<> '00' and name like 'DB%';
select privilege from dba_sys_privs where grantee='RECOVERY_CATALOG_OWNER'





-- 查找数据库名
show parameter db_name;
select name from v$database;

-- 查找实例名
show parameter instance_name;
select instance_name from v$instance;


-- alter-related
alter database add logfile member '+DATA/myora/onlinelog/group_1a' to group 1, '+DATA/myora/onlinelog/group_2a' to group 2, '+DATA/myora/onlinelog/group_3a' to group 3;

alter system switch logfile;
1
2
3
4
5
6

-- create new tablespace
select name from v$datafile;
create tablespace noncrit datafile '+DATA/myora/datafile/noncrit.dbf' size 2m;
create table ex16(c1 date) tablespace noncrit;
commit;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

-- a means string
col col_name for aNum
    col machine for a20

SQL> col machine for a10
SQL> col machine for a10
SQL> set pagesize 9999
select SID,SERIAL#,MACHINE,TERMINAL from v$session;

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE

6 rows selected.


-- user-related

sqlplus /nolog;
conn / as sysdba;
[oracle@localhost] sqlplus scott/tiger@myora
[oracle@localhost] sqlplus hr/hr@192.168.1.32:1521/myora/

SQL> alter user hr account unlock;

SQL> alter user hr identified by hr account unlock;

SQL> alter user hr identified by "hr" account unlock;

SQL> select table_name,tablespace_name from user_tables;



SQL> select status,name from v$datafile

STATUS  NAME
------- --------------------------------------------------
SYSTEM  +DATA/myora/datafile/system.256.1015819375
ONLINE  +DATA/myora/datafile/sysaux.257.1015819375
ONLINE  +DATA/myora/datafile/undotbs1.258.1015819375
ONLINE  +DATA/myora/datafile/users.259.1015819375
ONLINE  +DATA/myora/datafile/example.265.1015819467



SQL> select status,name,bytes/1024/1024 as tablespce_size from v$datafile;

STATUS  NAME               TABLESPCE_SIZE
------- -------------------------------------------------- --------------
SYSTEM  +DATA/myora/datafile/system.256.1015819375          680
ONLINE  +DATA/myora/datafile/sysaux.257.1015819375          510
ONLINE  +DATA/myora/datafile/undotbs1.258.1015819375           85
ONLINE  +DATA/myora/datafile/users.259.1015819375     5
ONLINE  +DATA/myora/datafile/example.265.1015819467         100


SQL> r
  1* select component,min_size,max_size,current_size from v$sga_dynamic_components

COMPONENT                   MIN_SIZE   MAX_SIZE CURRENT_SIZE
------------------------- ---------- ---------- ------------
shared pool                209715200  218103808    218103808
large pool                   4194304    4194304      4194304
java pool                    4194304    4194304      4194304
streams pool                       0          0            0
DEFAULT buffer cache       255852544  264241152    255852544
KEEP buffer cache                  0          0            0
RECYCLE buffer cache               0          0            0
DEFAULT 2K buffer cache            0          0            0
DEFAULT 4K buffer cache            0          0            0
DEFAULT 8K buffer cache            0          0            0
DEFAULT 16K buffer cache           0          0            0
DEFAULT 32K buffer cache           0          0            0
Shared IO Pool                     0          0            0
ASM Buffer Cache                   0          0            0

14 rows selected.


SQL> r
  1* select name,value from v$pgastat where name in ('maximum PGA allocated','total PGA allocated')

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
total PGA allocated                                               156115968
maximum PGA allocated                                             216137728
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select open_mode from v$database;

select status from v$instance;

-- connect to rdbms instance
SQL> show parameter instance_type;

NAME                     TYPE     VALUE
------------------------------------ ---
instance_type                 string     RDBMS


-- connect to ASM instance
sqlplus / as sysasm
SQL> show parameter instance_type;

NAME                     TYPE     VALUE
------------------------------------ --
instance_type                 string     asm



-- 查看执行计划
explain plan for SQL;

集群相关

1
2
crs_stat -t
crsctl stat res -t

执行的时候@example.sql，如果文件的后缀名为*.sql,直接用文件名也可以.

LISTENER

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 建立监听器 listener.ora
netca
-- 注册实例至监听器
-- 名称解析(为了找到监听器)
    -- easy connect
conn hr/hr@192.168.1.32:1521/myora;
show user;
conn hr/hr@rhelora:1521/myora;
conn hr/hr@rhelORA:1521/myora;
    -- 本地命名法 tnsnames.ora
    -- 目录命名法
    -- 第三方命名法
-- 配置服务别名
netmgr

oracle 在分布式事务中执行两阶段提交

1
2
3
4
5
6
7
8
9
10

-- rman-related
[oracle@localhost] rman target /
RMAN> report schema;
report need backup;
report obsolete;
backup as compressed backupset datafile 4;
list backup;
list backup of datafile 4;
delete obsolete;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 配置数据库Flashback
alter system set db_recovery_file_dest_size=1G;
alter system set db_recovery_file_dest='/u01/app/oracle/oradata/flash_recovery_area';
alter system set db_flashback_retention_target=240;
shutdown immediate;
startup mount;
alter database flashback on;
alter database open;

-- flashback-related
select flashback_on from v$database;
select retention_target,flashback_size,oldest_flashback_time from v$flashback_database_log;
select end_time,flashback_data,db_data,redo_data from v$flashback_database_stat;
select * from v$sgastat where name = 'flashback generation buff';

素丶素丶丶wodetian

忍一时越想越气，退一步越想越亏。