SQL> select username,account_status from dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SYS OPEN
SYSTEM OPEN
OUTLN OPEN
DBSNMP OPEN
PS OPEN
SYSADM OPEN
PEOPLE OPEN
7 rows selected.
SQL> create user scott identified by tiger;
User created.
SQL> select username,account_status from dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SYS OPEN
SYSTEM OPEN
OUTLN OPEN
DBSNMP OPEN
PS OPEN
SYSADM OPEN
PEOPLE OPEN
SCOTT OPEN
8 rows selected.
SQL> desc dba_profiles;
Name Null? Type
----------------------------------------- -------- ----------------------------
PROFILE NOT NULL VARCHAR2(30)
RESOURCE_NAME NOT NULL VARCHAR2(32)
RESOURCE_TYPE VARCHAR2(8)
LIMIT VARCHAR2(40)
SQL> set linesize 200
SQL> select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
16 rows selected.
SQL> create profile P1 limit
2 sessions_per_user 2;
Profile created.
SQL> alter user scott profile p1;
User altered.
SQL> select username,profile from dba_users;
USERNAME PROFILE
------------------------------ ------------------------------
SYS DEFAULT
SYSTEM DEFAULT
OUTLN DEFAULT
DBSNMP DEFAULT
PS DEFAULT
SYSADM DEFAULT
PEOPLE DEFAULT
SCOTT P1
8 rows selected.
SQL> conn scott/tiger;
ERROR:
ORA-01045: user SCOTT lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn /as sysdba
Connected.
SQL> grant connect,resource to scott;
Grant succeeded.
SQL> conn scott/tiger;
Connected.
SQL> show user
USER is "SCOTT"
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
PRIVILEGE
----------------------------------------
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
14 rows selected.
SQL> conn /as sysdba
Connected.
SQL> conn scott/tigeer
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn scott/tigeerr
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn scott/tigeerr
ERROR:
ORA-28000: the account is locked
SQL> conn /as sysdba
Connected.
SQL> select username,account_status from dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SYS OPEN
SYSTEM OPEN
OUTLN OPEN
DBSNMP OPEN
PS OPEN
SYSADM OPEN
PEOPLE OPEN
SCOTT LOCKED(TIMED)
8 rows selected.
SQL> alter user scott account unlock;
User altered.
SQL> select username,account_status from dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SYS OPEN
SYSTEM OPEN
OUTLN OPEN
DBSNMP OPEN
PS OPEN
SYSADM OPEN
PEOPLE OPEN
SCOTT OPEN
8 rows selected.
SQL> conn /as sysdba
Connected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/9.2.0.4.0/dbs/arch
Oldest online log sequence 129
Next log sequence to archive 130
Current log sequence 131
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
=================================================================================
SQL> desc dba_data_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
SQL> select file_id,file_name,bytes/1024/1024/1024 from dba_data_files;
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024/1024
--------------------
1
/u01/PROD/system01.dbf
1.953125
2
/u01/PROD/psundots01.dbf
10.8740234
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024/1024
--------------------
3
/u01/PROD/psdefault.dbf
.09765625
4
/u01/PROD/aaapp.dbf
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024/1024
--------------------
.009765625
5
/u01/PROD/aalarge.dbf
.004882813
6
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024/1024
--------------------
/u01/PROD/adapp.dbf
.0390625
7
/u01/PROD/amapp.dbf
.000976563
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024/1024
--------------------
8
/u01/PROD/avapp.dbf
.012695313
9
/u01/PROD/bdapp.dbf
.000976563
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024/1024
--------------------
10
/u01/PROD/bnapp.dbf
.048828125
11
/u01/PROD/bnlarge.dbf
SQL> !
[oracle@sudhi dbs]$ cd /sudhi/export
[oracle@sudhi export]$ pwd
/sudhi/export
[oracle@sudhi export]$ exp scott/tiger file=exp_scott.dmp log=exp_scott.log owner=scott
Export: Release 9.2.0.4.0 - Production on Wed Oct 26 02:18:51 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P15 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table T1 0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[oracle@sudhi export]$ exp scott/tiger file=exp_scott.dmp log=exp_scott.log owner=scott
Export: Release 9.2.0.4.0 - Production on Wed Oct 26 02:19:11 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P15 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table T1 2 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[oracle@sudhi export]$ exp scott/tiger file=exp_scott.dmp log=exp_scott.log tables=t1
Export: Release 9.2.0.4.0 - Production on Wed Oct 26 02:20:05 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P15 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table T1 2 rows exported
Export terminated successfully without warnings.
[oracle@sudhi export]$ expdp scott/tiger directory=expdp dumpfile=expdp_scott.dmp logfile=expdp_scott.log schemas=scott
bash: expdp: command not found
[oracle@sudhi export]$ imp scott/tiger file=exp__scott.dmp log=imp_scott.log fromuser=scott touser=scott
Import: Release 9.2.0.4.0 - Production on Wed Oct 26 02:26:27 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
IMP-00002: failed to open exp__scott.dmp for read
Import file: expdat.dmp > imp scott/tiger file=exp_scott.dmp log=imp_scott.log fromuser=scott touser=scott
IMP-00002: failed to open imp scott/tiger file=exp_scott.dmp log=imp_scott.log fromuser=scott touser=scott for read
Import file: expdat.dmp >
IMP-00002: failed to open expdat.dmp for read
Import file: expdat.dmp >
Import terminated successfully with warnings.
[oracle@sudhi export]$ exit
SQL> !
[oracle@sudhi dbs]$ cd /sudhi
[oracle@sudhi sudhi]$ ls -ltr
total 10384
drwx------ 2 root root 16384 Oct 26 00:26 lost+found
-rw-r----- 1 oracle oinstall 98304 Oct 26 01:28 bak7.bak
-rw-r----- 1 oracle oinstall 10493952 Oct 26 01:59 aaapp.dbf
drwxr-xr-x 2 oracle oinstall 4096 Oct 26 02:26 export
[oracle@sudhi sudhi]$ chmod 777 export
[oracle@sudhi sudhi]$ ls -ltr
total 10384
drwx------ 2 root root 16384 Oct 26 00:26 lost+found
-rw-r----- 1 oracle oinstall 98304 Oct 26 01:28 bak7.bak
-rw-r----- 1 oracle oinstall 10493952 Oct 26 01:59 aaapp.dbf
drwxrwxrwx 2 oracle oinstall 4096 Oct 26 02:26 export
[oracle@sudhi sudhi]$ exit
exit
SQL> !
[oracle@sudhi dbs]$ cd /sudhi/export
[oracle@sudhi export]$ ls -ltr
total 24
-rw-r--r-- 1 oracle oinstall 499 Oct 26 02:20 exp_scott.log
-rw-r--r-- 1 oracle oinstall 16384 Oct 26 02:20 exp_scott.dmp
-rw-r--r-- 1 oracle oinstall 431 Oct 26 02:27 imp_scott.log
[oracle@sudhi export]$ imp scott/tiger file=exp_scott.dmp log=imp_scott.log fromuser=scott touser=scott
Import: Release 9.2.0.4.0 - Production on Wed Oct 26 02:28:28 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P15 character set (possible charset conversion)
. . importing table "T1" 2 rows imported
Import terminated successfully without warnings.
[oracle@sudhi export]$ imp scott/tiger file=exp_scott.dmp log=imp1_scott.log fromuser=scott touser=sudhir
Import: Release 9.2.0.4.0 - Production on Wed Oct 26 02:30:55 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P15 character set (possible charset conversion)
IMP-00007: must be a DBA to import objects to another user's account
IMP-00000: Import terminated unsuccessfully
[oracle@sudhi export]$ imp file=exp_scott.dmp log=imp1_scott.log fromuser=scott touser=sudhir
Import: Release 9.2.0.4.0 - Production on Wed Oct 26 02:32:00 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by SCOTT, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P15 character set (possible charset conversion)
. importing SCOTT's objects into SUDHIR
. . importing table "T1" 2 rows imported
Import terminated successfully without warnings.
[oracle@sudhi export]$
[oracle@sudhi oracle]$ cd $ORACLE_HOME/dbs
[oracle@sudhi dbs]$ pwd
/u01/app/oracle/product/9.2.0.4.0/dbs
HOT BACKUP
==============
SQL> alter tablespace aaapp begin backup;
SQL>!
[oracle@sudhi PROD]$ cp aaapp.dbf /sudhi/aaapp.dbf
[oracle@sudhi PROD]$ exit
exit
SQL> alter tablespace aaapp end backup;
Tablespace altered.
SQL> desc v$backup;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
STATUS VARCHAR2(18)
CHANGE# NUMBER
TIME DATE
SQL> select * from v$backup where file#=4;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
4 NOT ACTIVE 9248830 26-OCT-11
SQL> alter tablespace aaapp begin backup;
Tablespace altered.
SQL> select * from v$backup where file#=4;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
4 ACTIVE 9248950 26-OCT-11
SQL> alter tablespace aaapp end backup;
Tablespace altered.
SQL> select * from v$backup where file#=4;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
4 NOT ACTIVE 9248950 26-OCT-11
SQL> select file_name from dba_data_files where tablespace_name='AAAPP';
FILE_NAME
--------------------------------------------------------------------------------
/u01/PROD/aaapp.dbf
SQL>
