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
Do a select statement using the query parameter.
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\"
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';
Let me try !! Thank you very much for your time !!
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
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.
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.
Click Here to Expand Forum to Full Width