</u2/oracle/product/11.2.0/dbhome_1> export ORACLE_SID=topprod <<设置要登陆的数据库的代号

</u2/oracle/product/11.2.0/dbhome_1> sqlplus / as sysdba <<SQLPLUS以系统管理员登陆

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 5 13:30:14 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user <<确认用户

USER is “SYS”

SQL> exit <<退出SQLPLUS

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

</u1/usr/oracle> export ORACLE_SID=topprod <<设置要启动的数据库的代号

</u1/usr/oracle> sqlplus / as sysdba <<SQLPLUS以系统管理员登陆

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 6 11:33:11 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup; <<启动数据库

ORACLE instance started.

Total System Global Area 267227136 bytes

Fixed Size 2225632 bytes

Variable Size 167774752 bytes

Database Buffers 92274688 bytes

Redo Buffers 4952064 bytes

Database mounted.

Database opened.

SQL> exit <<启动完成后退出

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

</u1/usr/oracle> export ORACLE_SID=gp510

</u1/usr/oracle> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 6 11:31:11 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate; <<关闭数据库

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit <<完成关闭后退出

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

<topprod:/u1/topprod/tiptop> sqlplus system/manager@topprod <<以system用户登陆数据库

SQL> @q_tbsFREE; <<执行查看表空间使用情况的脚本

TABLESPACE_NAME BYTES_USED BYTES_FREE LARGEST PERCENT_USED

—————————— ———- ———- ———- ————

SYSTEM 859832320 7143424 6291456 99.17

SYSAUX 713031680 43581440 39911424 93.89

DBS1 4613734400 354418688 126877696 92.32

BLOBDBS1 314572800 49610752 49283072 84.23

USERS 5242880 4194304 4194304 20

RPTDBS1 2097152000 1941962752 1934229504 7.4

UNDOTBS1 319815680 301727744 270532608 5.66

TEMPTABS 2097152000 2063400960 1765212160 1.61

TEMP02 524288000 523239424 523239424 .2

SQL> exit <<退出SQLPLUS

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

2）当DBS1、RPTDBS1、TEMPTABS表空间使用率超过80%时，需及时增加相应表空间

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 6 12:10:57 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile order by name; <<查看数据文件情况

NAME

——————————————————————————

/u2/oracle/oradata/topprod/blobdbs1.dbf

/u2/oracle/oradata/topprod/dbs1-01.dbf

/u2/oracle/oradata/topprod/dbs1-02.dbf

/u2/oracle/oradata/topprod/dbs1-03.dbf

/u2/oracle/oradata/topprod/dbs1-04.dbf

/u2/oracle/oradata/topprod/dbs1-05.dbf <<dbs1表空间数据文件最大代号05

/u2/oracle/oradata/topprod/rptdbs1-01.dbf

/u2/oracle/oradata/topprod/sysaux01.dbf

/u2/oracle/oradata/topprod/system01.dbf

/u2/oracle/oradata/topprod/temp02.dbf

/u2/oracle/oradata/topprod/temptabs.dbf

/u2/oracle/oradata/topprod/undotbs01.dbf

/u2/oracle/oradata/topprod/users01.dbf

13 rows selected.

SQL> alter tablespace dbs1 add datafile ‘/u2/oracle/oradata/topprod/dbs1-06.dbf’ size 2000M;

<<给dbs1表空间增加代号为06，大小为2000M的数据文件，使dbs1扩容2000M

Tablespace altered.

SQL> @q_tbsFREE; <<再观察表空间使用情况

SQL>exit <<退出SQLPLUS

<topprod:/u1/topprod/tiptop> sqlplus system/manager@topprod <<以system用户登陆数据库

SQL> @q_locktable;

Locked Object Tiptop User Proc Terminal SID SERIAL# MACHINE

—————- ———– —– ——– ———- ———- ———-

DS1.NMD_FILE tiptop 7208 29 41921 TTP-2

DS1.NMD_FILE tiptop 13327 1155 54429 TTP-2

SQL> alter system kill session ‘29,41921’ immediate; <<杀掉造成锁的会话

SQL> alter system kill session ‘1155,54429’ immediate; <<杀掉造成锁的会话

SQL>exit;

<topprod:/u1/usr/tiptop> exp ds1/ds1@topprod file=/tmp/exp_ds1.dmp log=/tmp/exp_ds1.log

<topprod:/u1/usr/tiptop> exp ds1/ds1@topprod file=/tmp/exp_ds1_ima_file.dmp tables=ima_file

<topprod:/u1/topprod/tiptop> sqlplus system/manager@topprod as sysdba

SQL> drop user ds1 cascade;

SQL>create user ds1 identified by ds1 default tablespace dbs1 temporary tablespace temp;

SQL>grant create session,create table to ds1;

SQL>grant resource to ds1;

SQL>grant create synonym to ds1;

SQL>grant select on sys.v_$session to ds1;

SQL>exit;

<topprod:/u1/topprod/tiptop> imp ds1/ds1@topprod file=/tmp/exp_ds1.dmp log=/tmp/imp_ds1.log

<topprod:/u1/topprod/tiptop> sqlplus ds1/ds1@topprod <<1.登入ds1数据库

SQL>create table ima_file_bak as select * from ima_file; <<2.备份ima_file到ima_file_bak

SQL> truncate table ima_file; <<3.清空ima_file表数据

SQL> exit; <<4.退出SQLPLUS

<topprod:/u1/topprod/tiptop> imp ds1/ds1@topprod file=/tmp/exp_ds1.dmp tables=ima_file ignore=y;

<<5.导入ima_file数据

1. 重建整个topprod实例
2. 导入之前的exp_all.dmp备份

<topprod:/u1/usr/tiptop>imp system/manager@topprod file=/tmp/exp_all.dmp ignore=y