DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: data

  1. #1
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401

    data

    is there any we can extract only 2 to 3 rows of data , even if the table contains more than that and thier dependencies so that I can copy to some other box as i am buliding with similar schema and table spaces etc


    i already created tables but is there any way , if not how do i tell the export command to do only 2 or 3 rows


    thanks...

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Do a select statement using the query parameter.


    http://download-west.oracle.com/docs...01.htm#1005545
    QUERY
    Default: none

    This parameter allows you to select a subset of rows from a set of tables when doing a table mode export. The value of the query parameter is a string that contains a WHERE clause for a SQL SELECT statement that will be applied to all tables (or table partitions) listed in the TABLE parameter.

    For example, if user scott wants to export only those employees whose job title is SALESMAN and whose salary is less than 1600, he could do the following (this example is UNIX-based):

    exp scott/tiger TABLES=emp QUERY=\"WHERE job=\'SALESMAN\' and sal \<1600\"

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Not much of a random set there though.

    you might try the SAMPLE clause of the SELECT statement.

    Here's an extract from the SQL Reference ...

    The following query estimates the number of orders in the oe.orders table:

    SELECT COUNT(*) * 100 FROM orders SAMPLE (1);

    The following example creates a sampled subset of the sample table hr.employees table and then joins the resulting sampled table with departments. This operation circumvents the restriction that you cannot specify the sample_clause in join queries:

    CREATE TABLE sample_emp AS
    SELECT employee_id, department_id FROM employees SAMPLE(10);

    SELECT e.employee_id FROM sample_emp e, departments d
    WHERE e.department_id = d.department_id
    AND d.department_name = 'Sales';
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    Let me try !! Thank you very much for your time !!

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Here is a good method to select random rows.

    SQL>create table t1 as select rownum as rnum, a.* from dba_objects );

    SQL> select rnum , object_name
    from ( select * from t1 order by dbms_random.value)
    where rownum < 4

    11861 PS_SCRTY_ACC_GRP
    30754 PS_ST_EXER_REL_VW
    36361 INPUT_PARAMETER2_FK

    Elapsed: 00:00:02.83
    SQL> /
    24774 PS_GVT_DISP_OFFNS
    16813 PS0GP_GCTL_FREQ
    3918 GV$SQL_SHARED_MEMORY

    Elapsed: 00:00:02.73
    SQL>/
    14401 PS_TRVL_DEPENDENT
    12699 PS_TL_ADM_SRCH_VW
    10216 PS_PA_CLC_SOC_AY

    Every time I run, I get different rows because of order by clause where I used dbms_random.value. This will only work in 9i.

    Tamil

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    Who said anything about random? All I saw was two or three rows. Just use where rownum < 3. That should be enough for what you are looking for.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width