السلام عليكم

هناك بعض الأسئلة وجدتها في أحد الكتب ولكني لم أعرف لها حلا ً.

وهي:
الأول

For example, one of the control file is deleted. When you start your database instance, you will receive message that database instance cannot startup. Write down steps how to resolve this task. Your steps must be outcome of implementation

.

الثاني

Write down SQL statement that shows free tablespace of one or more tablespaces?

الثالث

Write down SQL statement that shows a particular table exists on which tablespaces

.

الرابع

Suppose you want to import data in one of the user’s schema, some tables with same names already existing in a schema. Write down tested imp command to accomplish it. Also, show the output result of your command.

فهلا أجد الإجابة هنا؟

وشكرا ً

إليكم الإجابات ماعدا السؤال الثالث:

Q1. For example, one of the control file is deleted. When you start your database instance, you will receive message that database instance cannot startup. Write down steps how to resolve this task. Your steps must be outcome of implementation.
A1. To safeguard against a single point of failure of the control file, it is strongly recommended that the control file be multiplexed. If a control file is lost, a multiplexed copy of the control file can be used to restart the instance without database recovery.

These procedures assume that one of the control files specified in the CONTROL_FILES parameter is corrupted, that the control file directory is still accessible, and that you have a multiplexed copy of the control file

Recovering from Control File Corruption Using a Control File Copy
1.	With the instance shut down, use an operating system command to overwrite the bad control file with a good copy:
     % cp /u03/oracle/prod/control03.ctl  /u02/oracle/prod/control02.ctl

2.	Start SQL*Plus and open the database:
   SQL> STARTUP

Recovering from Permanent Media Failure Using a Control File Copy

1.	With the instance shut down, use an operating system command to copy the current copy of the control file to a new, accessible location:
   % cp /u01/oracle/prod/control01.ctl  /u04/oracle/prod/control03.ctl

2.	Edit the CONTROL_FILES parameter in the initialization parameter file to replace the bad location with the new location:
    CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
                     /u02/oracle/prod/control02.ctl, 
                     /u04/oracle/prod/control03.ctl)

3.	Start SQL*Plus and open the database:
SQL> STARTUP


If you have multiplexed control files, you can get the database started up quickly by editing the CONTROL_FILES initialization parameter. Remove the bad control file from CONTROL_FILES setting and you can restart the database immediately. Then you can perform the reconstruction of the bad control file and at some later time shut down and restart the database after editing the CONTROL_FILES initialization parameter to include the recovered control file.

Q2. Write down SQL statement that shows free tablespace of one or more tablespaces?
A2. To produce statistics about free extents and coalescing activity for each tablespace in the database, enter the following query:
SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,
  COUNT(*)    "PIECES",
  MAX(blocks) "MAXIMUM",
  MIN(blocks) "MINIMUM",
  AVG(blocks) "AVERAGE",
  SUM(blocks) "TOTAL"
  FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;

TABLESPACE    FILE_ID  PIECES   MAXIMUM    MINIMUM  AVERAGE    TOTAL
----------    -------  ------   -------    -------  -------   ------
RBS                 2       1       955        955      955      955
SYSTEM              1       1       119        119      119      119
TEMP                4       1      6399       6399     6399     6399
TESTTBS             5       5      6364          3     1278     6390
USERS               3       1       363        363      363      363

PIECES shows the number of free space extents in the tablespace file, MAXIMUM and MINIMUM show the largest and smallest contiguous area of space in database blocks, AVERAGE shows the average size in blocks of a free space extent, and TOTAL shows the amount of free space in each tablespace file in blocks.
This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.

Q3. Write down SQL statement that shows a particular table exists on which tablespaces.


Q4. Suppose you want to import data in one of the user’s schema, some tables with same names already existing in a schema. Write down tested imp command to accomplish it. Also, show the output result of your command.

A4. The command is:
imp scott/tiger file=emp.dmp full=yes
imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept

Use IGNORE=NO to skip tables that already exist (imp will give an error if the object is re-created).

السلام عليكم و رحمة الله و بركاته
اجابة السؤال الثالث

select TABLE_NAME
from dba_tables where TABLESPACE_NAME
like 'tablspace name' ; this for non partined tables

شكرا ً للأخ الكريم hkhalifa