Oracle DBA FAQ - Oracle Basic Concepts
This is a collection of 17 FAQs for Oracle DBA on fundamental concepts. The clear answers and sample scripts provided can be used as learning tutorials or interview preparation guides. Topics included in this FAQ are:
- What Is Oracle?
- What Is an Oracle Database?
- What Is an Oracle Instance?
- What Is a Parameter File?
- What Is a Server Parameter File?
- What Is a Initialization Parameter File?
- What is System Global Area (SGA)?
- What is Program Global Area (PGA)?
- What Is a User Account?
- What Is the Relation of a User Account and a Schema?
- What Is a User Role?
- What is a Database Schema?
- What Is a Static Data Dictionary?
- What Is a Dynamic Performance View?
- What Is SQL*Plus?
- What Is What Is Transport Network Substrate (TNS)?
- What Is Open Database Communication (ODBC)?
Descriptions of some key concepts are based on Oracle documentations.
What Is Oracle?
Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and retrieve related data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.
What Is an Oracle Database?
An Oracle database is a collection of data treated as a big unit in the database server.
What Is an Oracle Instance?
Every running Oracle database is associated with an Oracle instance. When a database is started on a database server (regardless of the type of computer), Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database's data efficiently and serve the one or multiple users of the database.
What Is a Parameter File?
A parameter file is a file that contains a list of initialization parameters and a value for each parameter. You specify initialization parameters in a parameter file that reflect your particular installation. Oracle supports the following two types of parameter files:
- Server Parameter Files - Binary version. Persistent.
- Initialization Parameter Files - Text version. Not persistent.
What Is a Server Parameter File?
A server parameter file is a binary file that acts as a repository for initialization parameters. The server parameter file can reside on the machine where the Oracle database server executes. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup.
What Is a Initialization Parameter File?
An initialization parameter file is a text file that contains a list of initialization parameters. The file should be written in the client's default character set. Sample initialization parameter files are provided on the Oracle distribution medium for each operating system. A sample file is sufficient for initial use, but you will probably want to modify the file to tune the database for best performance. Any changes will take effect after you completely shut down and restart the instance.
What is System Global Area (SGA)?
The System Global Area (SGA) is a memory area that contains data shared between all database users such as buffer cache and a shared pool of SQL statements. The SGA is allocated in memory when an Oracle database instance is started, and any change in the value will take effect at the next startup.
What is Program Global Area (PGA)?
A Program Global Area (PGA) is a memory buffer that is allocated for each individual database session and it contains session specific information such as SQL statement data or buffers used for sorting. The value specifies the total memory allocated by all sessions, and changes will take effect as new sessions are started.
What Is a User Account?
A user account is identified by a user name and defines the user's attributes, including the following:
- Password for database authentication
- Privileges and roles
- Default tablespace for database objects
- Default temporary tablespace for query processing work space
What Is the Relation of a User Account and a Schema?
User accounts and schemas have a one-to-one relation. When you create a user, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user.
What Is a User Role?
A user role is a group of privileges. Privileges are assigned to users through user roles. You create new roles, grant privileges to the roles, and then grant roles to users.
What is a Database Schema?
A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include: tables, views, and other types of data objects.
What Is a Database Table?
A database table is a basic unit of data logical storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.
What Is a Table Index?
Index is an optional structure associated with a table that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.
What Is an Oracle Tablespace?
An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used by the Oracle server to store structures data objects, like tables and indexes.
Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.
What Is an Oracle Data File?
An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.
What Is a Static Data Dictionary?
Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views. To list the data dictionary views available to you, query the view DICTIONARY. Many data dictionary tables have three corresponding views:
- An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.
- A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.
- A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.
- What Is a Dynamic Performance View?
- Oracle contains a set of underlying views that are maintained by the database server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance. Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. You can select from these views, but you can never update or alter them.
- What Is a Recycle Bin?
- Recycle bin is a logical storage to hold the tables that have been dropped from the database, in case it was dropped in error. Tables in recycle bin can be recovered back into database by the Flashback Drop action. Oracle database recycle save the same purpose as the recycle bin on your Windows desktop.
- Recycle bin can be turned on or off in the recyclebin=on/off in your parametere file.
- What Is SQL*Plus?
- SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface.
- What Is Transport Network Substrate (TNS)?
- TNS, Transport Network Substrate, is a foundation technology, built into the Oracle Net foundation layer that works with any standard network transport protocol.
- What Is Open Database Communication (ODBC)?
- ODBC, Open Database Communication, a standard API (application program interface) developed by Microsoft for Windows applications to communicate with database management systems.
- Oracle offers ODBC drivers to allow Windows applications to connect Oracle server through ODBC.
Oracle DBA FAQ - Loading and Exporting Data
A collection of 27 FAQs on Oracle loading data and exporting data. Clear answers are provided with tutorial exercises on saving data as flat files, loading data from flat, exporting and importing database, schema and tables, creating external tables. Topics included in this FAQ are:
- What Is the Simplest Tool to Run Commands on Oracle Servers?
- What Is the Quickest Way to Export a Table to a Flat File?
- How To Export Data with a Field Delimiter?
- What Is SQL*Loader?
- What Is a SQL*Loader Control File?
- How To Load Data with SQL*Loader?
- What Is an External Table?
- How To Load Data through External Tables?
- What Are the Restrictions on External Table Columns?
- What Is a Directory Object?
- How To Define an External Table with a Text File?
- How To Run Queries on External Tables?
- How To Load Data from External Tables to Regular Tables?
- What Is the Data Pump Export Utility?
- What Is the Data Pump Import Utility?
- How To Invoke the Data Pump Export Utility?
- How To Invoke the Data Pump Import Utitlity?
- What Are Data Pump Export and Import Modes?
- How To Estimate Disk Space Needed for an Export Job?
- How To Do a Full Database Export?
- Where Is the Export Dump File Located?
- How To Export Your Own Schema?
- How To Export Several Tables Together?
- What Happens If the Imported Table Already Exists?
- How To Import One Table Back from a Dump File?
- What Are the Original Export and Import Utilities?
- How To Invoke the Original Export Import Utilities?
Sample scripts used in this FAQ assumes that you are connected to the server with the HR user account on the default database instance XE. See other FAQ collections on how to connect to the server.
Some sample scripts may require database tables created by other samples in the beginning of the collection.
What Is the Simplest Tool to Run Commands on Oracle Servers?
The simplest tool to connect to an Oracle server and run commands to manage data is SQL*Plus. It is an Oracle database client tool that works as a command-line user interface to the database server. SQL*Plus allows you:
- Format, perform calculations on, store, and print from query results.
- Examine table and object definitions.
- Develop and run batch scripts.
- Perform database administration.
What Is the Quickest Way to Export a Table to a Flat File?
The quickest way to export a table to a flat file is probably to use the SQL*Plus SPOOL command. It allows you to record SELECT query result to a text file on the operating system. The following tutorial exercise shows you how control the output format, start the spooler, and dump all record from a table to a flat text file:
>mkdir \oraclexe\test
>sqlplus /nolog
SQL> connect HR/fyicenter
SQL> SET HEADING OFF;
SQL> SET FEEDBACK OFF;
SQL> SET LINESIZE 1000;
SQL> SPOOL \oraclexe\test\employees.txt;
SQL> SELECT * FROM EMPLOYEES;
......
SQL> SPOOL OFF;
You should get all records in employees.txt with fixed length fields.
How To Export Data with a Field Delimiter?
The previous exercise allows you to export data with fixed field lengths. If you want export data with variable field lengths and field delimiters, you can concatenate your fields with an expression in the SELECT clause as shown in the tutorial exercise bellow:
SQL> SET HEADING OFF;
SQL> SET FEEDBACK OFF;
SQL> SET LINESIZE 1000;
SQL> SPOOL \oraclexe\test\fyi_links.txt;
SQL> SELECT id ||','|| url ||','|| notes ||','|| counts
||','|| created FROM fyi_links;
......
SQL> SPOOL OFF;
You should see all records in fyi_links.txt with ',' delimited fields as shown here:
101,fyicenter.com,Session 1,,17-MAY-06
110,centerfyi.com,Session 1,,17-MAY-06
What Is SQL*Loader?
SQL*Loader is a database tool that allows to load data from external files into database tables. SQL*Loader is available as part of the free Oracle 10g Expression Edition. It has some interesting features as:
- Can load data from multiple data files into multiple tables in one load session.
- Can specify character set of the data.
- Can generate sophisticated error reports.
- Can load data directly to Oracle datafiles, bypassing normal record inserting process.
What Is a SQL*Loader Control File?
A SQL*Loader control file a text that defines how data files should be loaded into the database. It allows you to specify:
- Where is the input data file.
- The format of the input date file.
- The target table where the data should be loaded.
- How input data fields should be mapped to target table columns.
- Select criteria to select input records for loading.
- Where to output errors.
How To Load Data with SQL*Loader?
Let's say you have a table defined as:
CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
first_name VARCHAR(80) NOT NULL,
last_name VARCHAR(80) NOT NULL,
birth_date DATE NOT NULL,
social_number VARCHAR(80) UNIQUE NOT NULL);
There is an input data file stored at \oraclexe\text\student.txt with the following records:
1,Steven,King,17-JUN-77,515.123.4567
2,Neena,Kochhar,21-SEP-79,515.123.4568
3,Lex,De Haan,13-JAN-83,515.123.4569
4,Alexander,Hunold,03-JAN-80,590.423.4567
5,Bruce,Ernst,21-MAY-81,590.423.4568
6,David,Austin,25-JUN-87,590.423.4569
7,Valli,Pataballa,05-FEB-88,590.423.4560
8,Diana,Lorentz,07-FEB-89,590.423.5567
9,Nancy,Greenberg,17-AUG-84,515.124.4569
10,Daniel,Faviet,16-AUG-84,515.124.4169
You can create a control file at \oraclexe\test\student.ctl as:
LOAD DATA
APPEND INTO TABLE STUDENT
FIELDS TERMINATED BY ','
(id, first_name, last_name, birth_date, social_number)
When you are ready to load data, you can run the SQL*Loader with the "sqlldr" command:
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN
>sqlldr userid=hr/fyicenter,
control=\oraclexe\test\student.ctl,
data=\oraclexe\test\student.txt,
log=\oraclexe\test\student.log
SQL*Loader: Release 10.2.0.1.0 -
Commit point reached - logical record count 10
To see details of the loading process, you should check the log file \oraclexe\test\student.log.
What Is an External Table?
An external table is a table defined in the database with data stored outside the database. Data of an external table is stored in files on the operating systems. Accessing data of external tables are done through data access drivers. Currently, Oracle supports two data access drivers: ORACLE_LOADER and ORACLE_DATAPUMP.
External tables can be used to load data from external files into database, or unload data from database to external files.
How To Load Data through External Tables?
If you have data stored in external files, you can load it to database through an external table by the steps below:
- Create an external table with columns matching data fields in the external file.
- Create a regular table with the same columns.
- Run an INSERT INTO ... SELECT statement to load data from the external file to the regular table through the external table.
What Are the Restrictions on External Table Columns?
When creating external table columns, you need to watch out some restrictions:
- "PRIMARY KEY" is not allowed.
- "NOT NULL" is not allowed.
- "DEFAULT value" is not allowed.
What Is a Directory Object?
A directory object is a logical alias for a physical directory path name on the operating system. Directory objects can be created, dropped, and granted access permissions to different users. The following tutorial exercise shows you some good examples:
>sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> CREATE DIRECTORY test_dir AS '/oraclexe/test';
Directory created.
SQL> GRANT READ ON DIRECTORY test_dir TO hr;
Grant succeeded.
SQL> GRANT WRITE ON DIRECTORY test_dir TO hr;
Grant succeeded.
SQL> CREATE DIRECTORY temp_dir AS '/oraclexe/temp';
Directory created.
SQL> DROP DIRECTORY temp_dir;
Directory dropped.
How To Define an External Table with a Text File?
You can use the CREATE TABLE statement to create external tables. But you need to use ORGANIZATION EXTERNAL clause to specify the external file location and the data access driver. The tutorial exercise below shows you how to define an external table as a text file:
>sqlplus /nolog
SQL> connect HR/fyicenter
SQL> CREATE TABLE ext_fyi_links (
id NUMBER(4),
url VARCHAR2(16),
notes VARCHAR2(16),
counts NUMBER(4),
created DATE
) ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY test_dir
LOCATION ('ext_fyi_links.txt')
);
Table created.
SQL> SELECT table_name, tablespace_name, num_rows
FROM USER_TABLES;
TABLE_NAME TABLESPACE_NAME NUM_ROWS
--------------------- ---------------------- ----------
REGIONS USERS 4
LOCATIONS USERS 23
DEPARTMENTS USERS 27
JOBS USERS 19
EMPLOYEES USERS 107
JOB_HISTORY USERS 10
FYI_LINKS USERS 2
EXT_FYI_LINKS
COUNTRIES 25
How To Run Queries on External Tables?
If you have an external table defined as a text file with the ORACLE_LOADER driver, you can add data to the text file, and query the text file through the external table. By default, data fields in the text file should be terminated by ','. The tutorial exercise below shows you how add data to the external table defined in the previous exercise:
>edit /oraclexe/test/ext_fyi_links.txt
1101,dba.fyicenter,Link #1,88,07-MAY-06
1110,dev.fyicenter,Link #2,88,07-MAY-06
>sqlplus /nolog
SQL> connect HR/fyicenter
SQL> SELECT * FROM ext_fyi_links;
ID URL NOTES COUNTS CREATED
--------- ---------------- ----------- -------- ---------
1101 dba.fyicenter Link #1 88 07-MAY-06
1110 dev.fyicenter Link #2 88 07-MAY-06
How To Load Data from External Tables to Regular Tables?
Once you have your data entered in a text file, and an external table defined to this text file, you can easily load data from this text file to a regular table. The following tutorial exercise shows you how to load data from the text file and the external table defined in the previous exercises to a regular table:
SQL> CREATE TABLE fyi_links (
id NUMBER(4) PRIMARY KEY,
url VARCHAR2(16) NOT NULL,
notes VARCHAR2(16),
counts NUMBER(4),
created DATE DEFAULT (sysdate)
);
SQL> INSERT INTO fyi_links SELECT * FROM ext_fyi_links;
2 rows created.
SQL> SELECT * FROM fyi_links;
ID URL NOTES COUNTS CREATED
--------- ---------------- ----------- -------- ---------
101 fyicenter.com Session 1 07-MAY-06
110 centerfyi.com Session 1 07-MAY-06
1101 dba.fyicenter Link #1 88 07-MAY-06
1110 dev.fyicenter Link #2 88 07-MAY-06
What Is the Data Pump Export Utility?
Oracle Data Pump Export utility is a standalone programs that allows you to export data objects from Oracle database to operating system files called dump file set, which can be imported back to Oracle database only by Oracle Data Pump Import utility.
The dump file set can be imported on the same system or it can be moved to another system and loaded there.
The dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format. During an import operation, the Data Pump Import utility uses these files to locate each database object in the dump file set. Because the dump files are written by the server, rather than by the client, the data base administrator (DBA) must create directory objects.
What Is the Data Pump Import Utility?
Oracle Data Pump Import utility is a standalone programs that allows you to import data objects from an Oracle dump file set into Oracle database. Oracle dump file set is written in a proprietary binary format by the Data Pump Export utility.
Import can also be used to load a target database directly from a source database with no intervening dump files. This allows export and import operations to run concurrently, minimizing total elapsed time. This is known as a network import. Data Pump Import enables you to specify whether a job should move a subset of the data and metadata from the dump file set or the source database (in the case of a network import), as determined by the import mode. This is done using data filters and metadata filters, which are implemented through Import commands.
How To Invoke the Data Pump Export Utility?
The Data Pump Export utility is distributed as executable file called "expdp.exe". To invoke this utility, you should go to the "bin" directory of your Oracle server installation and run the "expdp" command. Here is tutorial exercise on how to invoke the export utility:
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN
>expdp help=y
Export: Release 10.2.0.1.0 -
The Data Pump export utility provides a mechanism for
transferring data objects between Oracle databases. The
utility is invoked with the following command:
Example: expdp scott/tiger DIRECTORY=dmpdir
DUMPFILE=scott.dmp
You can control how Export runs by entering the 'expdp'
command followed by various parameters:
Format: expdp KEYWORD=value or
KEYWORD=(value1,value2,...,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp
DIRECTORY=dmpdir
SCHEMAS=scott or TABLES=(T1:P1,T1:P2)
USERID must be the first parameter on the command line.
Keyword Description (Default)
------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name
COMPRESSION Reduce size of dumpfile contents where valid
keyword values are: (METADATA_ONLY) and NONE.
DIRECTORY Directory object to be used for dumpfiles
DUMPFILE List of destination dump files (expdat.dmp).
FLASHBACK_SCN SCN used to set session snapshot back to.
FULL Export entire database (N).
HELP Display Help messages (N).
......
How To Invoke the Data Pump Import Utitlity?
The Data Pump Import utility is distributed as executable file called "impdp.exe". To invoke this utility, you should go to the "bin" directory of your Oracle server installation and run the "impdp" command. Here is tutorial exercise on how to invoke the import utility:
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN
>impdp help=y
Import: Release 10.2.0.1.0 -
The Data Pump Import utility provides a mechanism for
transferring data objects between Oracle databases. The
utility is invoked with the following command:
Example: impdp scott/tiger DIRECTORY=dmpdir
DUMPFILE=scott.dmp
You can control how Import runs by entering the 'impdp'
command followed by various parameters.
Format: impdp KEYWORD=value or
KEYWORD=(value1,value2,...,valueN)
Example: impdp scott/tiger DIRECTORY=dmpdir
DUMPFILE=scott.dmp
USERID must be the first parameter on the command line.
Keyword Description (Default)
----------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name]
DIRECTORY Directory object to be used for dump, log, ...
DUMPFILE List of dumpfiles to import from (expdat.dmp)
FULL Import everything from source (Y).
HELP Display help messages (N).
......
What Are Data Pump Export and Import Modes?
Data pump export and import modes are used to determine the type and portions of database to be exported and imported. Oracle 10g supports 5 export and import modes:
- Full: Exports and imports a full database. Use the FULL parameter to specify this mode.
- Schema: Enables you to export and import all objects that belong to a schema. Use the SCHEMAS parameter to specify this mode. This is the default mode.
- Table: Enables you to export and import specific tables and partitions. Use the TABLES parameter to specify this mode.
- Tablespace: Enables a privileged user to move a set of tablespaces from one Oracle database to another. Use the TABLESPACES parameter to specify this mode.
- Tablespace: Enables a privileged user to move metadata from the tables within a specified set of tablespaces from one Oracle database to another. Use the TRANSPORT_TABLESPACES parameter to specify this mode.
How To Estimate Disk Space Needed for an Export Job?
If you just want to know how much disk space for the dump without actually exporting any data, you can use the ESTIMATE_ONLY=y parameter on the expdp command. The following tutorial exercise shows you how a system user wants to see the disk space estimates on a full database export:
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN
>expdp SYSTEM/fyicenter FULL=y ESTIMATE_ONLY=y
Starting "SYSTEM"."SYS_EXPORT_FULL_01": SYSTEM/**** FULL=y
ESTIMATE_ONLY=y
Estimate in progress using BLOCKS method...
Processing object DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. estimated "FLOWS_020100"."WWV_FLOW_PAGE_PLUGS" 42 MB
. estimated "FLOWS_020100"."WWV_FLOW_STEP_ITEMS" 27 MB
. estimated "FLOWS_020100"."WWV_FLOW_STEP_PROCESSING" 16 MB
......
. estimated "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB
. estimated "TSMSYS"."SRS$" 0 KB
Total estimation using BLOCKS method: 169.8 MB
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed
Now you know that you need 170 MB disk space to export the entire data base.
Oracle also records the screen output in a log file called export.log at \oraclexe\app\oracle\admin\XE\dpdump.
How To Do a Full Database Export?
If you are ready to do a full database export, you can use the FULL=y parameter on the expdp command, as shown in the following tutorial exercise:
>expdp SYSTEM/fyicenter FULL=y ESTIMATE_ONLY=y
Starting "SYSTEM"."SYS_EXPORT_FULL_01": SYSTEM/**** FULL=y
Estimate in progress using BLOCKS method...
Processing object DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 169.8 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
......
. . exported FLOWS_020100.WWV_FLOW_PAGE_PLUGS 32.51MB
. . exported FLOWS_020100.WWV_FLOW_STEP_ITEMS 21.68MB
. . exported FLOWS_020100.WWV_FLOW_STEP_PROCESSING 11.17MB
......
Master table "SYSTEM"."SYS_EXPORT_FULL_01" unloaded
**********************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
C:\ORACLEXE\APP\ORACLE\ADMIN\XE\DPDUMP\EXPDAT.DMP
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed
Where Is the Export Dump File Located?
If you are not specifying the dump directory and file name, the dump file will be stored in the default dump directory with the default file name. The tutorial exercise below tells you find what is your default dump directory and locate the dump file.
>sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> COL owner FORMAT A8;
SQL> COL directory_name FORMAT A16;
SQL> COL directory_path FORMAT A40;
SQL> SELECT * FROM dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
----- -------------- -------------------------------------
SYS DATA_PUMP_DIR \oraclexe\app\oracle\admin\XE\dpdump\
SYS TEST_DIR /oraclexe/test
SYS ORACLECLRDIR \oraclexe\app\oracle\product\10.2.0\
server\bin\clr
Obviously, the default dump directory is directory object defined to \oraclexe\app\oracle\admin\XE\dpdump\. If you go to that directory, you will find the full database dump file is called "expdat.dmp".
How To Export Your Own Schema?
If you have a non-system user account and you want to export all data objects in the schema associated with your account, you can use the "expdp" command with the SCHEMAS parameter. Running "expdp" command with a non-system user account requires a directory object granted to this user account. The following tutorial exercise shows you how to define a directory object and export a schema:
>mkdir \oraclexe\hr_dump
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN
>sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> CREATE DIRECTORY hr_dump AS '\oraclexe\hr_dump';
Directory created.
SQL> GRANT READ ON DIRECTORY hr_dump TO hr;
Grant succeeded.
SQL> GRANT WRITE ON DIRECTORY hr_dump TO hr;
Grant succeeded.
SQL> quit
>expdp hr/fyicenter SCHEMAS=hr
DIRECTORY=hr_dump DUMPFILE=schema.dmp LOGFILE=schema.log
Starting "HR"."SYS_EXPORT_SCHEMA_01":
hr/******** SCHEMAS=hr DIRECTORY=hr_dump
DUMPFILE=schema.dmp
LOGFILE=schema.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 960 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCH
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
......
. . exported "HR"."COUNTRIES" 6.085 KB 25 rows
. . exported "HR"."DEPARTMENTS" 6.632 KB 27 rows
. . exported "HR"."EMPLOYEES" 15.76 KB 107 rows
. . exported "HR"."EMPLOYEES_TEMP" 15.86 KB 107 rows
......
Master table "HR"."SYS_EXPORT_SCHEMA_01" loaded/unloaded
*********************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
C:\ORACLEXE\HR_DUMP\SCHEMA.DMP
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed
How To Export Several Tables Together?
If you don't want to export the entire schema and only want to export several tables only, you can use the "expdp" command with the "TABLES" parameter as shown in the following tutorial exercise:
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN
>expdp hr/fyicenter TABLES=employees,departments
DIRECTORY=hr_dump DUMPFILE=tables.dmp LOGFILE=tables.log
Starting "HR"."SYS_EXPORT_TABLE_01": hr/********
TABLES=employees,departments DIRECTORY=hr_dump
DUMPFILE=tables.dmp
LOGFILE=tables.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CON...
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTI...
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF...
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TAB...
. . exported "HR"."DEPARTMENTS" 6.632 KB 27 rows
. . exported "HR"."EMPLOYEES" 15.76 KB 107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" loaded/unloaded
***********************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
C:\ORACLEXE\HR_DUMP\TABLES.DMP
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed
What Happens If the Imported Table Already Exists?
If the import process tries to import a table that already exists, the Data Pump Import utility will return an error and skip this table. The following exercise shows you a good example:
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN
>impdp hr/fyicenter TABLES=employees
DIRECTORY=hr_dump DUMPFILE=tables.dmp LOGFILE=tables.log
Master table "HR"."SYS_IMPORT_TABLE_01" loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01": hr/** TABLES=employees
DIRECTORY=hr_dump DUMPFILE=tables.dmp LOGFILE=tables.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "HR"."EMPLOYEES" exists. All dependent
metadata and data will be skipped due to table_exists_action
of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
......
How To Import One Table Back from a Dump File?
If you only want to import one table back to the database, you can use a dump file that was created by full export, schema export or a table export. The following tutorial exercise shows you how to import the "fyi_links" table from a dump file created by a schema export:
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN
>sqlplus /nolog
SQL> connect HR/fyicenter
SQL> DROP TABLE fyi_links;
Table dropped.
SQL> exit;
>impdp hr/fyicenter TABLES=fyi_links DIRECTORY=hr_dump
DUMPFILE=schema.dmp LOGFILE=tables.log
Master table "HR"."SYS_IMPORT_TABLE_01" loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01": hr/** TABLES=fyi_links
DIRECTORY=hr_dump DUMPFILE=schema.dmp LOGFILE=tables.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."FYI_LINKS" 6.375 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CON...
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTI...
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TAB...
Job "HR"."SYS_IMPORT_TABLE_01" successfully completed.
What Are the Original Export and Import Utilities?
Oracle original Export and Import utilities are standalone programs that provide you a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.
When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an export dump file. The Import utility reads the object definitions and table data from the dump file.
An export file is an Oracle binary-format dump file that is typically located on disk or tape. The dump files can be transferred using FTP or physically transported (in the case of tape) to a different site. The files can then be used with the Import utility to transfer data between databases that are on systems not connected through a network. The files can also be used as backups in addition to normal backup procedures.
Export and Import utilities are now being replaced by Data Pump Export and Import utilities in Oracle 10g. But you can still use them.
How To Invoke the Original Export Import Utilities?
If you really want to run the original export import utilities, you can still go to "bin" directory of the Oracle server path and run the "exp" or "imp" command. The tutorial exercise below tells you how to run the export and import utilities in help modes:
>cd \oraclexe\app\oracle\product\10.2.0\server\BIN
>exp help=y
You can let Export prompt you for parameters by entering the
EXP command followed by your username/password:
Example: EXP SCOTT/TIGER
Or, you can control how Export runs by entering the EXP
command followed by various arguments. To specify parameters,
you use:
Format: EXP KEYWORD=value or
KEYWORD=(value1,value2,...,valueN)
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned
table
......
>imp help=y
......
Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges
A collection of 23 FAQs on Oracle user account, schema and privileges. Clear answers are provided with tutorial exercises on creating user accounts, granting privileges for session connections, granting privileges for creating tables and inserting rows. Topics included in this FAQ are:
- What Is a User Account?
- What Is the Relation of a User Account and a Schema?
- What Is a User Role?
- What Are the System Predefined User Roles?
- What Are Internal User Account?
- How To Connect to the Server with User Account: SYS?
- How To Use Windows User to Connect to the Server?
- How To List All User Accounts?
- How To Create a New User Account?
- How To Change User Password?
- How To Delete a User Account?
- What Privilege Is Needed for a User to Connect to Oracle Server?
- How To Grant CREATE SESSION Privilege to a User?
- How To Revoke CREATE SESSION Privilege from a User?
- How To Lock and Unlock a User Account?
- What Privilege Is Needed for a User to Creat Tables?
- How To Assign a Tablespace to a Users?
- What Privilege Is Needed for a User to Creat Views?
- What Privilege Is Needed for a User to Creat Indexes?
- What Privilege Is Needed for a User to Query Tables in Another Schema?
- What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?
- What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?
- How To Find Out What Privileges a User Currently Has?
What Is a User Account?
A user account is identified by a user name and defines the user's attributes, including the following:
- Password for database authentication
- Privileges and roles
- Default tablespace for database objects
- Default temporary tablespace for query processing work space
What Is the Relation of a User Account and a Schema?
User accounts and schemas have a one-to-one relation. When you create a user, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user.
What Is a User Role?
A user role is a group of privileges. Privileges are assigned to users through user roles. You create new roles, grant privileges to the roles, and then grant roles to users.
What Are the System Predefined User Roles?
Oracle 10g XE comes with 3 predefined roles:
- CONNECT - Enables a user to connect to the database. Grant this role to any user or application that needs database access.
- RESOURCE - Enables a user to create certain types of schema objects in his own schema. Grant this role only to developers and to other users that must create schema objects. This role grants a subset of the create object system privileges.
- DBA - Enables a user to perform most administrative functions, including creating users and granting privileges; creating and granting roles; creating and dropping schema objects in other users' schemas; and more. It grants all system privileges, but does not include the privileges to start up or shut down the database. It is by default granted to user SYSTEM.
What Are Internal User Account?
An internal user account is a system predefined user account. Oracle 10g XE comes with a number of internal accounts:
- SYSTEM - This is the user account that you log in with to perform all administrative functions other than starting up and shutting down the database. SYSTEM is automatically created when you install the server. It's password is the one you specified during the installation process.
- SYS - This is another user account automatically created when you install the server. It's password is the one you specified during the installation process. All base tables and views for the database data dictionary are stored in the SYS schema. So avoid log in as user SYS as much as possible to reduce the risk of damaging those important data objects. User SYSTEM is preferred for all administrative tasks except starting up and shutting down.
- Other internal user accounts - Other special user accounts are predefined for special purposes. For example, CTXSYS is a special user account used by the Oracle Text product.
How To Connect to the Server with User Account: SYS?
SYS is a very special user account. It has been associated with the highest privilege call SYSDBA. Normally, you should not connect to the server with SYS. But if you want to use it, you need to use a special connect command:
>cd (OracleXE home directory)
>.\bin\sqlplus /nolog
SQL> connect SYS/fyicenter AS SYSDBA
Connected.
SQL> quit
Note that the "/nolog" option is used to start SQL*Plus without login immediately. A special form of the "connect" command is used to include the user name, password, and the privilege in the same line.
You can not log in with SYS without SYSDBA privilege.
How To Use Windows User to Connect to the Server?
During the installation process, 10g XE will create a special Windows user group called ORA_DBA, and put your Windows user into this group. Any Windows users in this group can be connected to Oracle server with SYSDBA privilege without any Oracle server user account. This process is called connecting the server as SYSDBA with OS Authentication. Here is how to do this with a special form of the "connect" command:
(Log in with the same user you used to install 10g XE)
>cd (OracleXE home directory)
>.\bin\startdb
>.\bin\sqlplus /nolog
SQL> connect / AS SYSDBA
Connected.
SQL> quit
So if "connect" is used without user name and password, the current Windows user will be trusted if he/she is in the ORA_DBA user group on the Windows system.
How To List All User Accounts?
User accounts can be accessed through a system view called ALL_USERS. A simple SELECT statement can be used to get a list of all user accounts. Try the following script:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
Connected.
SQL> SELECT * FROM ALL_USERS;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
FLOWS_020100 35 07-FEB-06
FLOWS_FILES 34 07-FEB-06
HR 33 07-FEB-06
MDSYS 32 07-FEB-06
ANONYMOUS 28 07-FEB-06
XDB 27 07-FEB-06
CTXSYS 25 07-FEB-06
DBSNMP 23 07-FEB-06
TSMSYS 20 07-FEB-06
DIP 18 07-FEB-06
OUTLN 11 07-FEB-06
SYSTEM 5 07-FEB-06
SYS 0 07-FEB-06
How To Create a New User Account?
If you want to create a new user account, you can log in as SYSTEM and use the CREATE USER command as shown in the following example:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
Connected.
SQL> CREATE USER DEV IDENTIFIED BY developer ACCOUNT UNLOCK;
User created.
Note that CREATE is a SQL statement, so you need to terminate it with ";". This command creates a user called "DEV", with a password of "developer". You can test this account by log in with DEV from SQL*Plus.
How To Change User Password?
If you want to change a user's password, you can log in as SYSTEM and use the ALTER USER command as shown in the following example:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
Connected.
SQL> ALTER USER DEV IDENTIFIED BY beginner;
User altered.
Note that ALTER is SQL statement, so you need to terminate it with ";". This command resets DEV's password to "beginner".
How To Delete a User Account?
If you want to delete a user account and its associated schema, you can log in as SYSTEM and use the DROP USER command as shown in the following example:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
Connected.
SQL> DROP USER DEV CASCADE;
User dropped.
SQL> CREATE USER DEV IDENTIFIED BY developer ACCOUNT UNLOCK;
User created.
Note that CASCADE tells the server drop the associated schema.
What Privilege Is Needed for a User to Connect to Oracle Server?
Oracle deny connection to users who has no CREATE SESSION privilege. Try the following tutorial exercise, you will find out how Oracle denies connection:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> CREATE USER DEV IDENTIFIED BY developer ACCOUNT UNLOCK;
User created.
SQL> disconnect
SQL> CONNECT DEV/developer
ORA-01045: user DEV lacks CREATE SESSION privilege;
logon denied
Oracle error message is pretty clear.
How To Grant CREATE SESSION Privilege to a User?
If you want give a user the CREATE SESSION privilege, you can use the GRANT command. The following tutorial exercise shows you how to grant DEV the privilege to connect to the server:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> GRANT CREATE SESSION TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
Connected.
How To Revoke CREATE SESSION Privilege from a User?
If you take away the CREATE SESSION privilege from a user, you can use the REVOKE command as shown in the following example script:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> REVOKE CREATE SESSION FROM dev;
Revoke succeeded.
SQL> GRANT CREATE SESSION TO dev;
Grant succeeded.
This script restored the CREATE SESSION privilege to user "dev", so you can continue other example scripts below.
How To Lock and Unlock a User Account?
If you want to lock a user account for a short period of time, and unlock it later, you can use the ALTER USER ... ACCOUNT command. The following sample script shows how to use this command:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> ALTER USER dev ACCOUNT LOCK;
User altered.
SQL> disconnect
SQL> CONNECT DEV/developer
ORA-28000: the account is locked
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> ALTER USER dev ACCOUNT UNLOCK;
User altered.
SQL> disconnect
SQL> CONNECT DEV/developer
Connected.
What Privilege Is Needed for a User to Create Tables?
To be able to create tables in a user's own schema, the user needs to have the CREATE TABLE privilege, or the CREATE ANY TABLE privilege, which is more powerful, and allows the user to create tables in other user's schema. The following tutorial exercise gives you a good example on CREATE TABLE privilege:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> CREATE TABLE fyi (id NUMBER);
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT CREATE TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> CREATE TABLE fyi (id NUMBER);
ORA-01950: no privileges on tablespace 'SYSTEM'
The above error message tells that user "dev" is not allowed to use the tablespace "SYSTEM". See the next question for answers.
How To Assign a Tablespace to a Users?
When you create a new user, Oracle will assign the SYSTEM tablespace to the user by default. If you want to change this, you can assign a different table space to a user using the ALTER USER command. The following tutorial exercise changes user dev's default tablespace, and assigns 4MB of space to dev:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> ALTER USER dev DEFAULT TABLESPACE USERS;
User altered.
SQL> ALTER USER dev QUOTA 4M ON USERS;
User altered.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> CREATE TABLE fyi (id NUMBER);
Table created.
SQL> DROP TABLE fyi;
Table dropped.
SQL> CREATE TABLE fyi (id NUMBER);
Table created.
As you can see, "dev" can create and drop tables now. You can also let "dev" to create tables in any tablespace without any restriction by granting him the UNLIMITED TABLESPACE system privilege.
What Privilege Is Needed for a User to Create Views?
To be able to create views in a user's own schema, the user needs to have the CREATE VIEW privilege, or the CREATE ANY VIEW privilege, which is more powerful, and allows the user to create views in other user's schema. The following tutorial exercise gives you a good example on CREATE VIEW privilege:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT CREATE VIEW TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
View created.
SQL> DROP VIEW fyi_view;
View dropped.
SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
View created.
As you can see, "dev" can create and drop views now.
What Privilege Is Needed for a User to Create Indexes?
For a user to create indexes, he/she needs the same privilege as the creating tables. Just make sure he/she has the CREATE TABLE privilege. The following tutorial exercise gives you a good example on creating view privilege:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> GRANT CREATE TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> CREATE INDEX fyi_index ON fyi(id);
Index created.
SQL> DROP INDEX fyi_index;
Index dropped.
SQL> CREATE INDEX fyi_index ON fyi(id);
Index created.
What Privilege Is Needed for a User to Query Tables in Another Schema?
For a user to run queries (SELECT statements) on tables of someone else's schema, he/she needs the SELECT ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to query tables in "hr" schema:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> SELECT COUNT(*) FROM hr.employees;
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT SELECT ANY TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> SELECT COUNT(*) FROM hr.employees;
COUNT(*)
----------
107
As you can see, "dev" can query tables in any schema now.
You also need to remember that table name must be prefixed with the schema name (same as owner user name).
What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?
For a user to insert rows into tables of someone else's schema, he/she needs the INSERT ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to insert rows in "hr" schema:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> INSERT INTO hr.jobs
VALUES ('DV.FYI', 'Dev FYI Consultant', 7700, 8800);
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT INSERT ANY TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> INSERT INTO hr.jobs
VALUES ('DV.FYI', 'Dev FYI Consultant', 7700, 8800);
1 row created.
As you can see, "dev" can insert rows in any schema now. But you should be careful when giving this privilege to a regular developer.
What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?
For a user to delete rows from tables of someone else's schema, he/she needs the DELETE ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to delete rows in "hr" schema:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.FYI';
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT DELETE ANY TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.FYI';
1 row deleted.
As you can see, "dev" can delete rows in any schema now. But you should be careful when giving this privilege to a regular developer.
How To Find Out What Privileges a User Currently Has?
Privileges granted to users are listed in two system views: DBA_SYS_PRIVS, and USER_SYS_PRIVS. You can find out what privileges a user currently has by running a query on those views as shown in the tutorial exercise below:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> SELECT username, privilege FROM USER_SYS_PRIVS;
USERNAME PRIVILEGE
------------------------------ ----------------------
DEV SELECT ANY TABLE
DEV INSERT ANY TABLE
DEV CREATE SESSION
DEV CREATE VIEW
DEV DELETE ANY TABLE
DEV CREATE ANY TABLE
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT DELETE ANY TABLE TO dev;
Grant succeeded.
SQL> SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'HR';
GRANTEE PRIVILEGE
------------------------------ -----------------------
HR CREATE VIEW
HR UNLIMITED TABLESPACE
HR DEBUG CONNECT SESSION
HR CREATE DATABASE LINK
HR CREATE SEQUENCE
HR CREATE SESSION
HR DEBUG ANY PROCEDURE
HR ALTER SESSION
HR CREATE SYNONYM
Looks like "hr" has move privileges than "dev".
No comments:
Post a Comment