extracting TABLES from SAP

[Former Member]

Hi,

IS it possible to extract a complete table (with its all row) in a .sql formate from the SAP System? if yes pls do let me know the T-code for the same.

Actually I want to extract some tables from the SAP and want these to be DIRECTLY use in some other database. if i wl be able 2 extract the info in .SQL Format. it will be essy for me to compile the same file at sqlplus. this wl save my time.

Abhishek

[Former Member · Accepted solution]

Hello Abhishek,

you can export a SAP table from an Oracle database and later import in to different database

You can find the full info. for this here:

http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1264656,00.html

Also you see the table contents of SAP database from SAP from T-Code se11 or SE16

Rohit

[Former Member · Accepted solution]

Hello Abhishek,

you can also use BR Tools in SAP to export the table from OS level.

Please follow the link given:

http://help.sap.com/saphelp_nw04s/helpdata/en/84/4d854f11848a49939538fc1c9aa4ef/content.htm

I am sure it will help you

Rohit

[Former Member · Accepted solution]

hI ,

i TRIED THE LINK, BUT IT IS NOT WORKING.

I used SE11 to check the field names. the thing is that I want 2 create the same table in my database. how can i extract the sql format so that i can exactly create the same table in to my database.

Abhishek

[Former Member · Accepted solution]

Abhishek,both the link are working from my side.

The contents of the 1st link that I have posted are:

I need to export (using the Oracle exp utility) a table from one Oracle database (database A schema=abc) to another Oracle database (database B schema=xyz). My login in database A assigns me to schema=123. How can I export this table from database A (schema=abc) and import it into database B (schema=xyz)?

EXPERT RESPONSE

The parameters for export and import are fully documented in the Oracle Utilities Guide.

To export just the table, you can use the following:

exp userid=abc/password file=my_table.dmp tables=my_table

The above will sign on to the database as user "abc" and dump the specified table. To import that table, you can still use the TABLES parameter for the imp utility. But to change the schema owner, you will also need to user the FROMUSER and TOUSER parameters as follows:

imp userid=system/password file=my_table.dmp

fromuser=abc touser=xyz tables=my_table

When changing schema owners, you will need to run import with a user that has the IMP_FULL_DATABASE role, like the SYSTEM user

The contents of the 2nd link are:

You can use BR*Tools for Oracle to export tables to an operating system file.

This section describes how you export tables with BR*Tools.

Procedure

...

1. Start the procedure using BRGUI or BRTOOLS, or from the command line:

¡ BRGUI or BRTOOLS:

i. Choose Segment Management ® Export tables.

BRGUI or BRTOOLS displays the menu BRSPACE options for export tables, where you specify the options with which you call BRSPACE.

ii. Set the required options:

Menu Entry

Equivalent BRSPACE

Command Option

BRSPACE profile (profile)

-p|-profile

Database user/password (user)

-u|-user

Tablespace names (tablespace)

-f tbexport -s|-tablespaces

Table owner (owner)

-f tbexport -o|-owner

Table names (table)

-f tbexport -t|-tables

Export dump directory

-f tbexport -u|-dumpdir

Confirmation mode (confirm)

-c|-confirm

Extended output (output)

-o|-output

Scrolling line count (scroll)

-s|-scroll

Message language (language)

-l|-language

BRSPACE command line (command)

This shows you the -BRSPACE -f -tbexport command that is to be executed using the current settings.

If required, in Table names you can enter the names of multiple tables. You can use wildcards. For more information, see u201CSelecting Objectsu201C in Segment Management with BR*Tools.

In Tablespace names and Table owner, you can specify multiple objects but you cannot use wildcards. BRSPACE processes all the tables in the specified tablespace name(s) or all tables belonging to the specified table owner(s). But these entries are optional.

You can also specify the dump directory using the parameter exp_dump_dir in the initialization profile init.sap, but the entry you make on the screen overwrites the profile parameter.

iii. Choose Continue.

BRGUI or BRTOOLS prompts you to start BRSPACE.

iv. Choose Continue to start BRSPACE.

¡ Command line:

...

Enter at least the following command:

brspace u2013f tbexport

You can enter more parameters, including the table names, if required. For more information, see -BRSPACE -f -tbexport.

Whichever way you start the procedure u2013 with BRGUI or BRTOOLS, or from the command line u2013 you can use quick mode if you know the final object names, in this case the table names. For more information, see How to Use BR*Tools.

BRSPACE starts and you see a message that includes Start of BRSPACE processing. From now on, BRSPACE writes a detail log.

2. If you have already entered the table names, continue with step 5 (quick mode).

If you have entered multiple tables, BRSPACE displays as confirmation a List of tables for export. If you have not already made a final selection, you can make a selection from this list.

Continue with step 5 (quick mode).

BRSPACE displays the Export tables main menu.

3. Choose Export tables.

BRSPACE displays the table list:

List Entry

Meaning

Pos.

List sequence number

Owner

Table owner

Table

Table name

Part.

Partitioned

Rows

Number of rows

Space

Space occupied by the table

Data

Amount of data in the table: percentage of occupied space

4. Select a table or multiple tables.

These examples only apply to input in character mode:

To select the first three tables in the list, enter 1-3.

To select the first and third tables, enter 1,3.

To select the first three tables and the fifth, enter 1-3,5.

To select all tables, select 0.

BRSPACE displays the menu, Main options for export of tables.

5. Set the required options:

Menu Entry

Meaning

Tablespaces for export (tablespaces)

u2013 display only

The tablespace that you selected above, if any

Owners for export (owner)

u2013 display only

The table owner that you selected above, if any

Export table rows (rows)

-f tbexport -r|-rows

Export table indexes (indexes)

-f tbexport -i|-indexes

Export table constraints (constraints)

-f tbexport -c|-constraints

Export table grants (grants)

-f tbexport -g|-grants

Export table triggers (triggers)

-f tbexport -t|-triggers

6. When you have set the required options, choose Continue

BRSPACE displays the menu, Additional options for export of tables.

Menu Entry

Meaning

Use direct path (direct)

-f tbexport -d|-direct

Export buffer size in KB (buffer)

-f tbexport -b|-buffer

Compress table extents (compress)

-f tbexport -m|-compress

Consistent export (consistent)

-f tbexport -n|-consistent

Max. size of dump file in MB (filesize)

-f tbexport -z|-filesize

Force table export (force)

-f tbexport -f|-force

EXP command (command)

The EXP command that is to be executed using the current settings. For more information, see your Oracle documentation.

7. To start processing with the selected options, choose Continue.

Results

Check the results in the BRSPACE logs.

· The summary log space.tbe displays the details.

For more information on how to view the logs with BRTools, see Showing Logs and Profiles with BRTools.

Two files are created during the export:

· The export dump file, expdat.dmp, is created in subdirectory of the directory $SAPDATA_HOME/sapreorg. It contains the parameters for the Oracle export tool, EXP.

Hpe it helps

Rohit

[Former Member · Accepted solution]

What is all Abt this BRTOOL ???

Let me clear one thing. I am connected to a PAID SAP SYSTEM. which is definitely not running on my machine and the thing is that that i want to download table fro mthis system to my system.

where i m getting this BRTOOL??

Abhishek

[Former Member · Accepted solution]

BR Tools is a utility which runs from OS level,it is available with every standard SAP installation

u can run that from sidadm or orasid

Rohit

[Former Member · Accepted solution]

pls provide me the complete command to use this tool.

Abhishek

[Former Member · Accepted solution]

I don't think this wil work here, as i told you i am using a paid SAP System of others, so i dont think that i wl be able to get databse connection authorization from their system.

between is there any way i can get the sql script of the table in T-code SE11 ?? so that i can create the same mirror image in my databse.

Abhishek

[fidel_vales · Accepted solution]

Hi,

First, I'm not sure what do you want, as you contradict yourself:

> between is there any way i can get the sql script of the table in T-code SE11 ?? so that i can create the same mirror image in my databse.

Do you want a SQL script to be able to create the table or do you want the content of the table?

Second, If you need the data, then you should ask the administrators of that "paid SAP System". Otherwise, I would think that you do not have authorizations to extract the data and you are doing something that is not allowed.

> I don't think this wil work here, as i told you i am using a paid SAP System of others, so i dont think that i wl be able to get databse connection authorization from their system.

[Former Member · Accepted solution]

A SQL Script is more assisting.

Abhishek

[Former Member · Accepted solution]

Hi Abhishek,

You can't export a table without going at OS level,only the contents from SE16 or SE11

Even a SQL script can only be run on OS platform

Rohit

[former_member204746 · Accepted solution]

check SAP note 130906. it uses R3TRANS at OS level to export/import table contents.

this might be useful for you.

[Former Member · Accepted solution]

Hello

Also a easy option is oracles COPY FROM command, which allows you to copy table contense from database A into a table with the same field settings on database B. It's only required that both databases allow remote login and are on the same network.

Command example:

COPY FROM <db_user>/<password>@<Source DB> -

TO <db_user>/<password>@<Target DB> -

insert <target_table> (field_list) -

USING SELECT * FROM <source_table>;

Regards

Florian Wittmann

[Former Member · Accepted solution]

do you know how to use native SQL command to see the ABAP Program likes SAPMSSY1 ?

Abhishek

[Former Member · Accepted solution]

Hi Abhishek,

I am not sure what you mean but if you like to find out how the SQL statements look like when they are prompted to the database, you can have a look in ST04 via "Detail analysis menu" -> "SQL request", than filter the statements by ABAP programm and you can see SQL's it produced. Also you can do a SQL trace via ST05 or you install statspack reports if you use oracle, which also allows you to trace the satements.

If this did not meat your answer, thanks to correct me

Cheers

Florian

[Former Member]

Hello Abhishek,

You will have to go at the OS level to export the table

Only option is you can use SE16 to download the contents of a table.

Rohit