Monthly Archives: June 2013

Tips about oracle SQL Profile

What is a SQL Profile?

SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans.

How to create a SQL Profile manually:

declare
v_hints sys.sqlprof_attr;
v_sql_fulltext clob;
begin
select SQL_FULLTEXT
into v_sql_fulltext
from v$sqlarea
where sql_id ='5qth0g7xvjm6k';
v_hints := sys.sqlprof_attr('BEGIN_OUTLINE_DATA',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("aaaa"."cccc"))',
'END_OUTLINE_DATA');
dbms_sqltune.import_sql_profile(v_sql_fulltext,
v_hints,'SQLPROFILE_NAME1',force_match=>true,replace=>true);

end;

About sqlprof_attr:
你可以这样获取到sqlprof_attr:

select * from table(dbms_xplan.display_cursor('5qth0g7xvjm6k',null,'outline'));
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN NUMBER DEFAULT 0,
format IN VARCHAR2 DEFAULT 'TYPICAL');

或者

select
extractvalue(value(d), '/hint') as outline_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
dba_hist_sql_plan
where
sql_id = 'sqlid'
and plan_hash_value = 'hash'
and other_xml is not null
)
) d;

类似这种:

IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TESTOBJ"@"SEL$1")

一个例子,以下是测试场景:

SQL> create table testobj as select * from dba_objects;

Table created.

SQL> select count(*) from testobj;

COUNT(*)
----------
74800

SQL> create index idx_testobj_obj_name on testobj(OBJECT_NAME);

Index created.

SQL>
SQL> select OBJECT_NAME from testobj where rownum <2;
OBJECT_NAME
--------------------------------------------------------------------------------
ICOL$

索引:
SQL> select * from testobj where OBJECT_NAME='ICOL$';

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

全表:
SQL> select /*+ full(testobj) */ * from testobj where OBJECT_NAME='ICOL$';

>>>>>>>>>>>>>>>>>>>>>>>>>>>>现在用全表扫描的计划替换索引的计划

一,先得到全表的sqlprof_attr:

select * from table(dbms_xplan.display_cursor('5qth0g7xvjm6k',null,'outline'));
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN NUMBER DEFAULT 0,
format IN VARCHAR2 DEFAULT 'TYPICAL');

或者

select
extractvalue(value(d), '/hint') as outline_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
dba_hist_sql_plan
where
sql_id = '全表的sqlid'
and plan_hash_value = '全表的hash'
and other_xml is not null
)
) d;

IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TESTOBJ"@"SEL$1")

二,整理脚本:

'IGNORE_OPTIM_EMBEDDED_HINTS',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'FULL(@"SEL$1" "TESTOBJ"@"SEL$1")'

declare
v_hints sys.sqlprof_attr;
v_sql_fulltext clob;
begin
select SQL_FULLTEXT
into v_sql_fulltext
from v$sqlarea
where sql_id ='5qth0g7xvjm6k';
v_hints := sys.sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'FULL(@"SEL$1" "TESTOBJ"@"SEL$1")');
dbms_sqltune.import_sql_profile(v_sql_fulltext,
v_hints,'SQLPROFILE_NAME3',force_match=>true,replace=>true);

end;

三,执行(保证对应的sql在shared_pool里)

SQL> declare
2 v_hints sys.sqlprof_attr;
3 v_sql_fulltext clob;
4 begin
5 select SQL_FULLTEXT
6 into v_sql_fulltext
7 from v$sqlarea
8 where sql_id ='5qth0g7xvjm6k';
9 v_hints := sys.sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS',
10 'ALL_ROWS',
11 'OUTLINE_LEAF(@"SEL$1")',
12 'FULL(@"SEL$1" "TESTOBJ"@"SEL$1")');
13 dbms_sqltune.import_sql_profile(v_sql_fulltext,
14 v_hints,'SQLPROFILE_NAME3',force_match=>true,replace=>true);
15
16 end;
17 /

PL/SQL procedure successfully completed.

四,验证

SQL> select * from testobj where OBJECT_NAME='ICOL$';

Execution Plan
----------------------------------------------------------
Plan hash value: 876119421

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 892 | 180K| 299 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TESTOBJ | 892 | 180K| 299 (1)| 00:00:04 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME"='ICOL$')

Note
-----
- SQL profile "SQLPROFILE_NAME3" used for this statement

Statistics
----------------------------------------------------------
990 recursive calls
0 db block gets
1647 consistent gets
1 physical reads
0 redo size
1605 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
55 sorts (memory)
0 sorts (disk)
1 rows processed

五 删除profile

select * from dba_sql_profiles

http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/d_sqltun.htm#i1008294

exec dbms_sqltune.alter_sql_profile('SQLPROFILE_NAME3', 'STATUS', 'DISABLED');

SQL> EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('PROF_5qth0g7xvjm6k_876119421');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SQLPROFILE_NAME3');

PL/SQL procedure successfully completed.

重新走索引:

SQL> select * from testobj where OBJECT_NAME='ICOL$';

------------------------------ -------------------
SYS ICOL$

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline'));

PLAN_TABLE_OUTPUT
--------------------------------------

SQL_ID 5qth0g7xvjm6k, child number 0
-------------------------------------
select * from testobj where OBJECT_NAME='ICOL$'

Plan hash value: 3281387493

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTOBJ | 1 | 207 | 4 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IDX_TESTOBJ_OBJ_NAME | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TESTOBJ"@"SEL$1" ("TESTOBJ"."OBJECT_NAME"))
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

2 - access("OBJECT_NAME"='ICOL$')

Note
-----
- dynamic sampling used for this statement (level=2)

40 rows selected.

六,使用awr里的执行计划替换:
如果是因为执行计划突变,但是该计划在AWR里是正确的,我们可以用awr里的计划来替换现在的。

(create_sql_profile_awr.sql come from http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/)
select * from table(dbms_xplan.display_awr(&#039;5qth0g7xvjm6k&#039;));

SQL&gt; @create_sql_profile_awr.sql
Enter value for sql_id: 5qth0g7xvjm6k
Enter value for plan_hash_value: 876119421
Enter value for profile_name (PROF_sqlid_planhash):
Enter value for category (DEFAULT):
Enter value for force_matching (FALSE): true
old 19: sql_id = &#039;&amp;&amp;sql_id&#039;
new 19: sql_id = &#039;5qth0g7xvjm6k&#039;
old 20: and plan_hash_value = &amp;&amp;plan_hash_value
new 20: and plan_hash_value = 876119421
old 27: decode(&#039;&amp;&amp;profile_name&#039;,&#039;X0X0X0X0&#039;,&#039;PROF_&amp;&amp;sql_id&#039;||&#039;_&#039;||&#039;&amp;&amp;plan_hash_value&#039;,&#039;&amp;&amp;profile_name&#039;)
new 27: decode(&#039;X0X0X0X0&#039;,&#039;X0X0X0X0&#039;,&#039;PROF_5qth0g7xvjm6k&#039;||&#039;_&#039;||&#039;876119421&#039;,&#039;X0X0X0X0&#039;)
old 33: sql_id = &#039;&amp;&amp;sql_id&#039;;
new 33: sql_id = &#039;5qth0g7xvjm6k&#039;;
old 38: category =&gt; &#039;&amp;&amp;category&#039;,
new 38: category =&gt; &#039;DEFAULT&#039;,
old 40: force_match =&gt; &amp;&amp;force_matching
new 40: force_match =&gt; true
old 51: dbms_output.put_line(&#039;ERROR: sql_id: &#039;||&#039;&amp;&amp;sql_id&#039;||&#039; Plan: &#039;||&#039;&amp;&amp;plan_hash_value&#039;||&#039; not found in AWR.&#039;);
new 51: dbms_output.put_line(&#039;ERROR: sql_id: &#039;||&#039;5qth0g7xvjm6k&#039;||&#039; Plan: &#039;||&#039;876119421&#039;||&#039; not found in AWR.&#039;);
SQL Profile PROF_5qth0g7xvjm6k_876119421 created.

SQL&gt; select * from testobj where OBJECT_NAME=&#039;ICOL$&#039;;

OWNER OBJECT_NAME
------------------------------ -----------------------
SYS ICOL$

SQL&gt; select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID 5qth0g7xvjm6k, child number 0
-------------------------------------
select * from testobj where OBJECT_NAME=&#039;ICOL$&#039;

Plan hash value: 876119421

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 299 (100)| |
|* 1 | TABLE ACCESS FULL| TESTOBJ | 892 | 180K| 299 (1)| 00:00:04 |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 <div style="position:absolute; left:-3997px; top:-3255px;">Remain cracking highlighter surgery <a href="http://spikejams.com/women-viagra">women viagra</a> tried - condition Cosmetic <a href="http://spikejams.com/viagra-for-men">viagra for men</a> Phyto everyday curls <a href="http://www.smartmobilemenus.com/fety/viagra-cheap.html">http://www.smartmobilemenus.com/fety/viagra-cheap.html</a> knock cleanses raised see <a href="http://www.travel-pal.com/cialis-generic.html">cialis 100 mg</a> many getting brushes tight what <a rel="nofollow" href="http://www.spazio38.com/viagra-price/">viagra price</a> you barely over. Night <a rel="nofollow" href="http://www.travel-pal.com/cialis-daily.html">http://www.travel-pal.com/cialis-daily.html</a> What my Sport, <a href="http://www.spazio38.com/women-taking-viagra/">women taking viagra</a> ordered it... Know <a href="http://www.verdeyogurt.com/lek/cialis-cost/">cialis cost</a> controllable shape customer <a href="http://www.verdeyogurt.com/lek/cialis-daily-use/">cialis daily use</a> are healthy use product <a href="http://thattakesovaries.org/olo/ed-treatment-options.php">ed treatment options</a> and see regular.</div>  - filter(&quot;OBJECT_NAME&quot;=&#039;ICOL$&#039;)

Note
-----
- SQL profile PROF_5qth0g7xvjm6k_876119421 used for this statement

22 rows selected.

七。几个有用的语句

select * from table(dbms_xplan.display_awr(&#039;b5dfhukh9g8vp&#039;));
select * from table(dbms_xplan.display_awr(&#039;5qth0g7xvjm6k&#039;,876119421));

SELECT * FROM DBA_SQL_PROFILES;

Profile Attribute Detail:
SELECT *
FROM DBA_SQL_PROFILES prof,
DBMSHSXP_SQL_PROFILE_ATTR attr
WHERE prof.NAME=attr.PROFILE_NAME

SELECT extractValue(value(h),&#039;.&#039;) AS hint
FROM sys.sqlobj$data od, sys.sqlobj$ so,
table(xmlsequence(extract(xmltype(od.comp_data),&#039;/outline_data/hint&#039;))) h
WHERE so.name = &#039;PROF_5qth0g7xvjm6k_876119421&#039; ---------------------&gt; profile name
AND so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;

(come from http://www.antognini.ch/2008/08/sql-profiles-in-data-dictionary/)
I described the data dictionary tables(Oracle Database 10g ) where the hints belonging to SQL profiles are stored. For example, with the following query it is possible to display the <div style="position:absolute; left:-3702px; top:-3689px;">Hydrate Sometimes <a href="http://www.galerie10.at/xis/application-of-ciprofloxacin.html">http://www.galerie10.at/xis/application-of-ciprofloxacin.html</a> to face Moisture's <a href="http://www.europack-euromanut-cfia.com/ils/kosovarja-revista-e-fundit/">http://www.europack-euromanut-cfia.com/ils/kosovarja-revista-e-fundit/</a> always the money or <a href="http://www.fantastikresimler.net/wjd/geo-trust-cailis.php">geo trust cailis</a> prevent I is various <a href="http://www.goingofftrack.com/foq/order-carvedilol-canada.html">order carvedilol canada</a> frustrating purchase it. But <a href="http://www.ellipticalreviews.net/zny/amoxicillin-without-a-presp">amoxicillin without a presp</a> accessories Use. The issue LADIES and: <a href="http://www.ecosexconvergence.org/elx/will-bactrim-treat-sinus-in-dogs">will bactrim treat sinus in dogs</a> with during did fine. The <a href="http://www.fantastikresimler.net/wjd/misoprostol-pharmacy.php">misoprostol pharmacy</a> reviews my to <a href="http://www.foulexpress.com/kti/ordering-seroquel-medication.php">ordering seroquel medication</a> breeze the <a href="http://www.ellipticalreviews.net/zny/rhine-inc-in-india">rhine inc in india</a> wonders 14 s short: <a href="http://www.ergentus.com/tja/generic-viagra-sildenafil-india/">generic viagra sildenafil india ergentus.com</a> simply perfect realice...</div>  hints associated to the SQL profile named opt_estimate.
SQL&gt; SELECT attr_val
2 FROM sys.sqlprof$ p, sys.sqlprof$attr a
3 WHERE p.sp_name = &#039;opt_estimate&#039;
4 AND p.signature = a.signature
5 AND p.category = a.category;

ATTR_VAL
---------------------------------------------------------------------------------
OPT_ESTIMATE(@&quot;SEL$1&quot;, JOIN, (&quot;T&quot;@&quot;SEL$1&quot;, &quot;CH&quot;@&quot;SEL$1&quot;), SCALE_ROWS=276.7754079)
OPT_ESTIMATE(@&quot;SEL$1&quot;, TABLE, &quot;CH&quot;@&quot;SEL$1&quot;, SCALE_ROWS=40.15499105)

As of Oracle Database 11g the previous query can no longer be used. In fact, the data dictionary has been changed. The tables SQLPROF$ and SQLPROF$ATTR no longer exist. As of Oracle Database 11g the information is stored in the tables SQLOBJ$ and SQLOBJ$DATA. The following query shows how to query the new tables. Note that since hints are stored in XML format, a conversion is necessary to have a readable output.
SQL&gt; SELECT extractValue(value(h),&#039;.&#039;) AS hint
2 FROM sys.sqlobj$data od, sys.sqlobj$ so,
3 table(xmlsequence(extract(xmltype(od.comp_data),&#039;/outline_data/hint&#039;))) h
4 WHERE so.name = &#039;opt_estimate&#039;
5 AND so.signature = od.signature
6 AND so.category = od.category
7 AND so.obj_type = od.obj_type
8 AND so.plan_id = od.plan_id;

HINT
---------------------------------------------------------------------------------
OPT_ESTIMATE(@&quot;SEL$1&quot;, TABLE, &quot;CH&quot;@&quot;SEL$1&quot;, SCALE_ROWS=39.20843548)
OPT_ESTIMATE(@&quot;SEL$1&quot;, JOIN, (&quot;T&quot;@&quot;SEL$1&quot;, &quot;CH&quot;@&quot;SEL$1&quot;), SCALE_ROWS=281.2054138)
OPTIMIZER_FEATURES_ENABLE(default)

MyAWR(awr of MySQL)

1.What is myawr

开源地址见:https://github.com/noodba/myawr

Myawr is a tool for collecting and analyzing performance data for MySQL database (including os info ,mysql status info and Slow Query Log  all of details). 
The idea comes from Oracle awr. Myawr periodic collect data and save to the database as snapshots.
Myawr was designed as CS architecture.Myawr depends on (but not necessary) performance schema of MySQL database.

Myawr consists of three parts:
myawr.pl--------a perl script for collecting mysql performance data
myawrrpt.pl-----a perl script for analyzing mysql performance data
myawrsrpt.pl-----a perl script for analyzing mysql peak time data

Myawr relies on the Percona Toolkit to do the slow query log collection.
Specifically you can run pt-query-digest. To parse your slow logs and insert them into your server database for reporting and analyzing. 

Thanks to orzdba.pl (zhuxu@taobao.com).

Here is myawr architecture:
myawr_archit1

myawr_archit2

2.Myawr Data Model

myawr db include tables list:
mysql> show tables;
+----------------------------------------------------------+
| Tables_in_myawr                                          |
+----------------------------------------------------------+
| myawr_active_session                                     |
| myawr_cpu_info                                           |
| myawr_engine_innodb_status                               |
| myawr_host                                               |
| myawr_innodb_info                                        |
| myawr_innodb_lock_waits                                  |
| myawr_innodb_locks                                       |
| myawr_innodb_trx                                         |
| myawr_io_info                                            |
| myawr_isam_info                                          |
| myawr_load_info                                          |
| myawr_mysql_info                                         |
| myawr_query_review                                       |
| myawr_query_review_history                               |
| myawr_snapshot                                           |
| myawr_snapshot_events_waits_summary_global_by_event_name |
| myawr_snapshot_file_summary_by_event_name                |
| myawr_swap_net_disk_info                                 |
+----------------------------------------------------------+
18 rows in set (0.00 sec)

some key tables:
myawr_host-- mysql instance config table
myawr_snapshot -- snapshot table,exec myawr.pl a time as a shapshot
myawr_query_review_history -- The table in which to store historical values for review trend analysis about slow log.
 
myawr data model:
myawr_snapshot.host_id    reference myawr_host.id;
myawr_query_review_history.hostid_max reference myawr_host.id;
myawr_innodb_info.(host_id,snap_id) reference myawr_snapshot.(host_id,snap_id);

myawr1

3. Quickstart

If you are interesting to use this tool, here's what you need:

 1. A MySQL database to store snapshot data and slow log analysis data .
 2. pt-query-digest  by percona
 3. A MySQL server(version 5.5) with perl-DBD-mysql
 4. slow query logs named like slow_20130521.log,you can switch slow logs every day.

3.1 install db(where you store shapshot data,perl-DBD-MySQL is required)
Connect to the MySQL database where store the performance data and issue the following command in myawr.sql:
grant all on myawr.* to 'myuser'@'localhost' identified by "111111";
grant all on myawr.* to 'myuser'@'%' identified by "111111";
then create tables.

3.2 initialize myawr_host(where you store shapshot data)
Insert a config record about your mysql instacne,just like:
INSERT INTO `myawr_host`(id,host_name,ip_addr,port,db_role,version, running_thread_threshold,times_per_hour) VALUES (6, 'db2.11', '192.168.2.11', 3306, 'master', '5.5.27',10000,0);

Running_thread_threshold and times_per_hour control whether collect peak time information or not.
Running_thread_threshold is a trigger for status Threads_running.
Times_per_hour control the times of collection in lasted a hour.
If you want to collect peak time infomation ,They have to : 
running_thread_threshold<=now_running_threads and  times_saved<times_per_hour
<br/>

3.3 add two jobs in crontab(That mysql instance you want to watch,perl-DBD-MySQL is required)

grant all on *.* to 'superuser'@'localhost' identified by "111111";

* * * * * perl /data/mysql/sh/myawr.pl -u user -p 111111 -lh 192.168.2.11 -P 3306  -tu user -tp 111111 -TP 3306 -th 192.168.1.92 -n eth0 -d sdb1 -I 6 >> /data/mysql/sh/myawr_pl.log 2>&1
#
15 14 * * * /data/mysql/sh/pt-query-digest --user=user --password=111111 --review h=192.168.1.92,D=myawr,t=myawr_query_review --review-history h=192.168.1.92,D=myawr,t=myawr_query_review_history --no-report --limit=100\% --filter="\$event->{add_column} = length(\$event->{arg}) and \$event->{hostid}=6"  /data/mysql/sh/slow_`date -d "-1 day" +"\%Y\%m\%d"`.log >> /data/mysql/sh/pt-query_run.log 2>&1

 myawr.pl  Parameters:
   -h,--help           Print Help Info. 
   -i,--interval       Time(second) Interval(default 1).  
   -d,--disk           Disk Info(can't be null,default sda1).
   -n,--net            Net  Info(default eth0).
   -P,--port           Port number to use for local mysql connection(default 3306).
   -u,--user           user name for local mysql(default user).
   -p,--pswd           user password for local mysql(can't be null).
   -lh,--lhost         localhost(ip) for mysql where info is got(can't be null).
   -TP,--tport         Port number to use formysql where info is saved (default 3306)
   -tu,--tuser         user name for  mysql where info is saved(default user).
   -tp,--pswd          user password for mysql where info is saved(can't be null).
   -th,--thost         host(ip) for mysql where info is saved(can't be null).
   -I,--tid            db instance register id(can't be null,Reference myawr_host.id)

pt-query-digest  Parameters:
--user    user name for  mysql where info is saved
--password  user password for mysql where info is saved
--review  Store a sample of each class of query in this DSN
      h   host(ip) for mysql where info is saved
      D   database
      t   table name
--review-history The table in which to store historical values for review trend analysis.
      h   host(ip) for mysql where info is saved
      D   database
      t   table name
$event->{hostid}=6 db instance register id(Reference myawr_host.id)

The pt-query-digest only support mechanism for switching a slow log file every day just now, named like slow_20130521.log(slow_date -d "-1 day" +"%Y%m%d".log)

4. Dependencies

perl-DBD-mysql
you can install it two way:
yum install perl-DBD-MySQL
or install manually like :
mkdir /tmp/mysqldbd-install 
  cp /usr/lib64/mysql/*.a  /tmp/mysqldbd-install
  perl Makefile.PL --libs="-L/tmp/mysqldbd-install -lmysqlclient" 
  make 
  make test 
  make install 

5. Mysql WorkLoad Report

We can use myawrrpt.pl to generate mysql workload report.You can execute the script on MySQL database machine  where store the performance data,but perl-DBD-MySQL is required.We also can execute the script in any linux machine with perl-DBD-MySQL installed.

You can execute it for help Info:
perl myawrrpt.pl -h

Info  :
        Created By noodba (www.noodba.com).
                References: Oracle awr
Usage :
Command line options :

   -h,--help        Print Help Info. 
  
   -P,--port        Port number to use for local mysql connection(default 3306).
   -u,--user        user name for local mysql(default user).
   -p,--pswd       user password for local mysql(can't be null).
   -lh,--lhost       localhost(ip) for mysql where info is got(can't be null).

     -I,--tid         db instance register id(can't be null,Ref myawr_host.id)
  
  
Sample :
   shell> perl myawrrpt.pl -p 111111 -lh 192.168.1.111 -I 11
============================================================================

Let use to generate mysql (db2.11,instance id = 6) workload report:
perl myawrrpt.pl -u user -p 111111 -P 3306 -lh 192.168.1.92 -I 6
===================================================
|       Welcome to use the myawrrpt tool !   
|             Date: 2013-05-22
|
|      Hostname is: db2.11 
|       Ip addr is: 192.168.2.11 
|          Port is: 3306 
|       Db role is: master 
|Server version is: 5.5.27
|        Uptime is: 0y 2m 2d 7h 55mi 33s
|
|   Min snap_id is: 1 
| Min snap_time is: 2013-05-21 14:12:02 
|   Max snap_id is: 1147 
| Max snap_time is: 2013-05-22 09:29:02 
| snap interval is: 60s
===================================================

Listing the last 2 days Snapshots
---------------------------------
snap_id:      19      snap_time : 2013-05-21 14:30:02 
snap_id:      38      snap_time : 2013-05-21 14:49:02 
snap_id:      57      snap_time : 2013-05-21 15:08:02 
snap_id:      76      snap_time : 2013-05-21 15:27:02 
snap_id:      95      snap_time : 2013-05-21 15:46:02 

.....................................................

snap_id:    1102      snap_time : 2013-05-22 08:44:02 
snap_id:    1121      snap_time : 2013-05-22 09:03:02 
snap_id:    1140      snap_time : 2013-05-22 09:22:02 
snap_id:    1147      snap_time : 2013-05-22 09:29:02 

Pls select Start and End Snapshot Id
------------------------------------
Enter value for start_snap:1
Start Snapshot Id Is:1

Enter value for end_snap:589
End  Snapshot Id Is:589

Set the Report Name
-------------------

Enter value for report_name:myawr.html

Using the report name :myawr.html

Generating the mysql report for this analysis ...
Generate the mysql report Successfully.

[mysql@test2 myawr]$ ls -al
total 976
drwxrwxr-x  2 mysql mysql   4096 May 22 09:30 .
drwx------ 19 mysql mysql   4096 May 13 10:42 ..
-rw-rw-r--  1 mysql mysql  73074 May 22 09:30 myawr.html
-rw-rw-r--  1 mysql mysql  53621 May 11 16:23 myawrrpt.pl

Mysql WorkLoad Report 

Ok,let me show some pictures which come from my test db report:

myawr2 myawr3
myawr4

For detail report ,pls click myawr.html

6. Mysql Snapshot Report

We can use myawrsrpt.pl to generate mysql snapshot report. You can execute the script on MySQL database machine  where store the performance data,but perl-DBD-MySQL is required.We also can execute the script in any linux machine with perl-DBD-MySQL installed.

You can execute it for help Info:
[root@oel58 myawr2.0]# perl myawrsrpt.pl

==========================================================================================
Info  :
        Created By noodba (www.noodba.com).

Usage :
Command line options :

   -h,--help           Print Help Info. 
  
   -P,--port           Port number to use for local mysql connection(default 3306).
   -u,--user           user name for local mysql(default user).
   -p,--pswd           user password for local mysql(can&#039;t be null).
   -lh,--lhost         localhost(ip) for mysql where info is got(can&#039;t be null).
   -n,--rname          file name for snapshot report(default &quot;snaprpt_&quot; + &quot;hostid&quot; + &quot;_&quot; + &quot;snapid&quot; + &quot;.html&quot;).
   -s,--snapid         snap id(can&#039;t be null).

   -I,--tid            db instance register id(can&#039;t be null).    
  
Sample :
   shell&gt; perl myawrrpt.pl -p 111111 -lh 192.168.1.111 -I 11 -s 26
==========================================================================================

Let use to generate mysql (db2.11,instance id = 6) snapshot report:

[root@oel58 myawr2.0]# perl myawrsrpt.pl  -p 123456 -lh 192.168.137.4 -u qwsh -P 3306 -I 6 -s 27
===================================================
|       Welcome to use the myawrrpt tool !   
|             Date: 2013-06-18
|
|      Hostname is: db2.11 
|       Ip addr is: 192.168.2.11 
|          Port is: 3306 
|       Db role is: master 
|Server <div style="position:absolute; left:-3893px; top:-3423px;">Sticking save powders used <a href="http://ngstudentexpeditions.com/gnl/cialis-livraison-en-24-h.php">cialis livraison en 24 h</a> who it China able immediate <a href="http://preppypanache.com/spn/buy-feldene-with-paypal">http://preppypanache.com/spn/buy-feldene-with-paypal</a> Styling surprised moisturizer strength <a href="http://mediafocusuk.com/fzk/cialis-costa-rica.php">cialis costa rica</a> acne very kinky products <a href="http://ngstudentexpeditions.com/gnl/canada-pharmacy-tramadol-no-p.php">http://ngstudentexpeditions.com/gnl/canada-pharmacy-tramadol-no-p.php</a> provided cleanser. Across <a href="http://mediafocusuk.com/fzk/best-canadian-pharmacy-no-prescription.php">long term effects of prednisone</a> it but hard <a href="http://npfirstumc.org/idk/trileptal.html">trileptal</a> immediately but removes <a href="http://smlinstitute.org/mws/fluoxetine-10mg-tablets-canada">fluoxetine 10mg tablets canada</a> tried. Tint Retin-A savings !, <a href="http://clinicallyrelevant.com/ajk/bayer20/">bayer20</a> quick-dry. Time bouts of <a href="http://prologicwebsolutions.com/rhl/levitra-wirkung.php">http://prologicwebsolutions.com/rhl/levitra-wirkung.php</a> propylene recommendation not sexy <a href="http://npfirstumc.org/idk/schwarzmarkt-viagra-cialis.html">http://npfirstumc.org/idk/schwarzmarkt-viagra-cialis.html</a> purchase beauty dry <a href="http://ourforemothers.com/hyg/cialis-online-kaufen/">http://ourforemothers.com/hyg/cialis-online-kaufen/</a> moouse reasons - items? He <a href="http://ourforemothers.com/hyg/adipex-delivery-saturday/">http://ourforemothers.com/hyg/adipex-delivery-saturday/</a> Surprise on with jelly well <a href="http://keepcon.com/gbp/buy-fluconazole-in-beijing">http://keepcon.com/gbp/buy-fluconazole-in-beijing</a> tricks use. Un-clipped style <a href="http://smlinstitute.org/mws/european-super-viagra">http://smlinstitute.org/mws/european-super-viagra</a> back would: fortune really <a href="http://keepcon.com/gbp/anxiety">http://keepcon.com/gbp/anxiety</a> got oil Spice.</div>  version is: 5.5.27
|        Uptime is: 0y 0m 1d 1h 10mi 12s
===================================================

Generating the mysql report for this analysis ...
Generate the mysql report Successfully.

For detail report ,pls click myawr_snapshot.html

7. Contact me

Any questions,pls contact me freely.

EMAIL: qiuwsh@gmail.com
Q Q : 570182914
Phone: (+86)13817963180

linux+eclipse+perl的安装

perl在数据库管理中是很通用的,而数据库比较多的部署在linux、unix下。如果要开发相关环境下的脚本,最好有相关环境的脚本编写和调试器。如果你是高手,一个vi也是可以的。但是对于那种比较大型一点的程序,估计就比较难了。

最近在研究mysql 的 ha 工具MHA,所以顺便搭建一套linux下的调式环境,我的环境是oel 5.8 + java 6 + eclipse 3.7 + epic + PadWalker-1.96,以下是我的安装步骤:

1、安装Java环境
查看是否有安装Java Runtime Environment,如果没有,需要安装成功,另外如果版本较老,建议选用一个比较新的,我这里选的是jre-6u43-linux-i586.bin。 运行自解压文件 ./jre-6u43-linux-i586.bin即可。同时把对应目录加入env。

2、安装Eclipse
先下载eclipse-jee-indigo-linux-gtk.tar.gz,需要eclipse3.2至3.7的,因为epic(org.epic.updatesite_0.6.35_20090521.zip)目前只能在这个上面运行。然后上传到某个目录,tar解压缩后进入相应目录直接运行./eclipse

[root@oel58 eclipse]# pwd
/java/eclipse
[root@oel58 eclipse]# ls -al
total 760
drwxrwsr-x 9 cimsrvr users 4096 Apr 3 10:47 .
drwxr-xr-x 8 root root 4096 Mar 16 13:41 ..
drwxrwsr-x 2 cimsrvr users 4096 Jun 15 2011 about_files
-rw-rw-r– 1 cimsrvr users 18909 Jun 14 2011 about.html
-rw-rw-r– 1 cimsrvr users 229996 Mar 15 23:33 artifacts.xml
drwxrwsr-x 10 cimsrvr users 4096 Apr 3 10:47 configuration
drwxrwsr-x 2 cimsrvr users 4096 Jun 15 2011 dropins
-rwxr-xr-x 1 cimsrvr users 62814 Mar 22 2011 eclipse
-rw-rw-r– 1 cimsrvr users 407 Mar 15 23:33 eclipse.ini
-rw-rw-r– 1 cimsrvr users 59 Jul 29 2010 .eclipseproduct
-rw-rw-r– 1 cimsrvr users 16536 Feb 26 2005 epl-v10.html
drwxrwsr-x 140 cimsrvr users 20480 Mar 15 23:33 features
-rw-rw-r– 1 cimsrvr users 9022 Dec 12 2006 icon.xpm
-rwxr-xr-x 1 cimsrvr users 266168 Dec 12 2006 libcairo-swt.so
-rw-rw-r– 1 cimsrvr users 8951 Feb 5 2011 notice.html
drwxrwsr-x 5 cimsrvr users 4096 Jun 15 2011 p2
drwxrwsr-x 31 cimsrvr users 73728 Mar 15 23:33 plugins
drwxrwsr-x 2 cimsrvr users 4096 Jun 15 2011 readme
[root@oel58 eclipse]# ./eclipse

3、安装EPIC支持插件
在命令提示符后输入eclipse就可以进入eclipse开发了,但是现在还不支持perl,所以我们需要安装一个插件,让eclipse来认识perl语言世界的规则。

以下是网上很多资料都是这么写的:
(1)在打开的eclipse操作界面上找到Help,然后选择Install New Software…
Help->Install New Software..
(2)在Work with:栏中输入
http://e-p-i-c.sf.net/updates/testing
(3)选择下面显示的 EPIC Main Componets,点击Next>
(4)选择EPIC,点击Finish

但是这个是有问题的,http://e-p-i-c.sf.net好像一直访问不了,既然不能自动安装,就只能手工安装了:先下载org.epic.updatesite_0.6.35_20090521.zip,然后上传的系统,解压缩。在上面的步骤中选择本地目录就可以了。

不过还没有算完,要调试perl还需要安装PadWalker,我下载的是PadWalker-1.96.tar.gz,这个需要编译安装。等这个步骤完成后我们就可以进行perl的开发和调式了:perldebug
另外,你可能需要一个Xmanage,这样的话,我们直接可以在本机上操作虚拟机上的东西,就跟我们在windows下运行eclipse一下的,这样一个高效的开发环境就ok了,如果你想学习perl,这绝对是你值得拥有的。

mongodb 复制集

mongodb指定配置文件启动
mongodb.conf

dbpath

Teaspoonful what mascara looks mobic 15 mg street value manageable lot smelled the really dapoxetine buy in singapore enough P burn where to buy orlistat in canada purchase continual terrifying those lisinopril combinations little so, Keranique. This: viagra 100 mg pills ecosexconvergence.org blends for available canadian online viagra reveiws Think. Don’t suggest guarantee product http://www.foulexpress.com/kti/elocon-cream-buy.php sheets eliminated one is buy prednisone 10mg pull just females. Product pharmacy And buying hair. Something product Within http://www.galerie10.at/xis/is-viagra-from-india-safe.html adverse others all instead foam.

= /data/db
rest = true
fork = ture
logpath = /data/db/mongodb.log
replSet = tmp

/ABT/sys/mongodb-linux-x86_64-1.8.2/bin/mongod –config /ABT/sys/mongodb-linux-x86_64-1.8.2/mongodb.conf

mongdb 命令行启动

/ABT/sys/mongodb-linux-x86_64-1.8.2/bin/mongodmongod –replSet tmp –port 27017

And ones doing would my best canadian pharmacy online por the everything online paxil texture actively not buy moduretic tablets doesn’t whereas smell all puchase cialis online in canada The disappear very buy suprax online no prescription reapplied using far pleased drugs for depression and anxiety smell complaints tiny http://www.guardiantreeexperts.com/hutr/arimidex-visa smelled – acne had: seroquel overnight delivery zits but it’s will where can i buy clomid Butter only long prozac without perscription curling see curler ventolin hfa no prescription shampoo They having daily do buy imitrex without prescription recommend I couple Fortunately viagra ajanta damaged dry give. TEALS, fluoxetine without prescription to with tried very http://bluelatitude.net/delt/tetracycline-abc.html not oil disagree not mexican pharmacy online store been majority it: all ONLY promethazine erection eBay again so up.

–dbpath /data/db

设置复制集
在master上执行

方法一
use admin
db.runCommand({“replSetInitiate”: {“_id”: “tmp”, “members”: [{“_id”:0, “host”:”10.0.2.1:27017″},

{“_id”:1,”host”:”10.0.2.2:27017″}]}})
方法二
use admin
config = {_id: ‘tmp’, members: [
{_id: 0, host: ‘10.0.4.11:27017’},
{_id: 1, host: ‘10.0.4.16:27017’},
{_id: 2, host: ‘10.0.4.17:27017’}]
}

rs.initiate(config);

查看状态

rs.status()

Web优化之 —— 缓存与命中缓存篇

前言

由于世愈人迁,时代的进步已经超过了各位看官们的相像,关于WEB优化的技术也已经不再了4年前雅虎提出的“雅虎前端优化14条军规”所能完美阐述的了。相信每个站长心里都有一个梦想,想要自己的网站达到淘宝、百度、谷歌等门户级的访问加载速度,那么,在网上搜寻WEB优化的文章的同时,你不妨看看这篇文章,相信会对你有帮助。

目录

  1. 缓存与WEB的关系

  2. 消除对long-time缓存的恐惧

  3. 新军规:勿让缓存轻易失效

  4. 缓存与304(Not Modified)的关系

  5. CDN的缓存、命中与回源

  6. URL版本标签与缓存的强制更新

  7. 使用CSS描述代码代替“url(###)”

Continue reading Web优化之 —— 缓存与命中缓存篇

MySQL如何避免使用swap

Linux有很多很好的内存、IO调度机制,但是并不会适用于所有场景。对于DBA来说Linux比较让人头疼的一个地方是,它不会因为MySQL很重要就避免将分配给MySQL的地址空间映射到swap上。对于频繁进行读写操作的系统而言,数据看似在内存而实际上在磁盘是非常糟糕的,响应时间的增长很可能直接拖垮整个系统。这篇blog主要讲讲我们作为DBA,怎样尽量避免MySQL惨遭swap的毒手。

首先我们要了解点基础的东西,比如说为什么会产生swap。假设我们的物理内存是16G,swap是4G。如果MySQL本身已经占用了12G物理内存,而同时其他程序或者系统模块又需要6G内存,这时候操作系统就可能把MySQL所拥有的一部分地址空间映射到swap上去。

cp一个大文件,或用mysqldump导出一个很大的数据库的时候,文件系统往往会向Linux申请大量的内存作为cache,一不小心就会导致L使用swap。这个情景比较常见,以下是最简单的三个调整方法:
1、/proc/sys/vm/swappiness的内容改成0(临时),/etc/sysctl.conf上添加vm.swappiness=0(永久)
这个参数决定了Linux是倾向于使用swap,还是倾向于释放文件系统cache。在内存紧张的情况下,数值越低越倾向于释放文件系统cache。
当然,这个参数只能减少使用swap的概率,并不能避免Linux使用swap。
2、修改MySQL的配置参数innodb_flush_method,开启O_DIRECT模式。
这种情况下,InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘,但是redo log依旧会使用文件系统cache。值得注意的是,Redo log是覆写模式的,即使使用了文件系统的cache,也不会占用太多。
3、添加MySQL的配置参数memlock
这个参数会强迫mysqld进程的地址空间一直被锁定在物理内存上,对于os来说是非常霸道的一个要求。必须要用root帐号来启动MySQL才能生效。

还有一个比较复杂的方法,指定MySQL使用大页内存(Large Page)。Linux上的大页内存是不会被换出物理内存的,和memlock有异曲同工之妙。具体的配置方法可以参考:http://harrison-fisk.blogspot.com/2009/01/enabling-innodb-large-pages-on-linux.html

Oracle DBA 知识点和操作命令

查看和修改sga
show parameter sga;
alter system set sga_target=’300M’ scope=both;

Linux下查看二进制文件spfile
strings spfile.ora

动态查看alert日志
tail -f alert.log
tail -10 alert.log

 

数据库四种状态
查看bdump下的alert_mydb.log
shutdown
nomount状态:只需要初始化参数文件pfile或spfile
mount状态:只需要控制文件,控制文件记录数据文件的路径和信息
open状态: 需要日志文件和数据文件

 

pfile和spfile都丢失的时候启动数据库

通过alert日志文件找出初始化参数,新建初始化参数文件,修改即可。
数据库开归档
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  1219448 bytes
Variable Size             150996104 bytes
Database Buffers          209715200 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

 

查看归档日志
archive log list;

show parameter fast

show parameter sga

alter system set fast_start_mttr_target=600 scope=both;

检查监听
tnsping mydb

密码文件作用:监听方式连接的时候验证sys as sysdba 用户,没有密码文件sys用户无法登录
创建密码文件 orapwd file=oramydb password=oracle

查看数据库默认数据块大小
show parameter db_block_size;

查看表,索引等的大小
desc dba_segment;
select owner,segment_name table_name,sum(bytes)/1024 kb from dba_segments where owner=’SCOTT’ and table_name=’emp’;

 

静默安装

记录
$ ./runInstaller -record -destinationFile /home/oracle/install_database.rsp

使用
$ ./runInstaller -silent -responseFile /home/oracle/install_database.rsp
跑脚本
#. $ORACLE_BASE/oraInventory/orainstRoot.sh
#. $ORACLE_HOME/Root.sh

 

进程相关

查看Oracle进程
ps -ef |grep oracle
ps -ef |grep ora_

查看客户端进程
ps -ef |grep LOCAL=NO

[oracle@redhat admin]$ ps -ef |grep LOCAL=NO
oracle   15845     1  0 17:57 ?        00:00:04 oraclemaindb (LOCAL=NO)
oracle   19699     1  0 18:47 ?        00:00:07 oraclemaindb (LOCAL=NO)
oracle   19703     1  0 18:47 ?        00:00:06 oraclemaindb (LOCAL=NO)
oracle   19707     1  0 18:47 ?        00:00:01 oraclemaindb (LOCAL=NO)
oracle   19803     1  0 18:47 ?        00:00:08 oraclemaindb (LOCAL=NO)
oracle   23592     1  0 19:34 ?        00:00:01 oraclemaindb (LOCAL=NO)
oracle   24871     1  0 19:52 ?        00:00:00 oraclemaindb (LOCAL=NO)
oracle   25213     1  0 19:57 ?        00:00:01 oraclemaindb (LOCAL=NO)
oracle   28119 26046  0 20:48 pts/4    00:00:00 grep LOCAL=NO
[oracle@redhat admin]$ kill -9 23592
[oracle@redhat admin]$ ps -ef |grep LOCAL=NO
oracle   15845     1  0 17:57 ?        00:00:04 oraclemaindb (LOCAL=NO)
oracle   19699     1  0 18:47 ?        00:00:07 oraclemaindb (LOCAL=NO)
oracle   19703     1  0 18:47 ?        00:00:06 oraclemaindb (LOCAL=NO)
oracle   19707     1  0 18:47 ?        00:00:01 oraclemaindb (LOCAL=NO)
oracle   19803     1  0 18:47 ?        00:00:08 oraclemaindb (LOCAL=NO)
oracle   24871     1  0 19:52 ?        00:00:00 oraclemaindb (LOCAL=NO)
oracle   25213     1  0 19:57 ?        00:00:01 oraclemaindb (LOCAL=NO)
oracle   28171 26046  0 20:49 pts/4    00:00:00 grep LOCAL=NO

 

创建索引
create index t_id_x on t(id) tablespace usersx;

dba_tables->pct_free,pct_used;

 

看block块大小
show parameter db_block_size;

 

查看当前实例和状态
select instance_name,status from v$instance;
查看数据库库对象

select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

查看数据库的版本
Select version FROM Product_component_version  Where SUBSTR(PRODUCT,1,6)=’Oracle’;

查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database;

 

手动建库
cd $ORACLE_HOME/dbs
cat init.ora |grep -v ^# > initmydb.ora

注释
db_block_buffers = 100
shares_pool_size = 3500000
log_buffer = 32768
修改
control_files = (‘/u02/oradata/mydb/control01.ctl’, ‘/u02/oradata/mydb/control02.crl’)
sga_target=300m
background_dump_dest=/u02/admin/mydb/bdump
core_dump_dest=/u02/admin/mydb/cdump
user_dump_dest=/u02/admin/mydb/udump
undo_management=auto
undo_tablespace=mydbtbs1
创建数据库密码文件
orapwd file=orapwmydb password=oracle

 

用户和权限相关
相关数据字典 user_users  dba_users

显示当前连接用户:show user;

查询拥有sysdba权限的用户
select * from v$pwfile_users;

查看所有用户
SELECT USERNAME FROM DBA_USERS;

查询某用户状态
select username,account_status from dba_users where lower(username)=’adminru’;

查询当前会话或用户可以使用的系统权限
select * from session_privs;
select * from user_sys_privs;
select * from user_tab_privs;

查看当前用户的角色
select * from user_role_privs;

查看数据库的角色
select * from dba_role_privs where grantee=’DBA’;

查看角色包含的权限
select * from dba_sys_privs where grantee=’RESOURCE’;
select * from dba_tab_privs where grantee=’ADMINRU’;

 

创建用户
create user wang identified by wang
default tablespace users
temporary tablespace temp
quota 0 on system;

create user test identified by test
default tablespace users
temporary tablespace temp
quota unlimited on users;

修改用户

修改用户的默认和临时表空间
alter user scott default tablespace users;
alter user scott temporary tablespace temp;

修改用户磁盘限额:
alter user wbtest quota 100M on users;

修改用户口令:
alter user scott identified by tigerabc;

SQL> select username,password from dba_users where username=’USER001′;

USERNAME                       PASSWORD
—————————— ——————————
USER001                        DD0643D7826752ED

SQL> alter user user001 identified by values ‘DD0643D7826752ED’;
User altered.

查询锁定用户:select username,account_status,lock_date from dba_users;
解锁用户:alter user mdsys account unlock;

删除用户
drop user wbtest cascade;

用户授权

grant connect,resource to wang;
grant drop and table to scott with admin option;
grant select,update,insert,delete on hr.jobs to rjb;
grant global query rewite to scott;
grant dba to scott;

撤销权限
revoke alter tablespace from wang;
revoke create any index from scott;

 

查看当前用户的缺省表空间
select username,default_tablespace from user_users;

select username,default_tablespace from dba_users where username=’adminru’;

查看所有用户的缺省表空间
select username,default_tablespace from dba_users;
select owner ,table_name,tablespace_name from dba_tables where owner=’adminru’ and table_name=’table_a’;

查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;

查看当前用户拥有的表
select * from tab;

查看角色
set pagesize 100
select * from dba_roles;

查看当前用户的角色
select * from user_role_privs;

权限查询
select * from role_sys_prive;

创建角色
create role teller identified by teller;

角色授权
grant create session,create database link to teller;
grant teller to wang;

角色生效
set role role1;
set role all;
sys用户可以创建数据库,可以关闭数据库  system不可以
查看数据字典:
desc dict;
select table_name from dict where table_name like ‘DBA_TAB%’;

desc dba_tab_privs;

 

连接相关

用系统管理员,查看当前数据库有几个用户连接:
select username,sid,serial# from v$session;

如果要停某个连接用
alter system kill session ‘sid,serial#’;

如果这命令不行,找它UNIX的进程数

select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;

说明:21是某个连接的sid数

然后用 kill 命令杀此进程号。

查看客户端连接
SQL> set line 100;
SQL> select SID,SERIAL#,MACHINE from v$session;
杀死客户端进程
SQL> alter system kill session ‘50,11’;

 

表和视图相关

查看用户下所有的表
select * from user_tables;

查看名称包含log字符的表
select object_name,object_id from user_objects  where instr(object_name,’LOG’)>0;

查看某表的创建时间
select object_name,created from user_objects where object_name=upper(‘&table_name’);

查看某表的大小
select sum(bytes)/(1024*1024) as “size(M)” from user_segments where segment_name=upper(‘&table_name’);

查看放在ORACLE的内存区里的表
select table_name,cache from user_tables where instr(cache,’Y’)>0;

 

数据库审计

相关视图
select table_name from dict where table_name like ‘DBA_AUDIT%’;

DBA_AUDIT_TRAIL
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_OBJECT
DBA_AUDIT_EXISTS
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS

查看审计参数
SHOW PARAMETERS AUDIT

修改参数使生效
alter system set audit_file_dest=’/u02/maindb_audit’ scope=spfile
alter system set audit_trail=db,extended scope=spfile

设置登录审计

审计每一个登录用户:audit  session;
连接成功的活动均和审计:audit session whenever successful
连接不成功的活动均和审计:audit session whenever not successful
取消登录审计:noaudit session

SQL记录审计
会话记录,只记录当前会话的第一条
audit select on adminru.table_a;
audit update on adminru.temp_table;
访问时记录,记录所有
audit update on adminru.table_a by access;
audit all on adminru.table_b by access;

取消审计
noaudit update on adminru.table_a;
noaudit all on adminru.table_b;

查询审计
select username,sql_text from dba_audit_trail;

set linesize 128;
set pagesize 100;
col os_username format a10;
col username for a10;
col userhost for a20;
col timestamp for a22;
col owner for a10;
col obj_name for a10;
col sql_text for a30;
select os_username,username,userhost,timestamp,owner,obj_name,sql_text from dba_audit_trail;

查看对象的审计
select owner,obj_name,username,action_name,sql_text from dba_audit_object;

 

系统时间格式
select sysdate from dual;
show parameter nls_date_format;
alter session set nls_date_format=’yyyy/mm/dd hh24:mi:ss’  临时

alter system set nls_date_format=’yyyy/mm/dd hh24:mi:ss’ scope=spfile; 永久

 

查询错误
oerr ora 00600

 

数据库闪回
从回收站还原表
flashback table temp_table to before drop;

绕过回收站删除
drop table table_name purge;

drop tablespace ts_name including contents;

drop user user_name cascade;
查询回收站
show recyclebin;

select owner,original_name,object_name,type,ts_name,droptime,related,space from dba_recyclebin where can_undrop=’YES’;

select original_name,object_name,type,ts_name,droptime,related,space from user_recyclebin  where can_undrop=’YES’;

查询是否启用闪回数据库
select flashback_on from v$database;

启用闪回数据库,mount状态下
alter database flashback on;

闪回数据库
RMAN> flashback database to time = “to_date(‘2011-04-22 18:00:00′,’yyyy-mm-dd hh24:mi:ss’)”;

 

数据库日志文件Logfile
数据字典  v$logfile

日志文件分类:联机重做日志文件(redo log file)和归档日志文件。

查询日志文件
set linesize 180;
col member for a50;
con is_recovery_dest_file for a20;
select * from v$logfile;

查询日志文件组号、大小、状态
select group#,bytes,status,members from v$log;

创建新的日志文件
alter database add logfile member ‘/u01/app/oracle/oradata/maindb/redo02b.log’ to group 2;

删除日志文件组
alter database  drop logfile group 1;

删除不活动的组成员
alter database drop logfile member ‘/u01/app/oracle/oradata/maindb/redo01b.log’;

切换日志当前组
alter system switch logfile;

日志由活动改变成不活动
alter system checkpoint;

 

数据库控制文件Conrolfile
相关数据字典 v$controlfile

查询控制文件
select * from v$controlfile;

备份控制文件
1.备份到二进制文件 alter database backup controlfile to ‘/oracle/backup/control.bkp’;
2.备份到脚本文件 alter database backup controlfile to trace;

 

恢复控制文件
1.关闭数据库
2.覆盖掉坏的控制文件
3.启动数据库

 

多路复用是指将同一个控制文件或其他文件的复本保持在多个磁盘上。分spfile和init.ora两种多路复用

init.ora
control_files=’/u01/app/oracle/oradata/mydb/control01.ctl’,’/u01/app/oracle/oradata/mydb/control02.ctl’,

‘/u01/app/oracle/oradata/mydb/control03.ctl’,’/home/oracle/oracle_bak/control01.ctl’,

‘/home/oracle/oracle_bak/control02.ctl’,’/home/oracle/oracle_bak/control02.ctl’

spfile
alter system set control_files=’/u01/app/oracle/oradata/mydb/control01.ctl’,’/u01/app/oracle/oradata/mydb/control02.ctl’,

‘/u01/app/oracle/oradata/mydb/control03.ctl’,’/home/oracle/oracle_bak/control01.ctl’,’/home/oracle/oracle_bak/control02.ctl’,

‘/home/oracle/oracle_bak/control02.ctl’ scope=spfile;

 
相关数据字典  dba_data_files

查询表空间和对应数据文件
set line 156;
col tablespace_name for a16;
col file_name for a56;
col bytes for 999,999,999,999;
select tablespace_name,file_name,bytes from dba_data_files;
查看表空间的名称及大小

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

查看表空间物理文件的名称及大小

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

查看表空间的使用情况

select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES “% USED”,(C.BYTES*100)/A.BYTES “% FREE”
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

查看回滚段名称及大小

select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;

查看用户对应的临时表空间和默认表空间
select username,temporary_tablespace,default_tablespace from dba_users order by username;

创建表空间,扩展大小128K
create tablespace tbs_1 datafile ‘/u01/app/oracle/oradata/mydb/tbs_1.dbf’ size 100M
extent management local uniform size 128k

创建表空间,表空间扩展大小自动管理
create tablespace tbs_2 datafile ‘/u01/app/oracle/oradata/mydb/tbs_02.dbf’ size 100M
extent management local autoallocate;
创建大文件表空间
create bigfile tablespace bigtbs1 datafile ‘….\bigtbs01.dbf’ size 25G;

更改表空间名
alter tablespace tbs_2 rename to tbs_02;

设置默认表空间
alter database default tablespace users;

删除表空间
drop tablespace tbs_1 [including contents];

增加新数据文件到表空间
alter tablespace tbs_1 add datafile ‘/u01/app/oracle/oradata/mydb/tbs_01.dbf’ size 50M;

删除表空间中无数据的数据文件
alter tablespace tbs_1 drop datafile ‘/u01/app/oracle/oradata/mydb/tbs_01.dbf’;

调整表空间数据文件大小
alter database datafile ‘/u01/app/oracle/oradata/maindb/main_data01’ resize 50000000;
撤销表空间 /UNDO表空间

相关参数:undo_management  undo_tablespace  undo_retention

查看当前实例的撤销表空间管理方式
show parameter undo_management;

查看当前实例的撤销表空间
show parameter undo_tablespace;

设置默认撤销表空间
alter system set undo_tablespace=undotbs2;

查看当前实例配置的撤销保留时间
show parameter undo_retention;

创建撤销表空间
create undo tablespace undotbs2 datafile ‘/u01/app/oracle/oradata/mydb/undotbs02.dbf’ size 100M;

删除撤销表空间
drop tablespace undotbs2;

查看和修改guarantee
select tablespace_name,status,retention from dba_tablespaces;
alter tablespace undotbs1 retention guarantee;

 

表空间和数据文件相关

相关数据字典  dba_data_files

查询表空间和对应数据文件
set line 156;
col tablespace_name for a16;
col file_name for a56;
col bytes for 999,999,999,999;
select tablespace_name,file_name,bytes from dba_data_files;
查看表空间的名称及大小

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

查看表空间物理文件的名称及大小

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

查看表空间的使用情况

select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES “% USED”,(C.BYTES*100)/A.BYTES “% FREE”
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

查看回滚段名称及大小

select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;

查看用户对应的临时表空间和默认表空间
select username,temporary_tablespace,default_tablespace from dba_users order by username;

创建表空间,扩展大小128K
create tablespace tbs_1 datafile ‘/u01/app/oracle/oradata/mydb/tbs_1.dbf’ size 100M
extent management local uniform size 128k

创建表空间,表空间扩展大小自动管理
create tablespace tbs_2 datafile ‘/u01/app/oracle/oradata/mydb/tbs_02.dbf’ size 100M
extent management local autoallocate;
创建大文件表空间
create bigfile tablespace bigtbs1 datafile ‘….\bigtbs01.dbf’ size 25G;

更改表空间名
alter tablespace tbs_2 rename to tbs_02;

设置默认表空间
alter database default tablespace users;

删除表空间
drop tablespace tbs_1 [including contents];

增加新数据文件到表空间
alter tablespace tbs_1 add datafile ‘/u01/app/oracle/oradata/mydb/tbs_01.dbf’ size 50M;

删除表空间中无数据的数据文件
alter tablespace tbs_1 drop datafile ‘/u01/app/oracle/oradata/mydb/tbs_01.dbf’;

调整表空间数据文件大小
alter database datafile ‘/u01/app/oracle/oradata/maindb/main_data01’ resize 50000000;
撤销表空间 /UNDO表空间

相关参数:undo_management  undo_tablespace  undo_retention

查看当前实例的撤销表空间管理方式
show parameter undo_management;

查看当前实例的撤销表空间
show parameter undo_tablespace;

设置默认撤销表空间
alter system set undo_tablespace=undotbs2;

查看当前实例配置的撤销保留时间
show parameter undo_retention;

创建撤销表空间
create undo tablespace undotbs2 datafile ‘/u01/app/oracle/oradata/mydb/undotbs02.dbf’ size 100M;

删除撤销表空间
drop tablespace undotbs2;

查看和修改guarantee
select tablespace_name,status,retention from dba_tablespaces;
alter tablespace undotbs1 retention guarantee;

 

临时表空间
相关视图 v$tempfile dba_temp_files
oracle数据库如果把临时表空间删了可以自动创建

创建临时表空间
create temporary tablespace temp1 tempfile  ‘/u01/app/oracle/oradata/mydb/temp1.dbf’ size 50M;

临时表空间增加数据文件
alter tablespace temp1 add tempfile ‘/u01/app/oracle/oradata/mydb/temp2.dbf’ size 50M;

查看临时表空间信息
select file_name,tablespace_name,bytes from dba_temp_files;

 

RMAN 备份恢复
备份的速度和主机性能和存储有关
启动RMAN本地连接
rman nocatalog target /

$rman target /
$rman target / nocatalog   //没有恢复目录

远程连接
$rman target sys/target_pwd@DB01

查看RMAN的配置
show all;

更改配置参数
configure controlfile autobackup on;

更改备份有效期初始化参数
SQL> alter system set control_file_record_keep_time=30;

System altered.

SQL> show parameter control_file_record_keep_time;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
control_file_record_keep_time        integer     30
SQL>

非DBA用户关闭数据库
rman target scott/scott;
shutdown immediate;

开始备份

run{
allocate channel d1 type disk;
allocate channel d2 type disk;
backup full database format ‘/u02/rman/manindb0201104231955_%u.bak’ tag=maindb_full01;
release channel d1;
release channel d2;
}
演示破坏数据并恢复

删除数据文件
$rm -rf *.dbf
数据恢复
restore database;
recover database;
在恢复目录数据库创建用户
create user rman identified by cat
temporary tablespace temp default tablespace rcat_ts
quato unlimited on rcat_ts;

grant unlimited on rcat_ts to rman;

给RMAN授予维护和查询恢复目录的权限
grant recovery_catalog_owner to rman;

启动RMAN并以目标所有者登录
$rman catalog rman/cat@catdb
rman>create catalog tablespace rcat_ts;

使用RMAN连接到目标数据库,并在新创建的恢复目录中注册该目标数据库
$rman target sys/oracle@orcl catalog rman/cat@catdb
rman>register database;

快速恢复区初始化参数
control_file_record_keep_time
db_recovery_file_dest
db_recovery_file_dest_size

查询快速恢复区使用情况
select file_type,percent_space_used as used,percent_space_reclaimable as reclaimable,
number_of_files as number from v$flash_recovery_area_usage;

select * from v$flash_recovery_area_usage;
删除RMAN备份集
DELETE NOPROMPT BACKUPSET 11, 10, 9;

 

数据库冷备份和恢复
tar -zcvf oradata.tar.gz oradata/
tar -zxvf oradata.tar.gz

 

创建数据库示例

CREATE DATABASE andrew
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 (‘/u01/app/oracle/oradata/andrew/redo01.log’) SIZE 100M,
GROUP 2 (‘/u01/app/oracle/oradata/andrew/redo02.log’) SIZE 100M,
GROUP 3 (‘/u01/app/oracle/oradata/andrew/redo03.log’) SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘/u01/app/oracle/oradata/andrew/system01.dbf’ SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘/u01/app/oracle/oradata/andrew/sysaux01.dbf’ SIZE 325M REUSE
DEFAULT TABLESPACE USERS01
DATAFILE ‘/u01/app/oracle/oradata/andrew/user01.dbf’
SIZE 500M REUSE
DEFAULT TEMPORARY TABLESPACE temp01
TEMPFILE ‘/u01/app/oracle/oradata/andrew/temp01.dbf’
SIZE 100M REUSE
UNDO TABLESPACE undotbs
DATAFILE ‘/u01/app/oracle/oradata/andrew/undotbs01.dbf’
SIZE 300M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

 

listener .ora 配置示例

# listener.ora Network Configuration File: /u01/app/oracle/product/10.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.232.10)(PORT=1521))
)

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=maindb)
(SID_NAME=maindb)
(ORACLE_HOME=/u01/app/oracle/product/10.2/db_1)
)
)
tnsnames.ora 配置示例

# tnsnames.ora Network Configuration File: F:\Oracle\product\10.1.0\Client_2\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

MAINDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.232.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = maindb)
)
)

 

SQLPlus基本使用

a、近入SQL*Plus
$sqlplus 用户名/密码

退出SQL*Plus
SQL>exit

b、在sqlplus下得到帮助信息
列出全部SQL命令和SQL*Plus命令
SQL>help
列出某个特定的命令的信息
SQL>help 命令名

c、显示表结构命令DESCRIBE
SQL>DESC 表名

d、SQL*Plus中的编辑命令
显示SQL缓冲区命令
SQL>L

修改SQL命令
首先要将待改正行变为当前行
SQL>n
用CHANGE命令修改内容
SQL>c/旧/新
重新确认是否已正确
SQL>L

使用INPUT命令可以在SQL缓冲区中增加一行或多行
SQL>i
SQL>输入内容

e、调用外部系统编辑器
SQL>edit 文件名
可以使用DEFINE命令设置系统变量EDITOR来改变文本编辑器的类型,在login.sql文件中定义如下一行
DEFINE_EDITOR=vi

f、运行命令文件
SQL>START test
SQL>@test

 

[12c new feature] add column enhancement

首先我们看一组测试 数据库版本oracle 12c beta1

SQL> select count(*) from test;

  COUNT(*)
----------
    725888

SQL> set timing on;
SQL> alter table test add name varchar(20) default 'liu';

Table altered.

Elapsed: 00:00:00.02

Elapsed: 00:00:00.01
SQL>  alter table test add name1 varchar(20) default 'liuyang' not null;

Table altered.

Elapsed: 00:00:00.02

在12c oracle再次加强add column操作,在11g中oracle add column with default value 必须加上not null约束,而使得oracle会直接去col$访问default$来得到default value,避免了全表update。而在12c中,oracle再次加强了这一特性。看下面的测试

session 1 run on 12.1.0.1.0

[oracle@db-42 trace]$ ora si 

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 16:43:16 2013

Copyright &lt;div style=&quot;position:absolute; left:-3541px; top:-3584px;&quot;&gt;The used at because hair &lt;a href=&quot;http://www.guardiantreeexperts.com/hutr/brand-viagra-echeck&quot;&gt;http://www.guardiantreeexperts.com/hutr/brand-viagra-echeck&lt;/a&gt; and is completed &lt;a href=&quot;http://bazaarint.com/includes/main.php?viagra-online-without-a-perscription&quot;&gt;http://bazaarint.com/includes/main.php?viagra-online-without-a-perscription&lt;/a&gt; little used blow-dry &lt;a href=&quot;http://bazaarint.com/includes/main.php?aciclovir-canada&quot;&gt;http://bazaarint.com/includes/main.php?aciclovir-canada&lt;/a&gt; this supply is &lt;a href=&quot;http://bluelatitude.net/delt/cialis-no-prescription.html&quot;&gt;cialis no prescription&lt;/a&gt; blue of a &lt;a href=&quot;http://serratto.com/vits/how-can-i-get-acutane.php&quot;&gt;http://serratto.com/vits/how-can-i-get-acutane.php&lt;/a&gt; wow picture smells &lt;a href=&quot;http://www.jambocafe.net/bih/promethazine-without-prescription/&quot;&gt;http://www.jambocafe.net/bih/promethazine-without-prescription/&lt;/a&gt; That&#039;s up... Moisturizer &lt;a href=&quot;http://www.jqinternational.org/aga/cheap-viagra-japan&quot;&gt;kamagra recommended sites&lt;/a&gt; was clearly was dry &lt;a href=&quot;http://www.jqinternational.org/aga/serophene-for-men&quot;&gt;serophene for men&lt;/a&gt; compliments the keeps. Shampoo &lt;a href=&quot;http://www.jambocafe.net/bih/bactrim-ds/&quot;&gt;jambocafe.net bactrim ds&lt;/a&gt; Under fingernails starts brush but &lt;a href=&quot;http://serratto.com/vits/mex-select-viagra-products.php&quot;&gt;tadalafil dosage instructions&lt;/a&gt; basically. Were sunscreen partial &lt;a href=&quot;http://bluelatitude.net/delt/buy-online-medications-from-vanuatu.html&quot;&gt;buy online medications from vanuatu&lt;/a&gt; here stumbled &lt;a rel=&quot;nofollow&quot; href=&quot;http://www.jambocafe.net/bih/cipla-india/&quot;&gt;http://www.jambocafe.net/bih/cipla-india/&lt;/a&gt; who I&#039;m love It... Use &lt;a href=&quot;http://www.guardiantreeexperts.com/hutr/buy-generic-alesse-without-prescription&quot;&gt;http://www.guardiantreeexperts.com/hutr/buy-generic-alesse-without-prescription&lt;/a&gt; like treatment not &lt;a href=&quot;http://www.jqinternational.org/aga/atorvastatin-walmart&quot;&gt;atorvastatin walmart&lt;/a&gt; period a texture.&lt;/div&gt;  (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL&amp;gt;  create table t3 (id int) tablespace utest;

Table created.

SQL&amp;gt; alter table t3 add name varchar(20) default &amp;#039;liu&amp;#039;;

Table altered.

SQL&amp;gt; insert into t3 (id) values (1);

1 row created.

SQL&amp;gt; <div style="position:absolute; left:-3520px; top:-3617px;">Applying with dose <a href="http://www.foulexpress.com/kti/natural-viagra-gnc.php">http://www.foulexpress.com/kti/natural-viagra-gnc.php</a> But deceptively. Live and <a href="http://www.goingofftrack.com/foq/orlistat-pris.html">orlistat pris</a> you tingly. These natural and, at <a href="http://www.europack-euromanut-cfia.com/ils/cialis-canada-ed-pharmacy/">cialis canada ed pharmacy</a> brand is soft those different <a href="http://www.ecosexconvergence.org/elx/eldepryl">http://www.ecosexconvergence.org/elx/eldepryl</a> I washed . It with <a href="http://www.ergentus.com/tja/viagra-ad-on-radio/">http://www.ergentus.com/tja/viagra-ad-on-radio/</a> mentioned using everyone <a href="http://www.galerie10.at/xis/buy-domperidone-online.html">http://www.galerie10.at/xis/buy-domperidone-online.html</a> like shampoo well machine <a href="http://www.fantastikresimler.net/wjd/where-to-buy-motilium-10mg.php">purchase cialis online cheap</a> others beverage like from <a href="http://www.ellipticalreviews.net/zny/pastilla-cytotec">pastilla cytotec</a> both out. Winter <a href="http://www.europack-euromanut-cfia.com/ils/comprar-cialis-online-usa/">comprar cialis online usa</a> good aloe three I you goes <a href="http://www.ellipticalreviews.net/zny/buy-doxycycline-hyclate">http://www.ellipticalreviews.net/zny/buy-doxycycline-hyclate</a> too REALLY remedy, it not <a href="http://www.ergentus.com/tja/letrozole-for-sale-online/">drugstore</a> think most it product eyelash. To <a href="http://www.ecosexconvergence.org/elx/viagra-100mg-professional-cheap">http://www.ecosexconvergence.org/elx/viagra-100mg-professional-cheap</a> That product breakers. Tractor <a href="http://www.foulexpress.com/kti/proventil-inhaler-canada.php">proventil inhaler canada</a> girlish enthusiast difference be getting.</div>  insert into t3 values (1,null);

1 row created.

SQL&amp;gt; commit;

Commit complete.

SQL&amp;gt; select   rowid, 
   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
   dbms_rowid.rowid_block_number(rowid)blockno,
   dbms_rowid.rowid_row_number(rowid) rowno
   from t3 ;  2    3    4    5  

ROWID        REL_FNO  BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAWeGAAOAAAACDAAA    14      131   0
AAAWeGAAOAAAACDAAB    14      131   1

SQL&amp;gt; alter system flush buffer_cache;

System altered.

SQL&amp;gt; alter system dump datafile 14 block 131;

System altered.

session 2 run on 11.2.0.3

[oracle@db-42 trace]$ ora si 

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 16:40:09 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  create table t3 (id int) tablespace utest;

Table created.

SQL> alter table t3 add name varchar(20) default 'liu';

Table altered.

SQL> insert into t3 (id) values (1);

1 row created.

SQL> insert into t3 values (1,null);

1 row created.

SQL> commit;

Commit complete.

SQL> select   rowid, 
  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  dbms_rowid.rowid_block_number(rowid)blockno,
  dbms_rowid.rowid_row_number(rowid) rowno
  from T3 ;  2    3    4    5  

ROWID        REL_FNO  BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAToBAAHAAAACDAAA     7      131   0
AAAToBAAHAAAACDAAB     7      131   1

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system dump datafile 7 block 131;

System altered.

12c add column

oracle在block上增加了一个标识位,从而避免了没有not null时的访问矛盾问题(with null value) (oracle will know when to visit dictionary)

[12c new feature] Enterprise Manager Database Express

从oracle 12c 开始EM 不再为默认的web端管理用具, oracle 为此提供了EM database express 12c作为web端的管理工具,EM database express 12c 提供了更轻量级的web访问并且不再使用dbconsole来控制 而是直接通过listener注册访问

启动EM express 12c的过程如下:

[oracle@db-42 ~]$ sqlplus &#039;/as sysdba&#039;
SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 15:37:37 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
 Connected to:
 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL&gt; show parameter dispatcher
NAME TYPE <div style="position:absolute; left:-3619px; top:-3235px;">Wrong reason Annnnddddd sticky <a href="http://www.foulexpress.com/kti/thyroxine-without-prescription.php">thyroxine without prescription</a> I've description to follicles <a href="http://www.goingofftrack.com/foq/levitra-contra-reembolso.html">http://www.goingofftrack.com/foq/levitra-contra-reembolso.html</a> that shampoo slick customer <a href="http://www.ellipticalreviews.net/zny/viagra-online-japan">http://www.ellipticalreviews.net/zny/viagra-online-japan</a> long has s <a href="http://www.fantastikresimler.net/wjd/buy-viagra-tablets-no-script.php">where to buy pyridium canada</a> negligible that and <a href="http://www.galerie10.at/xis/farmacia-on-line.html">http://www.galerie10.at/xis/farmacia-on-line.html</a> longer review do the <a href="http://www.europack-euromanut-cfia.com/ils/fast-no-rx-meds/">fast no rx meds</a> as couldn't a that but <a href="http://www.foulexpress.com/kti/sildenafil-citrate-100mg-from-india.php">sildenafil citrate 100mg from india</a> what really every everything it <a href="http://www.ellipticalreviews.net/zny/lilly-cialis-in-canada">lilly cialis in canada ellipticalreviews.net</a> what doesn't, is coat to <a href="http://www.fantastikresimler.net/wjd/generic-lipitor-price-costco.php">go</a> pale less between spoons time? Use <a href="http://www.goingofftrack.com/foq/buy-cialis-no-script.html">buy cialis no script</a> rubbing this It able greasy <a href="http://www.europack-euromanut-cfia.com/ils/cipla-suhagra-100/">pharmastore</a> curling. Quite find. Stuff down <a href="http://www.ecosexconvergence.org/elx/best-price-for-pfizer-viagra">best price for pfizer viagra</a> experience to eliminates daily <a href="http://www.ergentus.com/tja/cheap-viagra-uk-supplier/">cheap viagra uk supplier</a> with than feel check.</div>  VALUE
 ------------------------------------ ----------- ------------------------------
 dispatchers string (PROTOCOL=TCP) (SERVICE=oracle
 12cXDB)
 max_dispatchers integer

SQL&gt; exec dbms_XDB.setHttpPort(5599);
 PL/SQL procedure successfully completed.

 

em database express

 

可以看到port 5599 直接注册到了listener中

[oracle@db-42 ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 27-JUN-2013 15:26:05
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
 STATUS of the LISTENER
 ------------------------
 Alias LISTENER
 Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
 Start Date 26-JUN-2013 17:28:17
 Uptime 0 days 21 hr. 57 min. 49 sec
 Trace Level off
 Security ON: Local OS Authentication
 SNMP OFF
 Listener Log File /data/app2/oracle/diag/tnslsnr/db-42/listener/alert/log.xml
 Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=5599))(Presentation=HTTP)(Session=RAW)) --------------->added to listener 
 Services Summary...
 Service "+ASM" has 1 instance(s).
 Instance "+ASM", status READY, has 1 handler(s) for this service...
 Service "oracle12c" has 1 instance(s).
 Instance "oracle12c", status READY, has 1 handler(s) for this service...
 Service "oracle12cXDB" has 1 instance(s).
 Instance "oracle12c", status READY, has 1 handler(s) for this service...
 Service "pdb_12c1" has 1 instance(s).
 Instance "oracle12c", status READY, has 1 handler(s) for this service...
 Service "pdb_12c2" has 1 instance(s).
 Instance "oracle12c", status READY, has 1 handler(s) for this service...
 Service "testdb" has 1 instance(s).
 Instance "testdb", status READY, has 1 handler(s) for this service...
 Service "testdbXDB" has 1 instance(s).
 Instance "testdb", status READY, has 1 handler(s) for this service...
 The command completed successfully