Copying only schema definition to another schema

Here is the command to copy only the schema definition from one schema to another schema.

First export the schema definition from user tfr_rep to empty_schema.dmp file

exp tfr_rep/welcome@datagen1-ajaved:1521/tfrdb file=empty_schema.dmp log=export.log rows=n constraints=n owner=tfr_rep

Then copy the dump file to ahsan_tfr user from tfr_rep user as follows.

imp userid=ahsan_tfr/welcome@tfindiadbzone:1521/tfindia file=empty_schema.dmp log=import.log rows=n fromuser=tfr_rep touser=ahsan_tfr

NOTE: Names of tablespaces on both the schema should be same, otherwise import will fail indicating oracle error

Sphere: Related Content

Materialized View Refresh Status

To know the materialized view refresh status along with their refresh time, you can issue following query to the database.

SELECT mview_name, refresh_mode, refresh_method, last_refresh_type, last_refresh_date FROM user_mviews;

Also to know the materialized view logs present in schema of a user, use this query

SELECT log_owner, master, log_table FROM user_mview_logs;

Sphere: Related Content

Script to create multiple startup screens in UNIX

Here is the scriipt that creates few startup screens on window.

Save this file in your home directory as MYSCREEN

execute it using screen -c {path to home}/MYSCREEN

startup_message off
vbell off
caption always "%{= bb}%{+b w}%n %h %=%t %c"
hardstatus alwayslastline "%-Lw%{= BW}%50>%n%f* %t%{-}%+Lw%<"
activity "Activity in %t(%n)"
shell -/bin/bash
screen -t SCREEN1
screen -t SCREEN2
screen -t SCREEN3
screen -t SCREEN4

This will create 4 screens named as SCREEN1,SCREEN2,SCREEN3 and SCREEN4 on your window

Sphere: Related Content

Script to export/import oracle schema

Here is the bash script that does the job of export and import of an oracle schema to another schema.

Save this file as EXPORT_IMPORT.

For exporting the schema type
EXPORT_IMPORT EXP

This file generates the schema dump in exp_database.dmp file and it also makes log of export process in exp_database.log file.

For importing the same exported exp_database.dmp dump file to another schema type
EXPORT_IMPORT IMP

if [ $# -lt 1 ]; then
echo "First argument to this file should be either EXP or IMP."
exit
fi
if [ "$1" = "EXP" ]; then
if [ $# != 2 ]; then
echo "No of arguments to this file should be two."
echo "USAGE: EXPORT_IMPORT EXP "
exit
fi
exp "$2" FILE=exp_database.dmp COMPRESS=N CONSISTENT=N CONSTRAINTS=Y GRANTS=Y INDEXES=Y RECORD=Y ROWS=Y Feedback=1000 LOG=exp_database.log
fi
if [ "$1" = "IMP" ]; then
if [ $# != 3 ]; then
echo "No of arguments to this file should be three."
echo "USAGE: EXPORT_IMPORT IMP "
exit
fi
from_user=`echo "$2" | cut -f1 -d'/'`
to_user=`echo "$3" | cut -f1 -d'/'`
imp userid="$3" fromuser="$from_user" touser="$to_user" file=exp_database.dmp buffer=64000 commit=y
fi

Sphere: Related Content

Know all users on an Oracle database

To know all users on Oracle database, issue following command on sqlplus window

select * from all_users

Sphere: Related Content

Create a new schema in Oracle

Here we will first create a tablespace and then we will create a user for that tablespace.

create tablespace ahsan
add datafile 'e:\ahsan.dbf' size 100 m;

create user ahsan identified by welcome
default tablespace ahsan temporary tablespace temp;

grant create session, connect, resource to ahsan;

Hence now you are ready with the new tablespace and a new user that belongs to the ahsan tablespace;

Sphere: Related Content

Installing oracle 10g database on parallels desktop

Recently, I switched to Mac from Windows machine. I had a challenge to make Mac development environment as similar to Windows development environment. Being a java developer I usually use Oracle database. So I tried to install it first on my mac, then I found somehow that Oracle does not support my mac version. Then I had to install Parallels Desktop to make it work.
I proceeded this way

1. Install Parallels Desktop, You can find how to do it on parallels support center.

2. Install Oracle 10g database on Parallels Desktop, It is smooth, so no problem.

3. Steps to install InstantClient and Sqlplus package on mac to access Parallel's oracle DB.

4. Download InstantClient and SqlPlus packages from Oracle download site.

5. Extract these two packages to a single directory like I chose /Applications/OracleClient

6. Copy tnsnames.ora file from parallels oracle installation to the Mac's /Applications/OracleClient directory you chose for installing InstantClient and SqlPlus.

7. Modify your .bash_profile to indicate these entries

PATH=/Applications/OracleClient:${PATH}
export PATH
ORACLE_HOME=/Applications/OracleClient
export ORACLE_HOME
DYLD_LIBRARY_PATH=/Applications/OracleClient
export DYLD_LIBRARY_PATH
TNS_ADMIN=/Applications/OracleClient
export TNS_ADMIN

8. Check for the IP of the Parallel's Desktop machine and note it.

9. Check for the IP of the Mac machine and note it.

10. open vi editor and edit /etc/hosts file for Mac machine to reflect the IP obtained from parallels desktop machine(Step 7)

192.168.128.82 ahsan-javed.mydomain.com
(Note:ahsan-javed.mydomain.com is the machine name that was given in my office network.)

11. open notepad and edit C:/Windows/System32/drivers/etc/hosts file for Parallels Desktop to reflect the IP obtained from Mac machine (step 8)

192.168.128.132 ahsanjaved
(Note: ahsanjaved is the machine name I assigned to parallels desktop while installing Windows XP on it.)

12. Go to Windows Control Center and disable firewall/enable 1521,1158 and 5506 ports for communication between Parallels Oracle and Mac machine.

13. Now go to shell and type sqlplus. it should ask you for username and password. Here you go connected.

Sphere: Related Content

Oracle cursors parameters

To view open cursors on oracle

No of Open cursors on Oracle(Admin permission is required)
select count(*) from v$open_cursor;

To see open_cursors parameter on oracle db.
show parameter open_cursors;

To set open_cursors parameter on oracle db.

alter system set open_cursors=700 scope=memory;

To see the sql query associated with cursor

select sid, sql_text from v$Open_cursor order by sid, sql_text;

To select number of open cursors, machine name, operating system and user name executing the query, run the following

SELECT v.value as numopencursors ,s.machine ,s.osuser,s.username
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 3 and v.sid = s.sid ;

Sphere: Related Content

Rebuild unusable index on a column of a table in Oracle schema

To rebuild unusable indexes on Oracle schema we can use this simple script.
This script first checks for all unusable indexes in the entire schema and then rebuild them to VALID state.

set serverout on
set head on
set echo on
declare
str varchar2(500);
begin for i in (select index_name,status from user_indexes where Status='UNUSABLE')
loop
str:='alter index ' || i.index_name || ' rebuild unrecoverable' ;
execute immediate str;
dbms_output.put_line('rebuilt index ' || i.index_name);
end loop;
end;
/

Sphere: Related Content

Microsoft Tech News Aggregator

Microsoft has recently launched its tech news aggregator platform called /Web. It is a platform for getting all technology related news in a consolidated manner. They allow contents from all other platforms as well. This site is beautifully designed into 6 sections where you can find relevant contents.

Inspiration
Methods
Business
Events
Blogs
Products

You can subscribe to the all articles at this link.

Sphere: Related Content

Sum of nth column in a file using awk

Today I had to find sum of nth column entries from one of my csv files. I just googled and found a nice way of doing it using awk.
Suppose my input csv file is named as test.out and I have to find sum of all nth column entries of it.

I just created an awk script file named test.awk for setting the FS variable and providing the summation logic to awk.

#test.awk
BEGIN {FS=","}
{ sum+=$COL}
END { print sum }

Then every thing is easy

Use the awk command as follows with input file test.out. Replace 8 with any column index you want.

awk -f test.awk COL=8 < test.out

Sphere: Related Content

Google SMS Channel

Google India has released a new service called Google SMS channel. This service lets you connect to all kind of news services, blog updates, cricket score, rss reader sync features at one single stop. While you can create any public channel, you can also create private channels for your family members and frieds. All you have to go to http://labs.google.co.in/smschannels link and create a nick name for you and enter your mobile no along with the name of the channel. Once created your friends and family member can subscribe to this channel and can send the messages to the channel without paying anything to the carrier services. You can also subscribe to this channel using web or mobile. While using mobile sign up you will have to give your mobile no. to proceed further.

SMS Commands
Almost everything that you can do on the website can be done over SMS. Detailed in the table below is the list of SMS commands and what they do.

All you have to do is to send SMS to 9870807070 with following content


on : Start receiving messages posted on channel

off : Stop receiving messages posted on channel

delete : Delete a channel that you created

send : Send message to channel

Sphere: Related Content

Oracle table partitions learning

Today, I was perplexed with a problem related to a partitioned table in oracle. My concern was to determine when I drop a partition of a table what happens the indexes on that table. I figured it out like this.

I created a partitioned test table as follows

CREATE TABLE TEST (
COLUMN1 NUMBER(9),
COLUMN2 NUMBER(9),
)
PARTITION BY LIST (COLUMN1)
(PARTITION LOW_PART VALUES (-1)
);

I created local bitmap indexes on COLUMN1 column of this table using a stored procedure

EXEC CREATE_BITMAP_INDEXES('TEST', 'COLUMN1');

This procedure created a local bitmap index IX_TEST_COLUMN1 on this table.
I then queried oracle about the state of bitmap indexes just created by this query

SELECT INDEX_NAME, STATUS FROM USER_IND_PARTITIONS WHERE PARTITION_NAME = 'LOW_PART';

SQL>
INDEX_NAME STATUS
--------------------- ---------------------
IX_TEST_COLUMN1 USABLE

Hence the created bitmap index is in usable state just after creation.
Now I inserted lots of data into this table from another staging table

INSERT /*+ APPEND */ INTO TEST
(COLUMN1, COLUMN2)
(SELECT COLUMN1, COLUMN2 FROM TEST_STAGING);
COMMIT;

Again I queried oracle about the state of local bitmap index.

SELECT INDEX_NAME, STATUS FROM USER_IND_PARTITIONS WHERE PARTITION_NAME = 'LOW_PART';

SQL>
INDEX_NAME STATUS
--------------------- ---------------------
IX_TEST_COLUMN1 USABLE

Again this index is in usable state. Hence local indexes status does not change upon insertion of data.
Now I tried to be tricky. I just created a new partition named as HIGH_PART on the table TEST.

ALTER TABLE TEST ADD PARTITION HIGH_PART VALUES (1000);

I then again queried oracle about the state of bitmap indexes

SELECT INDEX_NAME, STATUS FROM USER_IND_PARTITIONS;

SQL>
INDEX_NAME STATUS
--------------------- ---------------------
IX_TEST_COLUMN1 USABLE

Hence the created bitmap index is in usable state even after new partition addition.

Now I dropped the default partition as follows

ALTER TABLE TEST DROP PARTITION LOW_PART;

I then again queried oracle about the state of bitmap indexes

SELECT INDEX_NAME, STATUS FROM USER_IND_PARTITIONS;

SQL>
INDEX_NAME STATUS
--------------------- ---------------------
IX_TEST_COLUMN1 USABLE

Hence it is verified that local bitmap indexes on a partitioned table does not become unusable on
1. insertion of data
2. addition of new partition(When you add a partition, actually, a new partition is created for the local bitmap index as well)
3. drop of a partition (When you drop a partition, actually, the corresponding partition from the local bitmap index is dropped as well)

So when and which kind of indexes become unusable.
If you have global indexes on a partitioned table, it will become unusable when
1. you are trying to drop a partition.
2. you are splitting a partition to two new partitions
3. you are moving partitions

In that cases you will have to explicitly rebuild those global indexes as follows.

ALTER INDEX G_IX_TEST_COLUMN1 REBUILD PARTITION HIGH_PART;

Here it is assumed that G_IX_TEST_COLUMN1 is a global index on HIGH_PART partition of TEST table.

Sphere: Related Content

pagesize in oracle..

Recently I came across a problem of viewing the oracle sql queries results using sqlplus. I selected for this query
"select * from mytable"
Now my table contains 3000 rows. The result contains header breaks repeated after every 14 lines. so it looked very cluttered. I wanted to have it in nice format without any headers repeated. I just googled it and found a very nice command "pagesize"
I used it as follows
set pagesize 3000.
It sets the no of records to be shown at a time on sqlplus pompt without showing headers break. so I was now able to run my same query and it ran like charm.
Voila....no more cluttered line breaks...:)

Sphere: Related Content

Google releases Ad Manager

Google has launched a new product from their ad management suite, called Ad Manager. This free and currently beta service is a framework for webmasters, helping them to sell their ad space. It sounds like an interesting offering for all those who directly sell their ad space on some of their sites. Google in return would then make money simply due to the chance that you will (completely optionally) accept their AdSense ads to show whenever you don’t have advertisers yourself.

Google Ad Manager seems to have been in development at Google at least since late 2006.
If you have an AdSense account, you can sign in to Ad Manager today. If not, apply for an AdSense account now. A Google AdSense account is a technical requirement for creating an Ad Manager account.

Ad Manager can help you sell, schedule, deliver, and measure both directly-sold and network-based inventory. It offers an intuitive and simple user interface, Google serving speed and reliability, and significant cost savings. Best of all, Ad Manager can be optionally integrated with Google AdSense to offer you an automated way to maximize the revenue of your unsold and network-managed inventory.

Sphere: Related Content

Google Suggest graduates from Labs

Today, while searching for java I found that google.com now offers google suggest feature on its home page. It means that Google suggest feature has been now available to google site after graduating from google labs. The Google Suggest feature originally started as a 20% project in 2004, and has since expanded to Google Labs, Toolbar, Firefox search box, Maps and Web Search for select countries, the iPhone and BlackBerry, YouTube, and now Google.com.

As you type into the search box, Google Suggest guesses what you're typing and offers suggestions in real time. This is similar to Google's "Did you mean?" feature that offers alternative spellings for your query after you search, except that it works in real time. For example, if you type "bass," Google Suggest might offer a list of refinements that include "bass fishing" or "bass guitar." Similarly, if you type in only part of a word, like "prog," Google Suggest might offer you refinements like "programming," "programming languages," "progesterone," or "progressive." You can choose one by scrolling up or down the list with the arrow keys or mouse.

Sphere: Related Content

Google Translate OneBox

Google has a new tool for quick translations.
All you need to have to do is just search for "translate", followed by a word or an expression and the optional "into English". For example, you can search for [translate désormais] and Google shows the entry from an automatically-generated bilingual dictionary.






Unfortunately, you can't use the search box as a shortcut for Google Translate because full texts aren't yet translated. The bilingual dictionary is only available for the following language pairs: English <-> French/Italian/Spanish/Portuguese/German/Russian/Chinese/Korean/Hindi. It's interesting that Google shows results from Google Image Search next to the translations.

Sphere: Related Content

sending multiple attachments using Unix mailx command

Hi, ever tried to send mail from unix command prompt that contains multiple files as attachments, here is how you can do it in just a single command.

(uuencode file1.oldname file1.newname; uuencode file2.oldname file2.newname;
uuencode file3.oldname file3.newname; uuencode file4.oldname file4.newname) |
mailx -s "Subject of the mail" xyz@abc.com

Above snippets sends the file1, file2, file3 and file4 to the xyz@abc.com as attachments in a mail with a subject "Subject of the mail".

I have used a uuencode command that takes files as input and encode them over the network. The file1.oldname appears as file1.newname to the receiver when he/she opens the attachment.

You can send single file as an attachment as follows

mail -s "Subject of the mail" xyz@abc.com < file1

Sphere: Related Content

Google Releases AdPlanner

Google has recently launched a free media planning tool called AdPlanner.
It is supposed to be competitor tool of comscore's Media Metrix or Neilson's @Plan. This tool is invitation only and you have to verify your identity to google before signing up. It has some basic reports that shows you data for UV’s, Reach, Page Views and adSense related informations in case the sites contain adSense adverts. It also contains target audience feature but that is specific to US only. I think they will be enhancing it in future. As opposed to AtPlan and Media Metrix, Google's AdPlanner does not support mixing of audience data based on different demographic information. They support a higher no of demographic data as well. Also AdPlanner does not give any input on how the sites outside their own network will be catered out, especially in case if they are good target for advertising.

Sphere: Related Content

Google Releases a new Beta Knol

A few months ago we announced that we were testing a new product called Knol. Knols are authoritative articles about specific topics, written by people who know about those subjects. Today, we're making Knol available to everyone.

The web contains vast amounts of information, but not everything worth knowing is on the web. An enormous amount of information resides in people's heads: millions of people know useful things and billions more could benefit from that knowledge. Knol will encourage these people to contribute their knowledge online and make it accessible to everyone.

The key principle behind Knol is authorship. Every knol will have an author (or group of authors) who put their name behind their content. It's their knol, their voice, their opinion. We expect that there will be multiple knols on the same subject, and we think that is good.

With Knol, we are introducing a new method for authors to work together that we call "moderated collaboration." With this feature, any reader can make suggested edits to a knol which the author may then choose to accept, reject, or modify before these contributions become visible to the public. This allows authors to accept suggestions from everyone in the world while remaining in control of their content. After all, their name is associated with it!

Knols include strong community tools which allow for many modes of interaction between readers and authors. People can submit comments, rate, or write a review of a knol. At the discretion of the author, a knol may include ads from our AdSense program. If an author chooses to include ads, Google will provide the author with a revenue share from the proceeds of those ad placements.

We are happy to announce an agreement with the New Yorker magazine which allows any author to add one cartoon per knol from the New Yorker's extensive cartoon repository. Cartoons are an effective (and fun) way to make your point, even on the most serious topics.

Everyone knows something. See what people are writing about, then tell the world what you know: knol.google.com

Sphere: Related Content