oracle 基本技巧总结

jack_gogo 13年前

/**************表空间管理******************、

create tablespace "traineeManage" datafile 'E:\app\jack\oradata\orcl\trainmanage' size 100M autoextend on next 1 M
maxsize unlimited logging extent management local segment space management auto;

create smallfile temporary tablespace "traineeManageSys_Temp" tempfile 'F:\trainManageSys\data\traineeManageSys_temp.ldf'
size 100m autoextend on next 1 m maxsize unlimited extent management local uniform size 1m;

alter database default tablespace traineeManage;//创建默认表空间

drop tablespace "traineeManage" including contents and datafiles;
drop tablespace "traineeManage_Temp" including contents and datafiles;

/****************用户管理************************/

create user 用户名 identified by 密码 default tablespace "traineeManageSys" temporary tablespace
"traineeManageSys_Temp" profile default account unlock;

drop user 用户名;

/*****************授权管理**************/

grant connect,dba,resource to 用户名;
grant unlimited tablespace to 用户名;

/*****************创建备份目录并且赋予相应权限*****************、

create directory our_dir （目录名）as 'F:\trainManageSys\data\backup';（指定路径）
grant read,write on directory our_dir to our;

/***********创建自动增长************************/

create sequence T_resgist_seq increment by 1 start with 1 nomaxvalue nocycle nocache;

create trigger tri_res before insert on T_resgistration_way
for each row when (new.resgistion_way_id is null)
   begin
      select T_resgist_seq.Nextval into:new.resgistion_way_id from dual;
    end;

/**************oralce的启动和关闭***********/

startup;

SQL>shutdown normal
SQL>shutdown transactional
SQL>shutdown immediate
SQL>shutdown abort

/****************pfile ,spfile管理**************/

##用生成对应SID的spfile生成pfile，生成的pfile位置：

$ORACLE_HOME/dbs/init$ORACLE_SID.ora
SQL> create pfile from spfile;

-------------------------------------------------------------------
##自己指定生成文件的位置
SQL> create pfile='/home/oracle/initorcl.ora' from spfile;

/***********半小时采集一次，采集信息保存9天*****************/

exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>9*24*60);

/******取消自动收集**************/

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0,retention=>9*24*60);

注：10g，11g默认是自动开启awr信息收集的，会对系统有一定的影响（很小）；如果要关闭awr信息收集，只需设置interval参数为0即可。但interval设0后，AWR报告无法生成;注：报告生成在$ORACLE_HOME/rdbms/admin/目录下

/****************修改sga参数*****************/

修改sga的原则是：

sga_target < sga_max_size;

sga_target 加上pga等进程占用的内存必须小于机器物理内存（像我的机器内存是4G）

修改代码如下：

alter system set sga_max_size = 2048m scope=spfile;

aleter system set sga_target=2048m scope=spfile;

##修改SGA的相关参数，只能是spfile，然后重启数据库。不能直接scope=both!

/*******************查询oracle配置参数************************/

show parameter；

具体查询某个参数，只需输入具体参数即可，如查询sga-target

show parameter sga_target;

SQL> show parameter sga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1G
sga_target                           big integer 1G
SQL>
/*************redo,logfile管理**************************/

日志切换时间间隔查询

SELECT to_char(b.first_time, 'YYYY-MM-DD HH24:MI:SS') as swtich_time, (b.first_time - a.first_time) * 24 as "switch_interval(hr)" FROM v$log_history a, v$log_history b WHERE a.SEQUENCE# + 1 = b.SEQUENCE# ORDER BY SWTICH_TIME;

SQL> SELECT to_char(b.first_time, 'YYYY-MM-D
as "switch_interval(hr)" FROM v$log_history
TICH_TIME;

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-01-09 10:13:10          .035277778
2012-01-09 10:13:24          .003888889
2012-01-09 10:14:09               .0125
2012-01-09 10:14:49          .011111111
2012-01-09 10:21:50          .116944444
2012-01-09 17:24:32               7.045
2012-01-11 14:22:52          44.9722222
2012-01-11 15:00:21          .624722222
2012-01-13 16:53:52          49.8919444
2012-01-31 09:49:26          424.926111
2012-02-03 09:07:15          71.2969444

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-02-03 13:48:05          4.68055556
2012-02-10 09:14:11             163.435
2012-02-13 10:02:22          72.8030556
2012-02-14 08:34:13          22.5308333
2012-02-15 09:49:32          25.2552778
2012-02-16 08:39:12          22.8277778
2012-02-16 17:35:09              8.9325
2012-02-17 08:35:26          15.0047222
2012-02-20 08:35:05          71.9941667
2012-02-21 08:24:16          23.8197222
2012-02-22 08:34:42          24.1738889

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-02-22 11:38:55          3.07027778
2012-02-22 13:56:41          2.29611111
2012-02-23 08:32:39          18.5994444
2012-02-23 14:07:11          5.57555556
2012-02-24 08:22:57          18.2627778
2012-02-29 17:10:28          128.791944
2012-02-29 17:40:30          .500555556
2012-03-05 09:25:31          111.750278
2012-03-05 09:50:44          .420277778
2012-03-05 10:51:01          1.00472222
2012-03-05 13:19:41          2.47777778

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-03-06 08:22:26          19.0458333
2012-03-06 09:48:05              1.4275
2012-03-06 15:05:24          5.28861111
2012-03-07 08:40:33          17.5858333
2012-03-08 08:42:51          24.0383333
2012-03-09 08:25:47          23.7155556
2012-03-09 08:57:53                .535
2012-03-13 14:51:01          101.885556
2012-03-14 08:26:40          17.5941667
2012-03-15 09:41:35          25.2486111
2012-03-16 08:21:46          22.6697222

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-03-19 08:38:25             72.2775
2012-03-19 16:22:39          7.73722222
2012-03-20 08:27:44          16.0847222
2012-03-22 08:30:07          48.0397222
2012-03-23 08:28:48          23.9780556
2012-03-23 16:46:08          8.28888889
2012-03-26 08:29:54          63.7294444
2012-03-26 13:04:32          4.57722222
2012-03-26 14:11:44                1.12
2012-03-27 08:26:03          18.2386111
2012-03-28 09:08:28          24.7069444

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-03-29 09:26:45          24.3047222
2012-04-05 15:43:23          174.277222
2012-04-05 16:13:23                  .5
2012-04-06 08:55:31          16.7022222
2012-04-10 09:30:57          96.5905556
2012-05-16 17:15:09          871.736667
2012-05-17 08:42:32          15.4563889

已选择62行。--------
##前一百条记录（反序）
SELECT to_char(b.first_time, 'YYYY-MM-DD HH24:MI:SS') as swtich_time, (b.first_time - a.first_time) * 24 as "switch_interval(hr)" FROM v$log_history a, v$log_history b WHERE a.SEQUENCE# + 1 = b.SEQUENCE# AND ROWNUM <= 100 ORDER BY SWTICH_TIME desc;

---------------

SQL> SELECT to_char(b.first_time, 'YYYY-MM-DD
as "switch_interval(hr)" FROM v$log_history a,
<= 100 ORDER BY SWTICH_TIME desc;

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-05-17 08:42:32          15.4563889
2012-05-16 17:15:09          871.736667
2012-04-10 09:30:57          96.5905556
2012-04-06 08:55:31          16.7022222
2012-04-05 16:13:23                  .5
2012-04-05 15:43:23          174.277222
2012-03-29 09:26:45          24.3047222
2012-03-28 09:08:28          24.7069444
2012-03-27 08:26:03          18.2386111
2012-03-26 14:11:44                1.12
2012-03-26 13:04:32          4.57722222

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-03-26 08:29:54          63.7294444
2012-03-23 16:46:08          8.28888889
2012-03-23 08:28:48          23.9780556
2012-03-22 08:30:07          48.0397222
2012-03-20 08:27:44          16.0847222
2012-03-19 16:22:39          7.73722222
2012-03-19 08:38:25             72.2775
2012-03-16 08:21:46          22.6697222
2012-03-15 09:41:35          25.2486111
2012-03-14 08:26:40          17.5941667
2012-03-13 14:51:01          101.885556

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-03-09 08:57:53                .535
2012-03-09 08:25:47          23.7155556
2012-03-08 08:42:51          24.0383333
2012-03-07 08:40:33          17.5858333
2012-03-06 15:05:24          5.28861111
2012-03-06 09:48:05              1.4275
2012-03-06 08:22:26          19.0458333
2012-03-05 13:19:41          2.47777778
2012-03-05 10:51:01          1.00472222
2012-03-05 09:50:44          .420277778
2012-03-05 09:25:31          111.750278

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-02-29 17:40:30          .500555556
2012-02-29 17:10:28          128.791944
2012-02-24 08:22:57          18.2627778
2012-02-23 14:07:11          5.57555556
2012-02-23 08:32:39          18.5994444
2012-02-22 13:56:41          2.29611111
2012-02-22 11:38:55          3.07027778
2012-02-22 08:34:42          24.1738889
2012-02-21 08:24:16          23.8197222
2012-02-20 08:35:05          71.9941667
2012-02-17 08:35:26          15.0047222

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-02-16 17:35:09              8.9325
2012-02-16 08:39:12          22.8277778
2012-02-15 09:49:32          25.2552778
2012-02-14 08:34:13          22.5308333
2012-02-13 10:02:22          72.8030556
2012-02-10 09:14:11             163.435
2012-02-03 13:48:05          4.68055556
2012-02-03 09:07:15          71.2969444
2012-01-31 09:49:26          424.926111
2012-01-13 16:53:52          49.8919444
2012-01-11 15:00:21          .624722222

SWTICH_TIME         switch_interval(hr)
------------------- -------------------
2012-01-11 14:22:52          44.9722222
2012-01-09 17:24:32               7.045
2012-01-09 10:21:50          .116944444
2012-01-09 10:14:49          .011111111
2012-01-09 10:14:09               .0125
2012-01-09 10:13:24          .003888889
2012-01-09 10:13:10          .035277778

已选择62行。

/****************查询当前的日志设置情况：***************/

SQL> col member for a40;
SQL> select * from v$logfile;

    GROUP# STATUS TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
         3         ONLINE E:\APP\JACK\ORADATA\ORCL\REDO03.LOG      NO
         2         ONLINE E:\APP\JACK\ORADATA\ORCL\REDO02.LOG      NO
         1         ONLINE E:\APP\JACK\ORADATA\ORCL\REDO01.LOG      NO

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIME     NEXT_CHANGE# NEXT_TIME
---------------- ------------- -------------- ------------ --------------
         1          1         64   52428800        512          1 NO
CURRENT                2907057 17-5月 -12       2.8147E+14

2 1 62 52428800 512 1 NO
INACTIVE 2856611 16-5月 -12 2879698 17-5月 -12

3 1 63 52428800 512 1 NO
INACTIVE 2879698 17-5月 -12 2907057 17-5月 -12

/***************添加临时日志组*****************/

SQL> alter database add logfile group 4 ('E:\APP\JACK\ORADATA\ORCL\REDO04.LOG') size 150m;

将当前的online redo log 切换到新增的redo log group上

SQL> alter system switch logfile;

/************删除online redo log ****************/

SQL> alter database drop logfile group 1;

//然后需要手动在对应目录下，手动删除redo logfile文件。
SQL> !
[oracle@orcal-50 ~]$ cd 'E:\APP\JACK\ORADATA\ORCL\REDO01.LOG'
[oracle@orcal-50 ORA10G]$rm -rf redo01.log

/* ***********归档控制*******************、

开启归档

SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog
SQL> alter database open

如：SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             268435920 bytes
Database Buffers          796917760 bytes
Redo Buffers                4603904 bytes
数据库装载完毕。
SQL> alter database archivelog;

数据库已更改。

SQL> alter database open;

数据库已更改。

查询归档：

SQL> archive log list
如：SQL> archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     62
下一个存档日志序列   65
当前日志序列           65

关闭归档：

SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog
SQL> alter database open
如下：

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

SQL> alter database noarchivelog;

数据库已更改。

SQL> alter database open；
SQL> alter database noarchivelog;</div>

数据库已更改。

SQL> alter database open;

数据库已更改。

oracle 基本技巧总结

相关经验

目录