Questions & Scenarios (exp & imp ) :
This scenarios and questions is mostly useful for practice the subject and interview point of preparations.
1. what are values for compress in exp and expdp in exp compress= y / n
in expdp compress=COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}
The available options are:
ALL: Both metadata and data are compressed.
DATA_ONLY: Only data is compressed.
METADATA_ONLY: Only metadata is compressed. This is the default setting.
NONE: Nothing is compressed.
2. Can we use query and buffer parameter if we use direct = y in exp -
ans. no we can not!
example of using direct = y and having query parameter throws an error like this:
SQL> host exp \"sys/sys as sysdba\" file=d:\testdata_exp.dmp direct=y tables=scott.employees query=\"where employee_id > 200\"
Export: Release 11.2.0.4.0 - Production on Thu may 7 11:13:23 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 11.2.0.4.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00071: QUERY parameter not compatible with Direct Path export
EXP-00000: Export terminated unsuccessfully
3. We can use parameter buffer when we are using direct=y (i.e. using direct path), right or wrong??
SQL> host exp \"sys/sys as sysdba\" file=d:\testdata_exp.dmp direct=y tables=scott.employees buffer=50
Export: Release 11.2.0.4.0 - Production on Thu Jun 28 11:16:48 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 11.2.0.4.0 - Produc
tion
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 Direct Path ...
Current user changed to SCOTT
. . exporting table EMPLOYEES 107 rows exported
Export terminated successfully without warnings.
4. use of direct=n and buffer=50 (i.e. using conventional path)
SQL> host exp \"sys/sys as sysdba\" file=d:\testdata_exp.dmp direct=n tables=sc
ott.employees buffer=50
Export: Release 11.2.0.4.0 - Production on Thu Jun 28 11:17:21 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 11.2.0.4.0 - Produc
tion
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 ...
Current user changed to SCOTT
. . exporting table EMPLOYEES 107 rows exported
Export terminated successfully without warnings.
5. if user exists while imp ( full = y ) - what happens
6. if user doesnt exists while imp (full = y) - what happens
7. if users tablespace doesnt exists while imp what happens
8. if user exists while impdp - what happens
9. if user doesnt exists while impdp - what happens
10. if users tablespace doesnt exists while impdp what happens
11. can we do exp and them impdp or do expdp and then imp
12. in a owner level imp / the object already exists in that owner - what happnes
13. what is inctype parameter - does it work in the current version of oracle database
14. what is record parameter - does it work in the current version of oracle database
15. if you say rows=n - what happens?
16. if dumpfile is given, and you are suppose to do import, how will you understand the export level -whether it is full, owner/s, table/s or tablepace
17. use of show parameter is imp? can i use show in exp
18. use of feedback parameter? can i use feedback in exp as well as imp
19. what is statistics parameter - it can take three values 1. none 2. compute 3. estimate (which is default)
20. how to calculte buffer parameter? is it one of the way by which we can make exp work faster?
21. what does record length parameter do?
22. Can we use direct and query parameter together?
23. Can we use direct and buffer parameter together?
24. Give syntax of query parameter to filter some rows from the table as well as user of order by clause on the query
25. I have taken exp in Oracle 10g database, can i import it in 9i database and vice versa
26. While doing export power failure occured and the export terminated, can we resume that export task again from where it has failed when the server has restated?
27. what is resumable parameter, where and how to use it? (resumable, resumable_time, resumable_name)
28. how does consistent and object_consistent parameters work?
29. when to use volsize parameter?
30. I am a user in the database and would like to export my own schema, which role should be assigned to me?
31. who is a built in user having role exp_full_database apart from sys?
32. Why is it said to be not a good practice to do exp/imp using sys user
33. why one should not do exp/imp from sql promt (prefixing '!' in linux os or host key word in windows os)
34. How to calculate logical size of an database, an owner or table/s
35. Why we use filesize parameter?
36. In export we have taken dump in multiple file, what precaution we have to take while importing from all these dump file?
37. In what unit export takes the logical backup (bytes/blocks)
38. List logical objects?
39. If client machine where the exp has started is on, but the server goes off, what will happen? what do you mean by exp is a client process
40. Can I do exp/imp when database is shutdown? What should the status of database when you do imp/exp (nomount/mount/open)
41. Why is exp/imp known as command line utilities?
42. export table, what happens to objects such as procedures, functions, synonyms, views, sequences etc. which are depended on this table. Will they get export - note: we have a choice to select index, rows, grants, constraints as we have parameters to say yes or no for the specified dependend object on table
43. export table and import table, if the table exists in the destionation schema where you import what will happen
44. export table from one schema i.e. a and try to import it in another schema i.e. b, which parameters are require to be added to do the export of a's table into import of b
a. if a's default tablespace is different than b's default tablespace what will happen (considering a and b are two schema in same database)
b. if a's default tablespace is different than b's default tablespace, plus b has got resource role granted, what will happen
c. if a's default tablespace is different than b's default tablespace and quota is already utilized by b what will happen
d. if a's schema export from one database has to import into another database, and user a is not present, what will happen, we have not used fromuser and touser parameters while import.
e. if a's schema export from one database has to import into another database, and user a is not present, what will happen, we have not used fromuser and touser parameters while import plus tablespace on which schema a has quota in exporting database that tablespace is missing in the importing database
f. if a's schema export from one database has to import into another database, and user a is present, what will happen? how can you justify that a in the importing schema previous to import the number of objects and after import the number of object has gone up.
g. if a's schema export from one database has to import into another database, and user a is present, what will happen if in the importing schema one of the object name for e.g. test is already present. In exporting schema test is a table, in importing schema test is a view
h. what will happen if we export two tables dept, emp such as dept table is having dept_id as primary key and dept_id in emp table is a foreign key, we have used parameter constraints=n while exporting and then done the import
i.if we export a's schema and in import map it to b, here if b is not present, will the user(schema) be created
45. taken a full exp, in import can we imp certain schemas/tables from the full dump file
46. we are trying to take schema using the paramter owner, same time we want certain tables from other schema too. so we wrote both the paramters owner = schema_name tables=schema.table1, schema.table2 will it work
47. how to apply redo's on a table
48. why are we not suppose to import file by any other user which was exported by a sysdba
49. What happens to sys schema when we export it
50. What happens to sys schema when we import a full dump on to it
51. how to calculate logical size of table, owner i.e. schema and full database
52. how to calculate recordlength? when to use recordlength
53. How to calculate the correct buffer size for the parameter buffer? why the buffer by default is of 4096 bytes? what could be the maximum size of a buffer? what is evaluation buffer? for evaluation buffer the memory is given from where?
54. List users who by default have the role - exp_full_database and imp_full_database
55. what are the post task to be perfomed when we do imp
56. why do we use parameter ignore in imp
57. In what unit export takes the logical backup (bytes/blocks)
58. List logical objects?
59. Can I do exp/imp when database is shutdown? What should the status of database when you do imp/exp (nomount/mount/open)
60. what is resumable parameter, where and how to use it? (resumable, resumable_time, resumable_name)
61. use of show parameter is imp? can i use show in exp
62. use of feedback parameter? can i use feedback in exp as well as imp
63. use of indexfile parameter in imp? how show parameter of imp is different from indexfile parameter of imp
This scenarios and questions is mostly useful for practice the subject and interview point of preparations.
1. what are values for compress in exp and expdp in exp compress= y / n
in expdp compress=COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}
The available options are:
ALL: Both metadata and data are compressed.
DATA_ONLY: Only data is compressed.
METADATA_ONLY: Only metadata is compressed. This is the default setting.
NONE: Nothing is compressed.
2. Can we use query and buffer parameter if we use direct = y in exp -
ans. no we can not!
example of using direct = y and having query parameter throws an error like this:
SQL> host exp \"sys/sys as sysdba\" file=d:\testdata_exp.dmp direct=y tables=scott.employees query=\"where employee_id > 200\"
Export: Release 11.2.0.4.0 - Production on Thu may 7 11:13:23 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 11.2.0.4.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00071: QUERY parameter not compatible with Direct Path export
EXP-00000: Export terminated unsuccessfully
3. We can use parameter buffer when we are using direct=y (i.e. using direct path), right or wrong??
SQL> host exp \"sys/sys as sysdba\" file=d:\testdata_exp.dmp direct=y tables=scott.employees buffer=50
Export: Release 11.2.0.4.0 - Production on Thu Jun 28 11:16:48 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 11.2.0.4.0 - Produc
tion
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 Direct Path ...
Current user changed to SCOTT
. . exporting table EMPLOYEES 107 rows exported
Export terminated successfully without warnings.
4. use of direct=n and buffer=50 (i.e. using conventional path)
SQL> host exp \"sys/sys as sysdba\" file=d:\testdata_exp.dmp direct=n tables=sc
ott.employees buffer=50
Export: Release 11.2.0.4.0 - Production on Thu Jun 28 11:17:21 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 11.2.0.4.0 - Produc
tion
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 ...
Current user changed to SCOTT
. . exporting table EMPLOYEES 107 rows exported
Export terminated successfully without warnings.
5. if user exists while imp ( full = y ) - what happens
6. if user doesnt exists while imp (full = y) - what happens
7. if users tablespace doesnt exists while imp what happens
8. if user exists while impdp - what happens
9. if user doesnt exists while impdp - what happens
10. if users tablespace doesnt exists while impdp what happens
11. can we do exp and them impdp or do expdp and then imp
12. in a owner level imp / the object already exists in that owner - what happnes
13. what is inctype parameter - does it work in the current version of oracle database
14. what is record parameter - does it work in the current version of oracle database
15. if you say rows=n - what happens?
16. if dumpfile is given, and you are suppose to do import, how will you understand the export level -whether it is full, owner/s, table/s or tablepace
17. use of show parameter is imp? can i use show in exp
18. use of feedback parameter? can i use feedback in exp as well as imp
19. what is statistics parameter - it can take three values 1. none 2. compute 3. estimate (which is default)
20. how to calculte buffer parameter? is it one of the way by which we can make exp work faster?
21. what does record length parameter do?
22. Can we use direct and query parameter together?
23. Can we use direct and buffer parameter together?
24. Give syntax of query parameter to filter some rows from the table as well as user of order by clause on the query
25. I have taken exp in Oracle 10g database, can i import it in 9i database and vice versa
26. While doing export power failure occured and the export terminated, can we resume that export task again from where it has failed when the server has restated?
27. what is resumable parameter, where and how to use it? (resumable, resumable_time, resumable_name)
28. how does consistent and object_consistent parameters work?
29. when to use volsize parameter?
30. I am a user in the database and would like to export my own schema, which role should be assigned to me?
31. who is a built in user having role exp_full_database apart from sys?
32. Why is it said to be not a good practice to do exp/imp using sys user
33. why one should not do exp/imp from sql promt (prefixing '!' in linux os or host key word in windows os)
34. How to calculate logical size of an database, an owner or table/s
35. Why we use filesize parameter?
36. In export we have taken dump in multiple file, what precaution we have to take while importing from all these dump file?
37. In what unit export takes the logical backup (bytes/blocks)
38. List logical objects?
39. If client machine where the exp has started is on, but the server goes off, what will happen? what do you mean by exp is a client process
40. Can I do exp/imp when database is shutdown? What should the status of database when you do imp/exp (nomount/mount/open)
41. Why is exp/imp known as command line utilities?
42. export table, what happens to objects such as procedures, functions, synonyms, views, sequences etc. which are depended on this table. Will they get export - note: we have a choice to select index, rows, grants, constraints as we have parameters to say yes or no for the specified dependend object on table
43. export table and import table, if the table exists in the destionation schema where you import what will happen
44. export table from one schema i.e. a and try to import it in another schema i.e. b, which parameters are require to be added to do the export of a's table into import of b
a. if a's default tablespace is different than b's default tablespace what will happen (considering a and b are two schema in same database)
b. if a's default tablespace is different than b's default tablespace, plus b has got resource role granted, what will happen
c. if a's default tablespace is different than b's default tablespace and quota is already utilized by b what will happen
d. if a's schema export from one database has to import into another database, and user a is not present, what will happen, we have not used fromuser and touser parameters while import.
e. if a's schema export from one database has to import into another database, and user a is not present, what will happen, we have not used fromuser and touser parameters while import plus tablespace on which schema a has quota in exporting database that tablespace is missing in the importing database
f. if a's schema export from one database has to import into another database, and user a is present, what will happen? how can you justify that a in the importing schema previous to import the number of objects and after import the number of object has gone up.
g. if a's schema export from one database has to import into another database, and user a is present, what will happen if in the importing schema one of the object name for e.g. test is already present. In exporting schema test is a table, in importing schema test is a view
h. what will happen if we export two tables dept, emp such as dept table is having dept_id as primary key and dept_id in emp table is a foreign key, we have used parameter constraints=n while exporting and then done the import
i.if we export a's schema and in import map it to b, here if b is not present, will the user(schema) be created
45. taken a full exp, in import can we imp certain schemas/tables from the full dump file
46. we are trying to take schema using the paramter owner, same time we want certain tables from other schema too. so we wrote both the paramters owner = schema_name tables=schema.table1, schema.table2 will it work
47. how to apply redo's on a table
48. why are we not suppose to import file by any other user which was exported by a sysdba
49. What happens to sys schema when we export it
50. What happens to sys schema when we import a full dump on to it
51. how to calculate logical size of table, owner i.e. schema and full database
52. how to calculate recordlength? when to use recordlength
53. How to calculate the correct buffer size for the parameter buffer? why the buffer by default is of 4096 bytes? what could be the maximum size of a buffer? what is evaluation buffer? for evaluation buffer the memory is given from where?
54. List users who by default have the role - exp_full_database and imp_full_database
55. what are the post task to be perfomed when we do imp
56. why do we use parameter ignore in imp
57. In what unit export takes the logical backup (bytes/blocks)
58. List logical objects?
59. Can I do exp/imp when database is shutdown? What should the status of database when you do imp/exp (nomount/mount/open)
60. what is resumable parameter, where and how to use it? (resumable, resumable_time, resumable_name)
61. use of show parameter is imp? can i use show in exp
62. use of feedback parameter? can i use feedback in exp as well as imp
63. use of indexfile parameter in imp? how show parameter of imp is different from indexfile parameter of imp
No comments:
Post a Comment