DBA Blogs

I have used APEX 4.2.2. I was unable to logon my APEX with INTERNAL workspace as admin. It showed "The account is locked".
What was I able to do? ... run "apxchpwd.sql" script. I didn't think so. After checking in "apxchpwd.sql" script.
So, I checked in "wwv_flow_fnd_user_api" package, I found wwv_flow_fnd_user_api.UNLOCK_ACCOUNT procedure.
PROCEDURE UNLOCK_ACCOUNT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_USER_NAME                    VARCHAR2                INI thought I should test it. First of all, I must to make sure "security_group_id"(It should be number 10).
SQL> SELECT workspace_id FROM apex_workspaces WHERE workspace = 'INTERNAL';

WORKSPACE_ID
------------
          10Tested to unlock "admin" user on "INTERNAL" workspace.
SQL> alter session set current_schema = APEX_040200;

Session altered.

SQL> begin

    wwv_flow_security.g_security_group_id := 10;
    wwv_flow_fnd_user_api.UNLOCK_ACCOUNT('ADMIN');
    commit;end;
/

PL/SQL procedure successfully completed.
then I tested login again... It works for me. ^______________^


Written By: Surachart Opun http://surachartopun.com

There are certain dictionary views available to monitor the Exadata cells from the database.One of the view is gv$cell_thread_history. This view is very interesting as it delves down to the session level with its serial number, SQL, specific cell, instance and also which Exadata object was waited and every entry is logged with the time. Looks very cool:

SQL> desc gv$cell_thread_history
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 INST_ID                                                        NUMBER
 CELL_NAME                                                      VARCHAR2(1024)
 SNAPSHOT_ID                                                    NUMBER
 SNAPSHOT_TIME                                                  DATE
 THREAD_ID                                                      NUMBER
 JOB_TYPE                                                       VARCHAR2(32)
 WAIT_STATE                                                     VARCHAR2(32)
 WAIT_OBJECT_NAME                                               VARCHAR2(32)
 SQL_ID                                                         VARCHAR2(13)
 DATABASE_ID                                                    NUMBER
 INSTANCE_ID                                                    NUMBER
 SESSION_ID                                                     NUMBER
 SESSION_SERIAL_NUM                                             NUMBER

Here is a sample code snippet:

SQL> select wait_object_name,count(*) cnt from gv$cell_thread_history group by wait_object_name order by cnt;

WAIT_OBJECT_NAME                        CNT
-------------------------------- ----------
UserThread Cond                           4
PredicateDisk-113                         4
bwresv Q_1/2k                             4
bufWaitObjQ_8k                            4
PredicateDiskRead IO Completion           4
bwresv Q_8k                               4
FSA: cache replacementQ_1MHugePa          4
CachePut Fence                            4
NetworkDirectory ReqHandle                4
PredicateCacheGetJob                      4
Flash Cache LRU                           4
FSA: Cache Get Job                        4
DiskDirectory HT                          4
GenTimeStats Operation                    4
in use Q_8k                               4
Completed Map Element List                4
gdisk ext                                 4
FSA: fcCtxt                               8
FSA: cache replacementQ_8k                8
FlashCache HT buckets                     8
PredicateDisk-110                         8
FC outstanding IOs                        8
IOContext                                 8
Cache Completed Jobs                      8
PredicateDisk-32                         12
PredicateDisk-56                         12
PredicateDisk-50                         12
PredicateDisk-22                        12

News and views are an integral part of our modern daily life. When it comes to the information-hungry roles such as database professionals, such need becomes more pressing. Log Buffer is one way to keep abreast of news and views from the world of Oracle, SQL Server, and MySQL.

Oracle:

There is a significant update SLOB 2, Kevin Closson informs.
Charles Hooper is grouping Data Sets by Week Number of the Month.
Chris Antognini has shared a script that is used to demo ITL deadlocks.
As Exadata Storage Indexes (SI) are purely memory only structures located on the Exadata storage servers, care needs to be taken in how much memory they can potentially consume.
Connon McDonald says that its not about the outage, but….

SQL Server:

Richard Douglas is giving information about SQL Server events in UK.
Thomas LaRock has been involved in a virtualization projects for almost ten years now.
Kendra Little has five things about Fillfactor.
Kevin Kline suggests to use TPC database benchmarks to save money.
Here’s a quick mystery. You have a SQL Server with several jobs that appear to be running properly and on schedule, but many of them show no history.

MySQL:

How does InnoDB behave without a Primary Key? Jeremy Cole asks.
The MariaDB project is pleased to announce the immediate availability of the MariaDB Java Client 1.1.2.
Normal 0 false false false EN-US X-NONE X-NONE Everybody loves free. It is the best marketing term one could use.
MySQL Cluster Manager 1.2.3 is now available to download from My Oracle Support.
When is a Subquery Executed?  Øystein Grøvlen writes.

This post appeared at Pythian Blog initially.

This is a good web site with Oracle internals information:

http://www.ixora.com.au/notes/

I’ve benefited from this one for years.

- Bobby

 

Mothers day is coming up fast. May 12th 2013 is just around the corner and If you have not already done so, make sure you go out and get mom a special gift as soon as possible. If you need a hand picking something great, take a look at this mothers day gift guide by the HuffingtonPost

Many this year like they have in previous years shopped online and bought mom that special gift from an online retailer, a simple, easy and fun process. But is it always? Unfortunately for many, the experience did not live up to expectation due to site performance and both customers and merchants end up losing.

Is this a big deal? We put together this infograph on mothers day shopping. Take a look.

 

A quick request...

If you are using RAW devices for your database (no ASM, no filesystem - just 'raw') - please drop me a line and let me know.  Include the size of  your database as well please.

thanks!

Believe me, this article is worth reading I’m currently not allowed to discuss Oracle 12c Database goodies but I am allowed to discuss things perhaps initially intended for 12c that are currently available and already back-ported to 11g. This includes a wonderful improvement in the manageability of how the Clustering Factor (CF) of an index can now […]

I have a chance to read a book - Hadoop Real-World Solutions Cookbook(Thank you ^______^). It pops up in my head, why I have never tested about Sqoop. As you know sqoop is a tool designed for efficiently transferring bulk data between Apache Hadoop and structured datastores such as relational databases. So, I wanna test a little bit about it. I choose to read data from mysql into HDFS.
I assume my data in mysql
[surachart@centos ~]$ mysql -u surachart mydb -p < a.sql
Enter password:
[surachart@centos ~]$ mysql -u surachart mydb -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.69 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from mytable;
+----------+
| count(*) |
+----------+
|  1295462 |
+----------+
1 row in set (0.00 sec)

mysql>After I have my data, I download Sqoop (binary) and test it.
[surachart@centos ~]$ ls sqoop-1.4.3.bin__hadoop-0.20.tar.gz
sqoop-1.4.3.bin__hadoop-0.20.tar.gz
[surachart@centos ~]$ tar zxf sqoop-1.4.3.bin__hadoop-0.20.tar.gz

[surachart@centos ~]$ cd  sqoop-1.4.3.bin__hadoop-0.20
[surachart@centos sqoop-1.4.3.bin__hadoop-0.20]$ cd bin/
[surachart@centos bin]$ pwd
/home/surachart/sqoop-1.4.3.bin__hadoop-0.20/bin

[surachart@centos bin]$ ./sqoop
Error: /usr/lib/hadoop does not exist!
Please set $HADOOP_COMMON_HOME to the root of your Hadoop installation.
[surachart@centos bin]$ export HADOOP_COMMON_HOME=/usr
[surachart@centos bin]$ ./sqoop
Error: /usr/lib/hadoop-mapreduce does not exist!
Please set $HADOOP_MAPRED_HOME to the root of your Hadoop MapReduce installation.
[surachart@centos bin]$ export HADOOP_MAPRED_HOME=/usr
[surachart@centos bin]$ ./sqoop
Warning: /usr/lib/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Try 'sqoop help' for usage.
[surachart@centos bin]$ export HBASE_HOME=/home/surachart/hbase
[surachart@centos bin]$ ./sqoop
Try 'sqoop help' for usage.

[surachart@centos bin]$ ./sqoop help
usage: sqoop COMMAND [ARGS]

Available commands:
  codegen            Generate code to interact with database records
  create-hive-table  Import a table definition into Hive
  eval               Evaluate a SQL statement and display the results
  export             Export an HDFS directory to a database table
  help               List available commands
  import             Import a table from a database to HDFS
  import-all-tables  Import tables from a database to HDFS
  job                Work with saved jobs
  list-databases     List available databases on a server
  list-tables        List available tables in a database
  merge              Merge results of incremental imports
  metastore          Run a standalone Sqoop metastore
  version            Display version information

See 'sqoop help COMMAND' for information on a specific command.

[surachart@centos bin]$ hadoop fs -mkdir /user/surachart/import
[surachart@centos bin]$ hadoop fs -ls /user/surachart/import
[surachart@centos bin]$
[surachart@centos bin]$ ./sqoop import -m 1 --connect jdbc:mysql://localhost:3306/mydb --username surachart --password password --table mytable --target-dir /user/surachart/import
13/05/08 12:11:42 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/05/08 12:11:42 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/05/08 12:11:42 INFO tool.CodeGenTool: Beginning code generation
13/05/08 12:11:43 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: Could not load db driver class: com.mysql.jdbc.Driver
java.lang.RuntimeException: Could not load db driver class: com.mysql.jdbc.Driver
        at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:716)
        at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
        at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:605)
        at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:628)
        at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:235)
        at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:219)
        at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:283)
        at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1255)
        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1072)
        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:82)
        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:390)
        at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:476)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:238)Note: On Sqoop no mysql lib, So Download "mysql-connector-java-5.1.25" on http://dev.mysql.com/downloads/connector/j/ and copy it to sqoop lib path.
[surachart@centos ~]$ ls mysql-connector-java-5.1.25.zip
mysql-connector-java-5.1.25.zip
[surachart@centos ~]$ unzip mysql-connector-java-5.1.25.zip
[surachart@centos ~]$ cp mysql-connector-java-5.1.25/mysql-connector-java-5.1.25-bin.jar sqoop-1.4.3.bin__hadoop-0.20/lib/After everything should be fine. Try again.
[surachart@centos ~]$ cd sqoop-1.4.3.bin__hadoop-0.20/bin/
[surachart@centos bin]$ ./sqoop import --direct -m 1 --connect jdbc:mysql://localhost:3306/mydb --username surachart --password password --table mytable --target-dir /user/surachart/import
13/05/08 12:27:54 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/05/08 12:27:54 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/05/08 12:27:54 INFO tool.CodeGenTool: Beginning code generation
13/05/08 12:27:56 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `mytable` AS t LIMIT 1
13/05/08 12:27:56 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `mytable` AS t LIMIT 1
13/05/08 12:27:56 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr
Note: /tmp/sqoop-surachart/compile/7d652c85de6562a56d07c0b4017e3cd4/mytable.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
13/05/08 12:28:04 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-surachart/compile/7d652c85de6562a56d07c0b4017e3cd4/mytable.jar
13/05/08 12:28:04 INFO manager.DirectMySQLManager: Beginning mysqldump fast path import
13/05/08 12:28:04 INFO mapreduce.ImportJobBase: Beginning import of mytable
13/05/08 12:28:10 INFO mapred.JobClient: Cleaning up the staging area hdfs://centos:8020/user/surachart/.staging/job_201305081226_0002
13/05/08 12:28:10 ERROR security.UserGroupInformation: PriviledgedActionException as:surachart cause:org.apache.hadoop.mapred.FileAlreadyExistsException: Output directory /user/surachart/import already exists
13/05/08 12:28:10 ERROR tool.ImportTool: Encountered IOException running import job: org.apache.hadoop.mapred.FileAlreadyExistsException: Output directory /user/surachart/import already exists
        at org.apache.hadoop.mapreduce.lib.output.FileOutputFormat.checkOutputSpecs(FileOutputFormat.java:137)
        at org.apache.hadoop.mapred.JobClient$2.run(JobClient.java:949)
        at org.apache.hadoop.mapred.JobClient$2.run(JobClient.java:912)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAs(Subject.java:396)
        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1136)
        at org.apache.hadoop.mapred.JobClient.submitJobInternal(JobClient.java:912)
        at org.apache.hadoop.mapreduce.Job.submit(Job.java:500)
        at org.apache.hadoop.mapreduce.Job.waitForCompletion(Job.java:530)
        at org.apache.sqoop.mapreduce.ImportJobBase.doSubmitJob(ImportJobBase.java:173)
        at org.apache.sqoop.mapreduce.ImportJobBase.runJob(ImportJobBase.java:151)
        at org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:221)
        at org.apache.sqoop.manager.DirectMySQLManager.importTable(DirectMySQLManager.java:92)
        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:403)
        at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:476)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:238)

[surachart@centos bin]$ ./sqoop import --direct -m 1 --connect jdbc:mysql://localhost:3306/mydb --username surachart --password password --table mytable --target-dir /user/surachart/import/mytable
13/05/08 12:28:23 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/05/08 12:28:24 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/05/08 12:28:24 INFO tool.CodeGenTool: Beginning code generation
13/05/08 12:28:26 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `mytable` AS t LIMIT 1
13/05/08 12:28:26 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `mytable` AS t LIMIT 1
13/05/08 12:28:26 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr
Note: /tmp/sqoop-surachart/compile/98909e84f7e2215902ebdbcca40cfa9f/mytable.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
13/05/08 12:28:33 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-surachart/compile/98909e84f7e2215902ebdbcca40cfa9f/mytable.jar
13/05/08 12:28:34 INFO manager.DirectMySQLManager: Beginning mysqldump fast path import
13/05/08 12:28:34 INFO mapreduce.ImportJobBase: Beginning import of mytable
13/05/08 12:28:42 INFO mapred.JobClient: Running job: job_201305081226_0003
13/05/08 12:28:43 INFO mapred.JobClient:  map 0% reduce 0%
13/05/08 12:29:34 INFO mapred.JobClient:  map 100% reduce 0%
13/05/08 12:29:56 INFO mapred.JobClient: Job complete: job_201305081226_0003
13/05/08 12:29:56 INFO mapred.JobClient: Counters: 18
13/05/08 12:29:56 INFO mapred.JobClient:   Job Counters
13/05/08 12:29:56 INFO mapred.JobClient:     SLOTS_MILLIS_MAPS=54352
13/05/08 12:29:56 INFO mapred.JobClient:     Total time spent by all reduces waiting after reserving slots (ms)=0
13/05/08 12:29:56 INFO mapred.JobClient:     Total time spent by all maps waiting after reserving slots (ms)=0
13/05/08 12:29:56 INFO mapred.JobClient:     Launched map tasks=1
13/05/08 12:29:56 INFO mapred.JobClient:     SLOTS_MILLIS_REDUCES=0
13/05/08 12:29:56 INFO mapred.JobClient:   File Output Format Counters
13/05/08 12:29:56 INFO mapred.JobClient:     Bytes Written=23687032
13/05/08 12:29:56 INFO mapred.JobClient:   FileSystemCounters
13/05/08 12:29:56 INFO mapred.JobClient:     HDFS_BYTES_READ=87
13/05/08 12:29:56 INFO mapred.JobClient:     FILE_BYTES_WRITTEN=36476
13/05/08 12:29:56 INFO mapred.JobClient:     HDFS_BYTES_WRITTEN=23687032
13/05/08 12:29:56 INFO mapred.JobClient:   File Input Format Counters
13/05/08 12:29:56 INFO mapred.JobClient:     Bytes Read=0
13/05/08 12:29:56 INFO mapred.JobClient:   Map-Reduce Framework
13/05/08 12:29:56 INFO mapred.JobClient:     Map input records=1
13/05/08 12:29:56 INFO mapred.JobClient:     Physical memory (bytes) snapshot=73830400
13/05/08 12:29:56 INFO mapred.JobClient:     Spilled Records=0
13/05/08 12:29:56 INFO mapred.JobClient:     CPU time spent (ms)=16210
13/05/08 12:29:56 INFO mapred.JobClient:     Total committed heap usage (bytes)=29818880
13/05/08 12:29:56 INFO mapred.JobClient:     Virtual memory (bytes) snapshot=1179168768
13/05/08 12:29:56 INFO mapred.JobClient:     Map output records=1295462
13/05/08 12:29:56 INFO mapred.JobClient:     SPLIT_RAW_BYTES=87
13/05/08 12:29:56 INFO mapreduce.ImportJobBase: Transferred 22.5897 MB in 81.613 seconds (283.4336 KB/sec)
13/05/08 12:29:56 INFO mapreduce.ImportJobBase: Retrieved 1295462 records.

Check data in HDFS.
[surachart@centos bin]$ hadoop fs -ls /user/surachart/import
Found 1 items
drwx------   - surachart surachart          0 2013-05-08 12:29 /user/surachart/import/mytable
[surachart@centos bin]$ hadoop fs -ls /user/surachart/import/mytable
Found 2 items
-rw-------   3 surachart surachart          0 2013-05-08 12:29 /user/surachart/import/mytable/_SUCCESS
-rw-------   3 surachart surachart   23687032 2013-05-08 12:29 /user/surachart/import/mytable/part-m-00000Note: data in /user/surachart/import/mytable/part-m-00000 file, that is CSV type.


Written By: Surachart Opun http://surachartopun.com

Today, I have a chance to review a good book about Oracle Database - Managing Multimedia and Unstructured Data in the Oracle Database by Marcelle Kratochvil.
Author, she is CTO and co-founder of Piction, an Oracle PL/SQL based app that allows for the delivery of Images on the internet. Marcelle has been working in the IT industry for 20 years. In this time she has presented over 40 papers in Australia and the United States. Marcelle is both a DBA and Developer and has had experience using Oracle on MVS, VMS, Unix and Windows. She has worked on ultra-small databases to multi-terabyte sized ones. Marcelle has been using Oracle Multimedia since Oracle8, and has been using Oracle as a DBA and Developer since Oracle4. She also specialises in PL/SQL, Java, Gateways, Very Large Databases, Security and Database tuning. Her current focus is on Digital Imaging, Audio, Video, Spatial and all other types of binary object.As author wrote in a book , the aim of this book is to try and give a basic understanding to a lot of concepts involving unstructured data. Particular focus is given to multimedia (smart media or rich media). So, readers will get idea about Unstructured Data, Digital Objects, and many things. Anyway, if you are interested in increasing your knowledge and understanding more for multimedia, how to manage unstructured data  and why you should use Oracle Database with unstructured data. This book fits for you,  you are supposed to read it.

This book has 10 chapters. You will learn.
- Discover a whole new world beyond relational databases
- Understand what is involved in selling a digital image
- Learn about the different types of multimedia warehouses
- Uncover the truth behind searching for digital objects
- Understand the complete picture for tuning an Oracle database with multimedia
- Get to grips with all the issues in setting up a digital asset e-commerce system
- Understand what multimedia and unstructured data really is
- Realise how the Oracle database can work with multimedia
- Get to grips with digital image processing and transformation techniques
- Expand your database knowledge to include complex data
- Add credibility to your resume by adopting this new and visually exciting technological direction

Note: Appendix E, Loading and Reading, is not present in the book but is available for download at the following link: http://www.packtpub.com/sites/default/files/downloads/AppendixE_loading_and_reading.pdf

What do I think?  Easy to read and clearly about digital data. Give good idea to manage unstructured data, show idea why there uses Oracle Database and learn from good examples. This book will give benefit for some people who are interested in managing multimedia and Unstructured data in the Oracle Database or increasing knowledge about Data Digital. This book covers about database tuning as well. This chapter helps DBAs learn new concepts, skills, and techniques that are required to manage very large multimedia databases. You also get idea to understand the limitations of oracle database with unstructured data.Written By: Surachart Opun http://surachartopun.com

I was interested in how to do twitter streaming. I tried to find out on the internet and got many examples. I tested it with python code(tweepy). I got data in JSON format. I thought it's a good idea, if I was able insert this data into mongodb.
So. I created my application on twitter and tested some a little bit code.
[surachart@centos ~]$ ./mongo/bin/mongo
MongoDB shell version: 2.2.2
connecting to: test
>
> db.twittertest.find()
> db.twittertest.find()Used some code on the internet and changed a bit to insert data into database.
[surachart@centos ~]$ cat ./test-tweeter.py
#!/usr/bin/python
import tweepy
import pymongo
import json

consumer_key = "..."
consumer_secret = "..."
access_token_key = "..."
access_token_secret = "..."


auth1 = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth1.set_access_token(access_token_key, access_token_secret)

class StreamListener(tweepy.StreamListener):
    def on_status(self, tweet):
        print tweet.text

    def on_error(self, status_code):
        print 'Error: ' + repr(status_code)
        return False

    def on_data(self, data):
        print 'Ok! Inserting Data.'
        from pymongo import MongoClient
        client = MongoClient()
        client = MongoClient('localhost', 27017)
        db = client.test
        test_id = db.twittertest.insert(json.loads(data))

l = StreamListener()
streamer = tweepy.Stream(auth=auth1, listener=l)
streamer.filter(track=['oracle'])I filtered by using word 'oracle'.
[surachart@centos ~]$ ./test-tweeter.py
Ok! Inserting Data.

[surachart@centos ~]$ ./mongo/bin/mongo
MongoDB shell version: 2.2.2
connecting to: test
>
> db.twittertest.find()
{ "_id" : ObjectId("51851511cdc8722152c28171"), "contributors" : null, "truncated" : false, "text" : "Job Opportunity for Oracle PL/SQL Developers with HTML/Web development experience for 6+ Months(Contract) for Las...: http://t.co/p6p8cfV6qF", "in_reply_to_status_id" : null, "id" : NumberLong("330684010961448962"), "favorite_count" : 0, "source" : "<a href=\"http://www.linkedin.com/\" rel=\"nofollow\">LinkedIn</a>", "retweeted" : false, "coordinates" : null, "entities" : { "symbols" : [ ], "user_mentions" : [ ], "hashtags" : [ ], "urls" : [     {       "url" : "http://t.co/p6p8cfV6qF",       "indices" : [   118,    140 ],  "expanded_url" : "http://lnkd.in/G-hhiX",       "display_url" : "lnkd.in/G-hhiX" } ] }, "in_reply_to_screen_name" : null, "id_str" : "330684010961448962", "retweet_count" : 0, "in_reply_to_user_id" : null, "favorited" : false, "user" : { "follow_request_sent" : null, "profile_use_background_image" : true, "default_profile_image" : true, "id" : 360409067, "verified" : false, "profile_image_url_https" : "https://si0.twimg.com/sticky/default_profile_images/default_profile_4_normal.png", "profile_sidebar_fill_color" : "DDEEF6", "profile_text_color" : "333333", "followers_count" : 22, "profile_sidebar_border_color" : "C0DEED", "id_str" : "360409067", "profile_background_color" : "C0DEED", "listed_count" : 1, "profile_background_image_url_https" : "https://si0.twimg.com/images/themes/theme1/bg.png", "utc_offset" : null, "statuses_count" : 1777, "description" : null, "friends_count" : 0, "location" : "", "profile_link_color" : "0084B4", "profile_image_url" : "http://a0.twimg.com/sticky/default_profile_images/default_profile_4_normal.png", "following" : null, "geo_enabled" : false, "profile_background_image_url" : "http://a0.twimg.com/images/themes/theme1/bg.png", "name" : "Anand P. Sharma", "lang" : "en", "profile_background_tile" : false, "favourites_count" : 0, "screen_name" : "anandph123", "notifications" : null, "url" : null, "created_at" : "Tue Aug 23 04:52:03 +0000 2011", "contributors_enabled" : false, "time_zone" : null, "protected" : false, "default_profile" : true, "is_translator" : false }, "geo" : null, "in_reply_to_user_id_str" : null, "possibly_sensitive" : false, "lang" : "en", "created_at" : "Sat May 04 14:03:06 +0000 2013", "filter_level" : "medium", "in_reply_to_status_id_str" : null, "place" : null }
>I was able to insert data...  and I will play with it later.


Written By: Surachart Opun http://surachartopun.com

Over the years, I've encountered myths around nearly every Oracle product.  Lately the number of challenges around Enterprise Manager seem to have increased.

Blue Medora has created several Cloud Control extensions (plugins) and asked me to blog as a guest on their site.  I've decided to challenge some of the current myths.

Feel free to read my comments there at

http://www.bluemedora.com/blog/debunking-some-persistent-myths-about-oracle-enterprise-manager

In my previous post I explained that setting db_writer_processes=1, dbwr_io_slaves=32 made a 2-3 times reduction in run time of my test of 15 concurrent updates.

Further testing has shown that an even greater improvement – really 10 times – can be made by switching to direct IO and maxing out the db writer processes.

To switch my test database to direct IO I had to do two things:

1. Ask one of our Unix administrators to remount the filesystem that contains the datafile being tested using these options: mincache=direct,convosync=direct
2. Change this parameter: filesystemio_options=directIO

Then I switched to what the documentation says is the maximum number of db writers:

1. db_writer_processes=36
2. dbwr_io_slaves=0

I had setup a test that generated free buffer waits by changing my update statements to update more blocks than could be held in the buffer cache and I had set log_checkpoint_interval back to its default of 0 so we wouldn’t get frequent checkpoints.  I also increased the redo logs to 2 gig so they wouldn’t switch and checkpoint frequently.  So, my test was getting plenty of free buffer waits and it took roughly 30 minutes for my 15 concurrent update statements to update 1 million rows each.  This was with my current production settings of db_writer_processes=4 and dbwr_io_slaves=0.

Here is a profile of the time spent by one update statement with no direct io and db_writer_processes=4 and dbwr_io_slaves=0:

TIMESOURCE                    SECONDS PERCENTAGE
-------------------------- ---------- ----------
TOTAL_TIME                       2503        100
free buffer waits                2097         84
db file scattered read            253         10
CPU                                61          2
UNACCOUNTED_TIME                   57          2
db file sequential read            26          1
latch: redo copy                    5          0
events in waitclass Other           2          0
log buffer space                    1          0

Here is a profile with the direct io options and db_writer_processes=36, dbwr_io_slaves=0:

TIMESOURCE                    SECONDS PERCENTAGE
-------------------------- ---------- ----------
TOTAL_TIME                        171        100
free buffer waits                  51         30
UNACCOUNTED_TIME                   41         24
db file scattered read             34         20
CPU                                23         13
log buffer space                   16          9
events in waitclass Other           3          2
latch: redo copy                    2          1

Incredible.  Thanks to Jonathan Lewis and Mark Powell for all of their patient discussion of this issue with me on our forum thread.

So, I guess the bottom line is that if you can’t get your filesystems mounted with direct IO options then the IO slaves may be the way to go in certain scenarios.  But, with direct IO it appears that upping the number of db writers is better than using IO slaves, at least in a scenario like mine were you have many concurrent updates filling the buffer cache with updated blocks and waiting on free buffer waits.

- Bobby

News and views are an integral part of our modern daily life. When it comes to the information-hungry roles such as database professionals, such need becomes more pressing. Log Buffer is one way to keep abreast of news and views from the world of Oracle, SQL Server, and MySQL.

Oracle:

There is a significant update SLOB 2, Kevin Closson informs.

Charles Hooper is grouping Data Sets by Week Number of the Month.

Chris Antognini has shared a script that is used to demo ITL deadlocks.

As Exadata Storage Indexes (SI) are purely memory only structures located on the Exadata storage servers, care needs to be taken in how much memory they can potentially consume.

Connon McDonald says that its not about the outage, but….

SQL Server:

Richard Douglas is giving information about SQL Server events in UK.

Thomas LaRock has been involved in a virtualization projects for almost ten years now.

Kendra Little has five things about Fillfactor.

Kevin Kline suggests to use TPC database benchmarks to save money.

Here’s a quick mystery. You have a SQL Server with several jobs that appear to be running properly and on schedule, but many of them show no history.

MySQL:

How does InnoDB behave without a Primary Key? Jeremy Cole asks.

The MariaDB project is pleased to announce the immediate availability of the MariaDB Java Client 1.1.2.

Normal 0 false false false EN-US X-NONE X-NONE Everybody loves free. It is the best marketing term one could use.

MySQL Cluster Manager 1.2.3 is now available to download from My Oracle Support.

When is a Subquery Executed?  Øystein Grøvlen writes.

I came across the requirement the other day to update Salesforce every 5 minutes with data from Oracle. 
 The data in Oracle was a simple table with few columns A,B,C  and a timestamp column T indicating the last modified date/time of the record. 
To my surprise whenever I sent data changes from Oracle, mapping the columns and the timestamp to their corresponding Salesforce fields, the Salesforce field T would record the time 1 hour ahead of the wall clock time!
Quickly I realized that Salesforce, no matter where you are in the wold entering data to it, it always saves your standard / custom date/time fields in UTC (think of it as new GMT). The UI takes care of displaying your local time by working out the difference of where you are from the UTC timestamp. 
The 1 hour difference I was experiencing was because of Time-Zones and DST - Daylight Saving Time. I live in London, United Kingdom and currently the DST here says BST which is British Summer Time, and that is GMT + 1 hour. So if you modify data in Salesforce at 14:00, according to wall clocks in London, in your custom timestamp field in Salesforce it will actually record 13:00! (UTC time).
So when sending timestamps to Salesforce you have to send them in UTC!  
In case of my Oracle table A,B,C,T the date/time column was saying:
01/05/2013 17:07:20

If you send this timestamp to Salesforce as is, it will record this as a UTC value, but then because of the Salesforce UI and because of BST (GMT+1 DST) it will display:
01/05/2013 18:07:20 (1 hour ahead in the future!)

So the solution was to calculate the date/time in Oracle as UTC always, as this is the accepted value for Salesforce. To do exactly that and to provide the date/time field always in UTC no matter of DST I used the Oracle SQL CAST function to obtain the given date/time value at GMT (UTC) like this:
select to_char(cast(my_date as timestamp with local time zone) at time zone 'GMT', 'DD/MM/YYYY HH24:MI:SS') utc_my_date from my_table;

The formatting above will always return a GMT (UTC) date/time no matter which timezone or DST you are in. Then you can safely pass that as a timestamp to Salesforce and be sure that the correct time is going to be saved in Salesforce.

Here is an interesting idea  :  Gamifying the DBA Experience
.
.
.

Not very long before we kick off the 2013 Great Lakes Oracle Conference in Cleveland ( 2013 Great Lakes Oracle Conference ).  This will be our biggest and best ever conference.

Key notes by Tom Kyte and 2 from CJ Date ( the godfather of relational database design ).  Workshop sessions by Joel Kallman ( Apex I never new it was this easy ) / Tom Kyte / Craig Shallahamer.

Two days of sessions by top Oracle national class presenters Oakies / Aces / Ace Directors ... we got the whole enchilada ... please check it out and think about attending ... May 13/14/15 2013.

Hope to see you there!  If not this year then next year and think about submitting a presentation abstract next year ( yeah that's you Mr. Gorbachev and the pythian gang!  also you Enkitec people )!

Reblogged from So Many Oracle Manuals, So Little Time: FOR IMMEDIATE RELEASE San Francisco (April 1, 2013) – In a dramatic move calculated to give fresh life to the moribund relational database movement, the latest version of Oracle Corporation’s flagship database has eliminated the famous “join penalty” by making it possible to store rows from […]

“What have I gotten myself into” ran through my head during the first week of training at Pythian. The environment was great, the people were great, the work was great, but many doubts ran through my head as I ran from training meeting to training meeting – how am I going to retain all this information (my memory skills are bad as it is), am I going to meet their expectations, am I too junior for this, I can’t seem to catch up on anything, is this the right career choice.
6 months later and I’m still here! I feel more comfortable with the work, I know I am meeting expectations, I have confidence in my skills, my organizational skills have improved, I know I’ve made the right career choice, even my memory has gotten much better! What changed over the past 6 months you ask? Well, here is how it started.

I was happy and content at my previous employer. I knew what I was doing, I had the skills and knowledge, and I was regarded as one of their senior employees. Although it was mainly recruiting work I was performing, it was under a Sales umbrella. I had always wanted to follow the path of an HR professional, may it be recruiting, organizational design, compensation, employee relations, training – all of which fell under the umbrella of HR. I arrived at Pythian because I was contacted by a previous colleague with an opportunity that I could not resist. Finally, I will get a chance to get my hands dirty and work on my true passion – Human Resourcing (HR). Contrary to some beliefs, HR professionals are not “paper pushers”, administrators or strictly recruiters but in fact we are pieces of the strategic puzzle in any organization. The harsh reality though is that much of the world still believes that we are administrators. So, for me I really wanted to join an organization that leverages HR as a strategic partner for the organization.

What Pythian offered me was the “3 Pillars of Growth” – Sales, Service Delivery and HR. These 3 ‘pillars’ are crucial in helping grow a business. This was further solidified for me by the people I met, the new/ existing plans and programs within HR, and the focus on culture Pythian provided for the growth of the business. This was a great opportunity for me to grow as a person and my career, and be part of a strategic team.

So I arrive at Pythian and was excited to learn the things I have always wanted to learn while providing the expertise I already possessed. In fact, I was delegated as the lead on a major, high profile project within the first week. However, shortly after doubt crept in as I found myself feeling like I could barely tread water (ask my wife, I’m not a very strong swimmer). Here I was absorbing so much information about the company, policies, procedures and now I have to figure out how to role out a project while I was still learning about the internal processes (and performing my main responsibility of recruiting). I rapidly felt like I was taking on too much water and started drowning. I started to loose confidence in my abilities and doubted the decision to take on this roll. I had several discussions with my work colleagues and my peers about my concerns. However, the one discussion that really changed my mind set was a conversation with my wife, she told me – “You worked too hard for this, don’t let yourself be your worst enemy.” There it was, clear as day! It was all my own insecurities, my own self-doubts, myself talking me out of this equation. So, I worked hard to observe and utilize the tools I had been provided throughout my career and at Pythian.

Once I was more open, I quickly noticed that Pythian really believes in their employees. They really empower their employees to do well and have a company culture to nurture their growth. They are always to open to new ideas and are always willing to take on risks with you. In the event that your new idea does not work, they still support you and move on to other ideas without pointing fingers or seeing you as a failure. Pythian’s unwritten culture is to make sure their employees don’t fail. Being in HR here, I was able to observe these facts are not just in our department but it is well exemplified in all business units across Pythian, starting from the CEO and Founder, to the Senior Executive team, to the Leadership team, and so on. I also had the opportunity to address my concerns to my own colleagues and manager and they were nothing but supportive. They provided immediate feedback and were able to provide relatable experiences with me.

Today, I feel like I am a much better swimmer. With the support I was provided by my colleagues, my managers, and the company my confidence in my abilities have grown and I know I have and will continue to add value to the organization. I feel I have met the expectations of the people who hired me, and I know I have made the right decision in joining this team.

This was my own experience of the first 6 months at Pythian. I hope that others have had similar or will have similar experiences as I have had (although I hope they are better swimmers). It has been tough but I’ve enjoyed every second of it. Life is a journey after all!

As Exadata Storage Indexes (SI) are purely memory only structures located on the Exadata storage servers, care needs to be taken in how much memory they can potentially consume. As a result, there is a limit of 8 columns (or 8 SIs) that can be defined for a given 1M storage region at any point […]

Links to useful information on preparing for Oracle Certification.

1.  Youtube "HOW TO : Preparing For Your Oracle Certification"  by OracleCertification

2.  Youtube "Oracle Certification Practice Exams"  by OracleCertification

3.  Certification Blog "Oracle Certification"  by Oracle Certification Program

4.  Forum "Certification"  by Oracle Forums

5.  WebSites
a.  "Oracle Certification Prep"  by Matthew Morris
b.  "MindHub"  by PearsonVue

6.  Test Software Vendors
a.   SelfTestSoftware
b.   Transcender
c.   UCertify

.
.
.