Database Administration(oracle 10g)
Before any other concept, the first thing you need to be cleared is with ARCHITECTURE of the database.
under database administration there are many concepts which need to be discussed before implementation's.these includes:
1.Auditing of Database :
Auditing is the monitoring and recording of selected user database actions. It can be based on individual actions, such as the type of SQL statement executed, or on combinations of factors that can include user name, application, time, and so on. Security policies can trigger auditing when specified elements in an Oracle database are accessed or altered, including the contents within a specified object
Auditing is typically used to:
System altered.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1291988 bytes
Variable Size 234883372 bytes
Database Buffers 360710144 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string D:\ORACLE\PRODUCT\10.2.0\ADMIN
\RAC\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB, EXTENDED
SQL>
SQL> audit all by scott by access;
Audit succeeded.
SQL> select * from dba_audit_trail;
no rows selected
SQL> conn scott/tiger
Connected.
SQL>
SQL> create table test(id number);
Table created.
SQL> conn sys/sys as sysdba
Connected.
SQL>
SQL> select username,TIMESTAMP,SQL_TEXT from dba_audit_trail;
USERNAME TIMESTAMP SQL_TEXT
------------------------------ --------- --------------------------------------
SCOTT 28-JAN-11
SCOTT 28-JAN-11 create table test(id number)
2. import/export of database
The Export and Import utilities provide 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 dump files can only be read by the Oracle Import utility. The version of the Import utility cannot be earlier than the version of the Export utility used to create the dump file.
working queries of import/export :-
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS ADMIN_DIR
/ade/aime_10.2_lnx_push/oracle/md/admin
SYS SUBDIR
/oracle/app/product/10.2.0/db_1/demo/schema/order_entry//2002/Sep
SYS DATA_FILE_DIR
/oracle/app/product/10.2.0/db_1/demo/schema/sales_history/
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS WORK_DIR
/ade/aime_10.2_lnx_push/oracle/work
SYS LOG_FILE_DIR
/oracle/app/product/10.2.0/db_1/demo/schema/log/
SYS MEDIA_DIR
/oracle/app/product/10.2.0/db_1/demo/schema/product_media/
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS XMLDIR
/oracle/app/product/10.2.0/db_1/demo/schema/order_entry/
SYS DATA_PUMP_DIR
/oracle/app/product/10.2.0/db_1/rdbms/log/
8 rows selected.
SQL>
SQL>
SQL> define
DEFINE _DATE = "18-FEB-11" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "gedit" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)
SQL>
SQL>
SQL>
SQL> host
[oracle@localhost ~]$ mkdir /oracle/dir
[oracle@localhost ~]$ exit
exit
SQL>
SQL>
SQL> create directory dir as '/oracle/dir';
Directory created.
SQL>
SQL> grant read,write on directory dir to public;
Grant succeeded.
SQL>
SQL> grant exp_full_database,imp_full_database to system;
Grant succeeded.
SQL>
SQL>
SQL> --table level export and import
SQL>
SQL>
SQL> host
[oracle@localhost ~]$ expdp scott/tiger tables=emp,dept directory=dir dumpfile=ed.dmp logfile=ed.log
Export: Release 10.2.0.1.0 - Production on Friday, 18 February, 2011 9:16:46
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** tables=emp,dept directory=dir dumpfile=ed.dmp logfile=ed.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/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/oracle/dir/ed.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 09:17:03
[oracle@localhost ~]$ exit
exit
SQL> drop table scott.emp purge;
Table dropped.
SQL> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> host
[oracle@localhost ~]$ impdp scott/tiger tables=emp dumpfile=ed.dmp directory=dir logfile=emp.log
Import: Release 10.2.0.1.0 - Production on Friday, 18 February, 2011 9:18:43
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** tables=emp dumpfile=ed.dmp directory=dir logfile=emp.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP" 7.820 KB 14 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 09:18:46
[oracle@localhost ~]$ exit
exit
SQL> select * From scott.emp
2 where comm is not null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
2.Schema or User level export and import
SQL> --schema level export
SQL>
SQL>
SQL> host
[oracle@localhost ~]$ expdp schemas=scott directory=dir dumpfile=scott.dmp logfile=scott.log
Export: Release 10.2.0.1.0 - Production on Friday, 18 February, 2011 9:21:43
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Username: system/sys
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=scott directory=dir dumpfile=scott.dmp logfile=scott.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/oracle/dir/scott.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:22:08
[oracle@localhost ~]$ exit
exit
SQL> drop user scott cascade;
User dropped.
SQL> conn scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL>
SQL> host
[oracle@localhost ~]$ impdp schemas=scott dumpfile=scott.dmp directory=dir
Import: Release 10.2.0.1.0 - Production on Friday, 18 February, 2011 9:23:07
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Username: system/sys
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** schemas=scott dumpfile=scott.dmp directory=dir
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT" 5.656 KB 4 rows
. . imported "SCOTT"."EMP" 7.820 KB 14 rows
. . imported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . imported "SCOTT"."BONUS" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 09:23:13
[oracle@localhost ~]$ exit
exit
SQL> conn scott/tiger
Connected.
SQL>
SQL> select * From tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
BONUS TABLE
SALGRADE TABLE
EMP TABLE
3.Exporting and Importing full database:-
]$ expdp full=y dumpfile=full.dmp directory=dir logfile=full.log
]$ impdp full=y dumpfile=full.dmp directory=dir logfile=full.log
More database concepts will be shared soon. hope for good feedback 😀
under database administration there are many concepts which need to be discussed before implementation's.these includes:
1.Auditing of Database :
Auditing is the monitoring and recording of selected user database actions. It can be based on individual actions, such as the type of SQL statement executed, or on combinations of factors that can include user name, application, time, and so on. Security policies can trigger auditing when specified elements in an Oracle database are accessed or altered, including the contents within a specified object
Auditing is typically used to:
- Enable future accountability for current actions taken in a particular schema, table, or row, or affecting specific content
- Deter users (or others) from inappropriate actions based on that accountability
- Investigate suspicious activity
For example, if some user is deleting data from tables, then the security administrator might decide to audit all connections to the database and all successful and unsuccessful deletions of rows from all tables in the database.
- Notify an auditor that an unauthorized user is manipulating or deleting data and that the user has more privileges than expected which can lead to reassessing user authorizations
- Monitor and gather data about specific database activities
For example, the database administrator can gather statistics about which tables are being updated, how many logical I/Os are performed, or how many concurrent users connect at peak times.
- Detect problems with an authorization or access control implementation
System altered.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1291988 bytes
Variable Size 234883372 bytes
Database Buffers 360710144 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string D:\ORACLE\PRODUCT\10.2.0\ADMIN
\RAC\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB, EXTENDED
SQL>
SQL> audit all by scott by access;
Audit succeeded.
SQL> select * from dba_audit_trail;
no rows selected
SQL> conn scott/tiger
Connected.
SQL>
SQL> create table test(id number);
Table created.
SQL> conn sys/sys as sysdba
Connected.
SQL>
SQL> select username,TIMESTAMP,SQL_TEXT from dba_audit_trail;
USERNAME TIMESTAMP SQL_TEXT
------------------------------ --------- --------------------------------------
SCOTT 28-JAN-11
SCOTT 28-JAN-11 create table test(id number)
2. import/export of database
The Export and Import utilities provide 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 dump files can only be read by the Oracle Import utility. The version of the Import utility cannot be earlier than the version of the Export utility used to create the dump file.
working queries of import/export :-
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS ADMIN_DIR
/ade/aime_10.2_lnx_push/oracle/md/admin
SYS SUBDIR
/oracle/app/product/10.2.0/db_1/demo/schema/order_entry//2002/Sep
SYS DATA_FILE_DIR
/oracle/app/product/10.2.0/db_1/demo/schema/sales_history/
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS WORK_DIR
/ade/aime_10.2_lnx_push/oracle/work
SYS LOG_FILE_DIR
/oracle/app/product/10.2.0/db_1/demo/schema/log/
SYS MEDIA_DIR
/oracle/app/product/10.2.0/db_1/demo/schema/product_media/
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS XMLDIR
/oracle/app/product/10.2.0/db_1/demo/schema/order_entry/
SYS DATA_PUMP_DIR
/oracle/app/product/10.2.0/db_1/rdbms/log/
8 rows selected.
SQL>
SQL>
SQL> define
DEFINE _DATE = "18-FEB-11" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "gedit" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)
SQL>
SQL>
SQL>
SQL> host
[oracle@localhost ~]$ mkdir /oracle/dir
[oracle@localhost ~]$ exit
exit
SQL>
SQL>
SQL> create directory dir as '/oracle/dir';
Directory created.
SQL>
SQL> grant read,write on directory dir to public;
Grant succeeded.
SQL>
SQL> grant exp_full_database,imp_full_database to system;
Grant succeeded.
SQL>
SQL>
SQL> --table level export and import
SQL>
SQL>
SQL> host
[oracle@localhost ~]$ expdp scott/tiger tables=emp,dept directory=dir dumpfile=ed.dmp logfile=ed.log
Export: Release 10.2.0.1.0 - Production on Friday, 18 February, 2011 9:16:46
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** tables=emp,dept directory=dir dumpfile=ed.dmp logfile=ed.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/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/oracle/dir/ed.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 09:17:03
[oracle@localhost ~]$ exit
exit
SQL> drop table scott.emp purge;
Table dropped.
SQL> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> host
[oracle@localhost ~]$ impdp scott/tiger tables=emp dumpfile=ed.dmp directory=dir logfile=emp.log
Import: Release 10.2.0.1.0 - Production on Friday, 18 February, 2011 9:18:43
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** tables=emp dumpfile=ed.dmp directory=dir logfile=emp.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP" 7.820 KB 14 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 09:18:46
[oracle@localhost ~]$ exit
exit
SQL> select * From scott.emp
2 where comm is not null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
2.Schema or User level export and import
SQL> --schema level export
SQL>
SQL>
SQL> host
[oracle@localhost ~]$ expdp schemas=scott directory=dir dumpfile=scott.dmp logfile=scott.log
Export: Release 10.2.0.1.0 - Production on Friday, 18 February, 2011 9:21:43
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Username: system/sys
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=scott directory=dir dumpfile=scott.dmp logfile=scott.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/oracle/dir/scott.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:22:08
[oracle@localhost ~]$ exit
exit
SQL> drop user scott cascade;
User dropped.
SQL> conn scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL>
SQL> host
[oracle@localhost ~]$ impdp schemas=scott dumpfile=scott.dmp directory=dir
Import: Release 10.2.0.1.0 - Production on Friday, 18 February, 2011 9:23:07
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Username: system/sys
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** schemas=scott dumpfile=scott.dmp directory=dir
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT" 5.656 KB 4 rows
. . imported "SCOTT"."EMP" 7.820 KB 14 rows
. . imported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . imported "SCOTT"."BONUS" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 09:23:13
[oracle@localhost ~]$ exit
exit
SQL> conn scott/tiger
Connected.
SQL>
SQL> select * From tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
BONUS TABLE
SALGRADE TABLE
EMP TABLE
3.Exporting and Importing full database:-
]$ expdp full=y dumpfile=full.dmp directory=dir logfile=full.log
]$ impdp full=y dumpfile=full.dmp directory=dir logfile=full.log
More database concepts will be shared soon. hope for good feedback 😀
Replies
-
Jagroop Singh Gillfor any type query , do reply. hope i will help you
by: Jagroop (RIMT-IET, Mandigobindgarh, Punjab)
You are reading an archived discussion.
Related Posts
Dear Sir,
I am a graduate of 2012 in Mechanical Engineering. From past two years I am working in Pvt Company and my salary package is also good (4, 32,000...
I would like to introduce myself as Software Engineer willing to go one step beyond coding. Hope to explore a lot
Link to schedule : https://events.linuxfoundation.org/events/linuxcon-north-america/program/schedule
​
You're interested in which topic talks ? Share! Discuss!
Whenever we head outside our houses most of us carry our phones, wallets or bags and keys. The next time though you can forget about your keys as the new...
HI
As a part of my thesis i want to gain detailed knowledge on RC box girder bridges, from historic point of view to complete design process both in simulation...