Saturday, July 11, 2009

ORA-39165: Schema SYS was not found ORA-39166, ORA-31655

Problem Description
This is a variant of error described in ORA-39166: Object was not found, SYS tables can't be exported. The ORA-39166 throws if you want to take data pump export of SYS objects using SYS user. And ORA-39165 throws if you want to take data pump export of SYS objects as a non-SYS user.

With a simple example problem is demonstrated here.

SQL> conn / as sysdba
Connected.
SQL> create table database_10g(col1 number);

Table created.

SQL> insert into database_10g values(23);

1 row created.

SQL> commit;

Commit complete.

SQL> host E:\oracle\product\10.2.0\db_2\BIN\expdp userid=arju/a tables=sys.database_10g dumpfile=sys_table_test.dmp

Export: Release 10.2.0.1.0 - Production on Saturday, 11 July, 2009 18:30:26

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "ARJU"."SYS_EXPORT_TABLE_01": userid=arju/******** tables=sys.database_10g dumpfile=sys_table_test.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39165: Schema SYS was not found.
ORA-39166: Object DATABASE_10G was not found.
ORA-31655: no data or metadata objects selected for job
Job "ARJU"."SYS_EXPORT_TABLE_01" completed with 3 error(s) at 18:30:36

It says both schema SYS and object table does not exist. But actually both are existed. Here is the proof.
SQL> conn arju/a
Connected.
SQL> desc sys.database_10g
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER

SQL> select * from sys.database_10g ;

COL1
----------
23

SQL> select table_name, owner from dba_tables where table_name='DATABASE_10G';

TABLE_NAME OWNER
------------------------------ ------------------------------
DATABASE_10G SYS

Cause of the Problem
There is a restriction imposed in data pump export that SYS tables, objects are not exported even with full export option. Whenever we export by schemas=sys then role grants are exported but no data. Data pump does not allow to export system schemas like SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP and WMSYS in any mode.

Solution of the Problem
1)Use original export instead of data pump export to export SYS objects/schemas.

2)First using create table as select transfer SYS objects into non-restrictive schema and using data pump export data/tables from non-restrictive schema.

So the conclusion is the SYS schema, SYS tables cannot be used as a source schema for data pump export jobs.

Related Documents
Posted by Arju at 4:28 AM 1 comments Labels:
Reactions: 

In 11g data pump export schemas=sys do export only role grants

In the post http://arjudba.blogspot.com/2009/07/ora-39165-schema-sys-was-not-found-ora.html and http://arjudba.blogspot.com/2009/07/ora-39166-object-was-not-found-sys.html it is shown that the SYS schema objects or tables cannot be used as a source schema for data pump export jobs.

In this post it is shown if we specify schemas=sys option while data pump export then what it actually does. Of course no tables, indexes, constraint, procedures, packages, triggers are exported. Only role grant are exported.

SQL> host expdp userid=\"/ as sysdba\" dumpfile=sys_test_dump.dmp schemas=sys

Export: Release 11.1.0.6.0 - Production on Saturday, 11 July, 2009 18:17:04

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": userid="/******** AS SYSDBA" dumpfile=sys_test_dump.dmp schemas=sys
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
D:\APP\ARJU\ADMIN\ARJU\DPDUMP\SYS_TEST_DUMP.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:17:23

Let's see the contents inside dumpfile.
SQL> host impdp userid=\"/ as sysdba\" dumpfile=sys_test_dump.dmp sqlfile=inside_dump.txt

Import: Release 11.1.0.6.0 - Production on Saturday, 11 July, 2009 18:18:29

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01": userid="/******** AS SYSDBA" dumpfile=sys_test_dump.dmp sqlfile=inside_dump.txt
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 18:18:33

The contents inside_dump.txt is as follows.
-- CONNECT SYS
ALTER SESSION SET EDITION = "ORA$BASE";
-- new object type path: SCHEMA_EXPORT/ROLE_GRANT
-- CONNECT SYSTEM
ALTER SESSION SET EDITION = "ORA$BASE";
GRANT "CONNECT" TO "SYS" WITH ADMIN OPTION;

GRANT "DBA" TO "SYS" WITH ADMIN OPTION;

GRANT "SELECT_CATALOG_ROLE" TO "SYS" WITH ADMIN OPTION;

GRANT "EXECUTE_CATALOG_ROLE" TO "SYS" WITH ADMIN OPTION;

GRANT "DELETE_CATALOG_ROLE" TO "SYS" WITH ADMIN OPTION;

GRANT "EXP_FULL_DATABASE" TO "SYS" WITH ADMIN OPTION;

GRANT "IMP_FULL_DATABASE" TO "SYS" WITH ADMIN OPTION;

GRANT "LOGSTDBY_ADMINISTRATOR" TO "SYS" WITH ADMIN OPTION;

GRANT "AQ_ADMINISTRATOR_ROLE" TO "SYS" WITH ADMIN OPTION;

GRANT "AQ_USER_ROLE" TO "SYS" WITH ADMIN OPTION;

GRANT "DATAPUMP_EXP_FULL_DATABASE" TO "SYS" WITH ADMIN OPTION;

GRANT "DATAPUMP_IMP_FULL_DATABASE" TO "SYS" WITH ADMIN OPTION;

GRANT "GATHER_SYSTEM_STATISTICS" TO "SYS" WITH ADMIN OPTION;

GRANT "RECOVERY_CATALOG_OWNER" TO "SYS" WITH ADMIN OPTION;

GRANT "SCHEDULER_ADMIN" TO "SYS" WITH ADMIN OPTION;

GRANT "HS_ADMIN_ROLE" TO "SYS" WITH ADMIN OPTION;

GRANT "OEM_ADVISOR" TO "SYS" WITH ADMIN OPTION;

GRANT "OEM_MONITOR" TO "SYS" WITH ADMIN OPTION;

GRANT "JAVAUSERPRIV" TO "SYS" WITH ADMIN OPTION;

GRANT "JAVAIDPRIV" TO "SYS" WITH ADMIN OPTION;

GRANT "JAVASYSPRIV" TO "SYS" WITH ADMIN OPTION;

GRANT "JAVADEBUGPRIV" TO "SYS" WITH ADMIN OPTION;

GRANT "EJBCLIENT" TO "SYS" WITH ADMIN OPTION;

GRANT "JMXSERVER" TO "SYS" WITH ADMIN OPTION;

GRANT "JAVA_ADMIN" TO "SYS" WITH ADMIN OPTION;

GRANT "JAVA_DEPLOY" TO "SYS" WITH ADMIN OPTION;

GRANT "CTXAPP" TO "SYS" WITH ADMIN OPTION;

GRANT "XDBADMIN" TO "SYS" WITH ADMIN OPTION;

GRANT "XDB_SET_INVOKER" TO "SYS" WITH ADMIN OPTION;

GRANT "AUTHENTICATEDUSER" TO "SYS" WITH ADMIN OPTION;

GRANT "XDB_WEBSERVICES" TO "SYS" WITH ADMIN OPTION;

GRANT "XDB_WEBSERVICES_WITH_PUBLIC" TO "SYS" WITH ADMIN OPTION;

GRANT "XDB_WEBSERVICES_OVER_HTTP" TO "SYS" WITH ADMIN OPTION;

GRANT "ORDADMIN" TO "SYS" WITH ADMIN OPTION;

GRANT "OLAPI_TRACE_USER" TO "SYS" WITH ADMIN OPTION;

GRANT "OLAP_XS_ADMIN" TO "SYS" WITH ADMIN OPTION;

GRANT "OLAP_DBA" TO "SYS" WITH ADMIN OPTION;

GRANT "CWM_USER" TO "SYS" WITH ADMIN OPTION;

GRANT "OLAP_USER" TO "SYS" WITH ADMIN OPTION;

GRANT "SPATIAL_WFS_ADMIN" TO "SYS" WITH ADMIN OPTION;

GRANT "WFS_USR_ROLE" TO "SYS" WITH ADMIN OPTION;

GRANT "SPATIAL_CSW_ADMIN" TO "SYS" WITH ADMIN OPTION;

GRANT "CSW_USR_ROLE" TO "SYS" WITH ADMIN OPTION;

GRANT "WKUSER" TO "SYS" WITH ADMIN OPTION;

GRANT "MGMT_USER" TO "SYS" WITH ADMIN OPTION;

GRANT "OWB$CLIENT" TO "SYS" WITH ADMIN OPTION;

GRANT "OWB_DESIGNCENTER_VIEW" TO "SYS" WITH ADMIN OPTION;

GRANT "OWB_USER" TO "SYS" WITH ADMIN OPTION;
Related Documents
Posted by Arju at 4:17 AM 0 comments Labels:
Reactions: 

ORA-39166: Object was not found, SYS tables can't be exported

In case of original export we could easily export the tables those were inside under SYS schema.

But whenever you try to export a table from sys schema using expdp it fails with ORA-39166: Object was not found. With a simple example the scenario is demonstrated below.

1)Log on as sysdba.
SQL> conn / as sysdba
Connected.

2)Create a test table and insert data into it.
SQL> create table test_export_for_sys(value1 number);

Table created.

SQL> insert into test_export_for_sys values(55);

1 row created.

SQL> commit;

Commit complete.

3)Try to take a data pump export of this table.
SQL> host expdp userid=\"/ as sysdba\" dumpfile=sys_test.dmp tables=test_export_for_sys

Export: Release 11.1.0.6.0 - Production on Saturday, 11 July, 2009 15:01:39

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": userid="/******** AS SYSDBA" dumpfile=sys_test.dmp tables=test_export_for_sys
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39166: Object TEST_EXPORT_FOR_SYS was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at 15:01:45

But in the database there exists test_export_for_sys table,

SQL> desc test_export_for_sys
Name Null? Type
----------------------------------------- -------- -------------
VALUE1 NUMBER

SQL> select * from test_export_for_sys;

VALUE1
----------
55

SQL> show user;
USER is "SYS"

If you try to export schema also no tables are exported.
SQL> host expdp userid=\"/ as sysdba\" dumpfile=sys_test_schema.dmp schemas=sys

Export: Release 11.1.0.6.0 - Production on Saturday, 11 July, 2009 19:14:56

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": userid="/******** AS SYSDBA" dumpfile=sys_test_schema.dmp schemas=sys
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
D:\APP\ARJU\ADMIN\ARJU\DPDUMP\SYS_TEST_SCHEMA.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:16:12


But this is not the fact in case of original export. Here is the original export output,

SQL> host exp userid=\"/ as sysdba\" file=sys_test.dmp tables=test_export_for_sys

Export: Release 11.1.0.6.0 - Production on Sat Jul 11 17:42:29 2009

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table TEST_EXPORT_FOR_SYS 1 rows exported
Export terminated successfully without warnings.

Solution of the Problem
This is the restriction imposed in oracle data pump. A number of system schemas tables cannot be exported because they are not user schemas, they contain Oracle-managed data and metadata. As in every schemas there by default system schemas exist. Data pump utility designed for transferring data, not the database; so not the system schemas. However if you want to export sys tables like SYS.AUD$ then first transfer that table into non-restricted schema and export the table from non restricted schema.

Related Documents
Posted by Arju at 1:04 AM 0 comments Labels:
Reactions: 

Friday, July 10, 2009

Import data into an existing table-TABLE_EXISTS_ACTION(ORA-39151)

In many cases we need to import data into in existing table. A common example is you take a data pump export, truncate the table, then table undergoes for normal operation. Suddenly your manager ask to get back old data while running in tact current operation as well as leave current data in place. Just you need to append data into an existing table.

Both original export and data pump export can be used to append data into an existing table but data pump import offer flexible option.

Below is an example about the happenings to import a table which already exist in the database.

SQL> $impdp arju/a

Import: Release 11.1.0.6.0 - Production on Friday, 10 July, 2009 23:07:08

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ARJU"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ARJU"."SYS_IMPORT_FULL_01": arju/********
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "ARJU"."TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ARJU"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 23:07:13

In data pump import the parameter TABLE_EXISTS_ACTION help to do the job. The default value of this parameter is SKIP which means if table to be imported already existed in the database table will be skipped and data not to be imported and continue processing next object. However if in your import job if CONTENT=DATA_ONLY is specified, the default is APPEND, and then data will be appended into existing table.

TABLE_EXISTS_ACTION can have following values.

1)SKIP: It leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

2)APPEND: This option loads rows from the source and leaves existing rows unchanged. This is a default option is CONTENT=DATA_ONLY is specified.

3)TRUNCATE: This option deletes existing rows and then loads rows from the source.

4)REPLACE: This option drops the existing table in the database and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

Important Note
- If you use TRUNCATE or REPLACE, make sure that rows in the affected tables are not targets of any referential constraints.

- If you use SKIP, APPEND, or TRUNCATE then existing table dependent objects in the source, such as indexes, grants, triggers, and constraints, are ignored. In case of REPLACE, the dependent objects are dropped and again created from the source, if they were not explicitly or implicitly excluded (using EXCLUDE) and they exist in the source dump file or system.

- If you use APPEND or TRUNCATE, checks are made to ensure that rows from the source are compatible with the existing table prior to performing any action. If the existing table has active constraints and triggers, it is loaded using the external tables access method. If any row violates an active constraint, the load fails and no data is loaded. You can override this behavior by specifying DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS on the Import command line.

If you want data must be loaded but causes constraint voilations, you can disable constraints, import data, delete the rows which causes problems and then enable constraints.

- When you use APPEND, the data is always loaded into new space. So if you have any existing space available the space is not reused. So after the import operation, you may wish to compress your data after the load.

- TRUNCATE cannot be used on clustered tables or over network links.

In case of original import use ignore=y option to append data into an existing table. ignore=y causes rows to be imported into existing tables without any errors or messages being given.
Related Documents
Posted by Arju at 9:34 AM 0 comments Labels:
Reactions: 

Step by step to create a paypal donation button

Many one specially if you are a blogger or webmaster or own a site, you might show an interest to create a paypal donation button and integrate it inside your page so that you can accept online donation through paypal. I myself run this blog http://arjudba.blogspot.com and like to add a paypal donation button. I am sharing the steps so that you too can do it in your site.

Step by step procedure to create PayPal donation button.

1. Login your PayPal account. You will see "My Account". Click on the "Merchant Tools". Scroll down and click to "Website Payments Standard".

2.Click on "Create payment buttons". You will see "Create PayPal payment button" page. There is three steps. In,
Step 1: Choose button type and enter payment details
-Select Accept payment for from dropdown menu. You can select "Donations".

-You can select customized button, country, language, you have the option to use your own button image, currency.

-The important radio box is "Contribution amount". You have two options, 1)Donors enter their own contribution amount. 2)Donors contribute a fixed amount. If you select 2) the donator cannot donate more than or less than the specified amount.

-From the Merchant ID for purchase transactions radiobox it is better to choose Secure merchant account ID. If you choose a plain text e-mail address, it will be displayed in the button code. So, anyone, including spammers, can copy this address for their own use.

3.Step 2)Track inventory, profit & loss (optional)
and Step 3: Customize advanced features (optional)
both are optional and they are only available if you upgrade to a PayPal Business account.

4.Click "Create Button".

5.You will see a HTML code for the donation button. Copy it and paste it into your website. Done.

Here is my PayPal donation button. It is real! Click it to donate USD$1 to me, credit card accepted. :)







Advanced Techniques
Below is the code which accept a fixed amount 10$ for donation.
<form name="_xclick" action="https://www.paypal.com/cgi-bin/webscr" method="post">
<input type="hidden" name="cmd" value="_xclick">
<input type="hidden" name="business" value="aly.mir1@gmail.com">
<input type="hidden" name="item_name" value="Team In Training">
<input type="hidden" name="currency_code" value="USD">
<input type="hidden" name="amount" value="10.00">
<input type="image" src="http://www.paypal.com/en_US/i/btn/btn_donate_LG.gif" border="0" name="submit" alt="Make payments with PayPal - it's fast, free and secure!">
</form>

If you paste above code inside your site it will look like,









Related Documents
http://arjudba.blogspot.com/2009/07/draw-google-adsense-money-using-western.html
http://arjudba.blogspot.com/2009/06/debit-card-credit-card-atm-card-charge.html
Posted by Arju at 4:46 AM 0 comments Labels: ,
Reactions: 

Parameter comparison between oracle export and data pump

We know Oracle data pump is latest utility than oracle original export/import tool and also data pump comes with greater flexibility and more advantages. Those who are good at original export/import tool and new to oracle data pump, this post will help them specially to compare the differences between two.



Parameter Comparion between Oracle export/Import and data pump






















































































































Orginal Export Parameter Comparable Data pump Parameter
BUFFER In datapump equivalent parameter
is not needed.
COMPRESS In datapump equivalent parameter
is not needed
CONSISTENT In datapump FLASHBACK_SCN and
FLASHBACK_TIME do the job.
CONSTRAINTS EXCLUDE=CONSTRAINT
DIRECT Data pump automatically choose
direct path or external tables mode.
FEEDBACK STATUS
FILE DUMPFILE
FILESIZE FILESIZE
FLASHBACK_SCN FLASHBACK_SCN
FLASHBACK_TIME FLASHBACK_TIME
FULL FULL
GRANTS EXLUDE=GRANT
HELP HELP
INDEXES EXCLUDE=INDEX
LOG LOGFILE
OBJECT_CONSISTENT Equivalent parameter is not needed.
OWNER SCHEMAS
PARFILE PARFILE
QUERY QUERY
RECORDLENGTH Equivalent parameter is not needed.
RESUMABLE This function is automatically enabled
for user who has been granted EXP_FULL_DATABASE role.
RESUMABLE_NAME This function is automatically enabled
for user who has been granted EXP_FULL_DATABASE role.
RESUMABLE_TIMEOUT This function is automatically enabled
for user who has been granted EXP_FULL_DATABASE role.
ROWS=N CONTENT=METADATA_ONLY
ROWS=Y CONTENT=ALL
STATISTICS Not needed. Statistics are always
saved for tables.
TABLES TABLES
TABLESPACES It has TABLESPACES parameter but
behave differently than original export.


Related Documents
Posted by Arju at 12:37 AM 0 comments Labels:
Reactions: 

Thursday, July 9, 2009

Components of a USB flash drive

Components of a USA flash drive can be divided into two types.
1)Essential Components: Must exist in flash drive.
2)Optional/Good to have Components: Not essential component but it makes good to look and feel.

1)Essential Components:
a)A USB Connector: The USB connector is covered a cap and after removing cap it is connected to host computer. It is male type port and acts as an interface to transfer data.

b)USB Mass storage controller: It contains a small microcontroller with a small amount of on-chip ROM and RAM. If you use transparent flash drive cover then you can see it as the smaller black coloured portion.

c)NAND flash memory chip: It is used to store data. If you use transparent flash drive cover then you can see it as a bigger black coloured chip.

d)Crystal oscillator: It produces the device's main 12 MHz clock signal and controls the device's data output.

2) Optional Components:
a)Jumpers and test pins: It is a component to test flash drive during manufacturing.

b)LEDs: We can see small light in the flash drive while it is performing read/write. That part is lighted because of LEDs.

c)Write-protect switches: A switch by which we can control whether data can be written to flash drive. Switch on or off controls write to the flash drive. Very effect whenever you need plug flash drive to a computer(to read contents from the flash drive) that have viruses. :)

d)USB connector cover or cap: A cap which is connected to the USB connector in no operation state which reduces the risk of damage and prevents the ingress of fluff or other contaminants, and improves overall device appearance.

e)Unpopulated space: A space to which we can add another memory chip.

f)Key Chain: Some usb flash drive can contain a key chain which helps to facilitate transport.

g)Some flash drive also offer expandable storage.

After all, you can have these many features with a customizable design. You can get a Personalized USB Drive from flashdealer.com and can design your usb flash drive whatever you want.
Posted by Arju at 10:06 PM 1 comments Labels:
Reactions: 

Wednesday, July 8, 2009

Default location of Dump, Log and SQL file

As data pump is server based, all dump files, log files and SQL files are generated and accessed from /to server-based directory paths.

In simple term, Data pump directory object is an alias of an operating system directory. There must physically exist OS directory in order to effect corresponding oracle directory. Changes permission to oracle directory does not affect any changes to OS directory and in the same way changes permission to OS directory does not affect any oracle directory. In order to read/write any oracle directory, there must explicitly have read/write permission from corresponding OS directory.

As this post is related tolocation of dump, log and sql file in the following section the order of precedence to determine a file's location is discussed in case of Data Pump Export and Import.

1)If we specify a directory name with the parameter of LOGFILE or DUMPFILE or SQLFILE then the location specified by that directory object is used. It is very good to know that the directory object must be separated from the filename by a colon(:).
An example is given below.

SQL> create table test(a number);

Table created.

SQL> create directory c_drive as 'c:';

Directory created.

SQL> create directory d_drive as 'd:';

Directory created.

SQL> host expdp dumpfile=c_drive:test.dmp logfile=d_drive:test.log tables=test

Export: Release 11.1.0.6.0 - Production on Wednesday, 08 July, 2009 19:11:06

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: arju/a

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ARJU"."SYS_EXPORT_TABLE_01":  arju/******** dumpfile=c_drive:test.dmp logfile=d_drive:test.log tables=test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ARJU"."TEST":"P1"                          5.007 KB       1 rows
. . exported "ARJU"."TEST":"P2"                          5.007 KB       1 rows
. . exported "ARJU"."TEST":"P3"                          5.007 KB       1 rows
Master table "ARJU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_TABLE_01 is:
C:\TEST.DMP
Job "ARJU"."SYS_EXPORT_TABLE_01" successfully completed at 19:11:39

2)If the directory object is not specified for a file which means not included with DUMPFILE/LOGFILE/SQLFILE, then the directory object named by the DIRECTORY parameter is used.

So if your command is below then the dump file will represent c_drive directory which
indicates C: drive.

SQL> host expdp directory=c_drive tables=test

Export: Release 11.1.0.6.0 - Production on Wednesday, 08 July, 2009 22:04:09

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: arju/a

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ARJU"."SYS_EXPORT_TABLE_01":  arju/******** directory=c_drive tables=test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ARJU"."TEST":"P1"                          5.007 KB       1 rows
. . exported "ARJU"."TEST":"P2"                          5.007 KB       1 rows
. . exported "ARJU"."TEST":"P3"                          5.007 KB       1 rows
Master table "ARJU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_TABLE_01 is:
C:\EXPDAT.DMP
Job "ARJU"."SYS_EXPORT_TABLE_01" successfully completed at 22:04:50

3)If you don't provide any directory object with filename, and if no directory object was named by the DIRECTORY parameter, then the value of the environment variable DATA_PUMP_DIR is used.

Always remember that this environment variable is defined using operating system
commands on the client system where the Data Pump Export and Import utilities
are run. But this value assigned to this client-based environment variable must be the name of a server-based directory object. So you first need to create a directory in the server as a DBA user and then you need to setup in client environment variable.

Below is an exact example of this scenario.
Though in this example DATA_PUMP_DIR directory points to OS directory D:\app\Arju\admin\arju\dpdump\ but because of client environmental variable setting of DATA_PUMP_DIR to TEST(which is E: drive) create the dumpfile inside E:\ drive.
Never confuse with the name DATA_PUMP_DIR. Here there can be any directory name that reside in the database.

E:\>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jul 8 15:54:04 2009

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


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

SQL> select directory_path from dba_directories where directory_name='DATA_PUMP_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
D:\app\Arju\admin\arju\dpdump\

SQL> create directory test as 'E:';

Directory created.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

E:\>set DATA_PUMP_DIR=TEST


E:\>expdp schemas=arju
Export: Release 11.1.0.6.0 - Production on Wednesday, 08 July, 2009 15:54:54

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=arju
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 576 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "ARJU"."C1"                                 5.007 KB       1 rows
. . exported "ARJU"."P3"                                 5.007 KB       1 rows
. . exported "ARJU"."T"                                  5.023 KB       2 rows
. . exported "ARJU"."TEST":"P1"                          5.007 KB       1 rows
. . exported "ARJU"."TEST":"P2"                          5.007 KB       1 rows
. . exported "ARJU"."TEST":"P3"                          5.007 KB       1 rows
. . exported "ARJU"."TEST2"                              5.406 KB       1 rows
. . exported "ARJU"."TEST3"                              5.414 KB       2 rows
. . exported "ARJU"."T_LARGEST"                          5.046 KB       5 rows
. . exported "ARJU"."MASTER_TEMP"                            0 KB       0 rows
. . exported "ARJU"."MASTER_TEMP2"                           0 KB       0 rows
. . exported "ARJU"."MLOG$_MASTER_TEMP"                      0 KB       0 rows
. . exported "ARJU"."MV_MASTER"                              0 KB       0 rows
. . exported "ARJU"."T1"                                     0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
E:\EXPDAT.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:56:37


4)If none of the above three conditions yields a directory object, but you invoke data pump and you have permission on DATA_PUMP_DIR, then Data Pump attempts to use the value of the default server-based directory object, DATA_PUMP_DIR.

This DATA_PUMP_DIR directory is automatically created at database creation or when the database dictionary is upgraded.

If you don't have access to the DATA_PUMP_DIR directory object then you will get insufficient privilege error.

Related Documents
Posted by Arju at 7:46 AM 1 comments Labels:
Reactions: 

Different types of eye glasses.

A man or a woman usually use eye glasses for vision correction or to protect eyes from ultra-violate rays. I used to use eye glasses since I am 18. I use it for the vision correction. I could not see an object at a distant clearly since 18. The object at a distant seemed blurred to me, then I contacted an eye doctor, he suggested an eyeglass of power -1.5D. Eyeglasses also named as glasses or spectacles.

I used a normal styled eyeglasses at first. Generally a eye glass consists of two parts, one is eyeglass frames and another is lenses. Eye glass frames usually made of plastic or metal. Lenses are made of plastic or glass. At the age of 18 I used glass as lenses. But now I use plastic as eye lenses. Plastic has many advantages over glasses because of plastic have less weight, reduce the danger of breakage, better transmission of visible light, greater absorption of ultraviolet light etc. Because of these advantages of plastic over glasses I started to use plastic lenses.

Now a days wearing glasses become a fashion and glasses come with greater design. Based on the design of glasses we can categorize it into various types.

1)Full-Frame Glasses: It is fully rimmed style which means frames are fully encircled the lenses.

2)Three-piece rimless: Three-piece rimless glasses have no frame around the lenses, and the bridge and temples are mounted directly onto the lenses. Frames does not encircle the lenses unlike full-frame glasses. It is also called rimless glasses. As there is three pieces (two by side and one in middle that connect the lenses) of frames, so it is called three-pieces rimless glass.

3)Semi-rimless glasses: In case of semi-rimless glasses frames partially encircles the lenses (commonly the top portion and the bottom position becomes open).

4)Fashion glasses: Plastic frames with designs.

5)Wood like: The frames are made of advanced plastic materials which look like wooden materials. It is more durable.

There are many companies that offer Wide Selection of Eyeglasses like glassesshop.com. They can offer any customized styles, frames, lenses, in fact whatever you want your glasses to look like.

Related Documents
http://arjudba.blogspot.com/2009/06/typhoid-fever-symptoms-diagnosis.html
Posted by Arju at 12:38 AM 1 comments Labels:
Reactions: 

Tuesday, July 7, 2009

How to know a row of a table belong to which tablespace

If someone ask you a question about "how you will determine a table belong to which tablespace." The answer is easy, query from dba_segments/user_segments/all_segments. Like I want to know table T belongs to which tablespace. Then query like below.
SQL> select tablespace_name from dba_segments where segment_name='T';

TABLESPACE_NAME
------------------------------
USERS

So table T belong to USERS tablespace.

If we want to know a table belongs to which datafile we can also determine it by querying from dba_data_files and dba_extents as below.
SQL> select file_name from dba_data_files where file_id in
2 (select file_id from dba_extents where segment_name='T');

FILE_NAME
-----------------------------------------------------------------------
D:\APP\ARJU\ORADATA\ARJU\USERS01.DBF

But if someone asked you to determine about a row of a table resides on which tablespace then you might think once how to do it. Following is an example of how to do it.

1)Create a partition table named test.
SQL> create table test (col1 number)
2      partition by range (col1)
3      (
4      partition p1 values less than (100) tablespace users,
5      partition p2 values less than (200) tablespace users02,
6      partition p3 values less than (maxvalue) tablespace users03
7      )
8  /

Table created.

2)Insert some rows so that they span into multiple tablespaces.
SQL> insert into test values (1);

1 row created.

SQL> insert into test values (111);

1 row created.

SQL> insert into test values (1000);

1 row created.

3)Query to select row and corresponding tablespace.
SQL> select col1, tablespace_name
2      from test, dba_data_files
3      where dbms_rowid.rowid_to_absolute_fno(test.rowid,user,'TEST') = file_id
4      /

COL1 TABLESPACE_NAME
---------- ------------------------------
1 USERS
111 USERS02
1000 USERS03

4)If you were asked to determine the tablespace name which holds the value 1 and determine it's tablespace_name, file_name then query as,
SQL> set lines 100
SQL> col file_name for a50
SQL> select col1, tablespace_name, file_name
2          from test, dba_data_files
3          where dbms_rowid.rowid_to_absolute_fno(test.rowid,user,'TEST') = file_id
4          and col1=1;

COL1 TABLESPACE_NAME                FILE_NAME
---------- ------------------------------ -------------------------------------------
1 USERS                          D:\APP\ARJU\ORADATA\ARJU\USERS01.DBF

Related Documents
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
Posted by Arju at 7:14 PM 0 comments Labels:
Reactions: 

Sunday, July 5, 2009

How to add a site to google directory

Unlike yahoo directory, there is no hassle to submit your site to google directory. It is straigtforward and easy job to submit your site in google directory and of course it is free to do that.

Google directory contents are based on dmoz.org but it's contents are enhanced using it's own technology. So if you already have an entry in dmoz.org you don't need to look for further to add you site into google directory. You can check your site entry inside dmoz.org by opening dmoz.org and and type your site name then press submit button.

You can check whether your site is listed inside google directory by opening http://directory.google.com/ and then type your site name. For example I can check whether my site is listed inside google directory by following url. Check Arjudba.blogspot.com google directory. If row is returned with your site name then be sure that your site is listed inside google directory. But if the result looks like "Your search - "%s" - did not match any documents" then your site is not listed inside google directory and you may like to add you.

Go to http://directory.google.com/ and click Submit a Site at the bottom of the page and then follow the directions as it asked.

Related Documents

How to add a site to yahoo directory
Posted by Arju at 10:34 PM 0 comments Labels:
Reactions: 

How to add a site to yahoo directory

At first, let me know you why people add a site to a directory? It is because of increasing your site traffic. Web directories consists a list of category to which you can submit your site. The reason people submit a link for a web directory is simple. People want to search for links for their business requirements. Then instead of searching site they may go inside specific website categories/directories where you find those directories that are similar to your line of work. For example, if you are looking for computer and technology resources, then look for those website directories named with Computer/Technology. These also may have sub-directory/sub category. So you now have control to your specific needs. With aid of web directory you can narrow down your search so that you can get more accurate results.

There are many web directories in Internet world like, Google Web directory,Yahoo Web directory, Open directory etc.

Before entering into real thing, let me know you my feelings about adding a site to yahoo directory. I personally think that adding a site to yahoo directory does not help a site except that he had to pay some $$$. I don't believe that adding a site to a yahoo directory will bring traffic much and help for SEO. It is nothing but a name like "Hay, my site is listed in yahoo directory". Certainly that does not refer to quality of the site.

Anyway, still many one wants to add their site to yahoo directory and want to spend some time as well as money for it. This post will help you how to add a site to yahoo directory. Step by step it is discussed.

Step 01- Check whether your site is already listed in yahoo directory:
From the link http://help.yahoo.com/l/us/yahoo/directory/basics/steps/basics-22.html check if your site is already listed in yahoo directory. Inside the box type your site name and click Yahoo directory search. I believe that if your site is listed in search results then it is useless to add it to directory because crawler already identified your site. If you are very new in your site then your site may not display in search result. In that case you can think about it.

Step 02- Find an appropriate category in yahoo directory for you site:
If you committed to add your site to yahoo directory then it is important to find a category that mostly matches your site contents. Go to http://dir.yahoo.com/ and find an appropriate category that matches your site contents. For example you may browse to "Computers_and_Internet/News_and_Media/Computers_and_Technology_Blogs/" if you own a blog like my own.

Steo 03- Suggest a site:
On the top right hand corner you will see a link "Suggest a site". Click that link. You will see you have two options to select. One is 1)Yahoo Directory Submit and another is 2)Standard.

If you choose Yahoo Directory submit then there is a guaranteed and expedited consideration from Yahoo! that you site will be included in their Directory list within 7 business days. Also you need to pay $299 per year to Yahoo!.

If you choose Standard then there is no time when yahoo will list your site in their directory but it is free to submit your site to yahoo through standard way.

Related Documents
Posted by Arju at 9:54 PM 3 comments Labels:
Reactions: 

Different ways to open Office .docx documents

DOCX is the Word document which is the "Microsoft Office Open XML Format Document". This is the new upcoming format for Microsoft Office documents. This extension came with Microsoft Office 2007. It is a combination of XML architecture and ZIP compression for size reduction.

Microsoft introduced the .docx file format in its Office 2007 and wants it to replace the commonly used doc format. Any word document in Office 2007 can be saved as .docx file extension but as .docx extension is latest version, so older version like Office 2000, Office XP, Office 98 are not still aware of the .docx file extension. So these software can't open the .docx file. Though Microsoft promised that .docx file will be compatible to old Microsoft word but they are still their promises.

Now, as many users still using old Microsoft Office so many users can't open the .docx file document. So the main question for these users is of course how they would open a docx file to be able to read and work with it. The answer depends on the platform you are using. Below is some solutions based on platform you are using as well as solution of platform independent.

For Older Version of Microsoft Office:
If you are on windows machine and you have older version of Microsoft Office like Office XP, Office 2000, Office 98 then the best way is to install Microsoft compatibility pack for previous Office versions that adds .docx support in Microsoft Word. You can download the compatibility pack from http://office.microsoft.com/en-us/word/HA100444731033.aspx.

If you use Open office Software:
If you use open office software then you can use the Open XML Translator for Open Office to be able to read and edit docx files.

Online Solution:
There are many websites over Internet that do the conversion job and most of them do offer the job for free. You can upload your .docx file in that site and then you will get back .doc document. There are many docx converter sites for it.

A hack way:
This procedure worked perfectly for Microsoft Office 2007 beta version. But if it is not beta then it will not work. Still you can get an idea about the contents inside your .docx file. The idea is as .docx file is the combination of XML architecture and ZIP compression. So first save the .docx file as .zip and then extract it using any extract utility like unzip. If you used beta version then word folder you will get your expected word file. If you don't you can still have an idea to rename big sized .XML to .HTML and open that file in your browser.

Related Documents
Posted by Arju at 8:27 AM 0 comments Labels: , , ,
Reactions: 
Subscribe to: Posts (Atom)