select username from dba_users SQL> drop user DXSYSDB cascade; cascade表示要删除用户下的所有对象和数据。 SQL> ALTER USER XDJ ACCOUNT UNLOCK; grant dba to username TRUNCATE TABLE TABLENAME show parameter alter system set log_buffer=10485760 scope=spfile; alter system set processes=500 scope=spfile; SQL> alter database datafile 'd: \test.dbf' offline drop; SQL > alter database open; 方法一 1. 关闭数据库，利用os拷贝 shutdown immediate关闭数据库 select * from dba_tablespaces; SELECT * FROM DBA_DATA_FILES; select tablespace_name,sum(bytes)/(1024*1024) from sys.dba_free_space group by tablespace_name; 裸设备：create tablespace cbs_default_dat datafile '/dev/rlv_cbs_d_dat' size 2048m reuse autoextend on next 128m maxsize 4096m; 文件系统：create tablespace cbs_default_dat datafile '/orainst/oracle/db/oradata/ora253/cbs_default_dat.dbf' size 2048m; alter tablespace TBS_1 add datafile '/orainst/oracle/db/oradata/ora254/TBS_1.dbf' size 1024m; create temporary tablespace temp tempfile 'c:\oracle\oradata\temp01.dbf' size 500m extent management local uniform sizeTABLE_NAME MOVE TABLESPACE_NAME; ALTER TABLE 10m; ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME; 可以使用如下命令删除表空间，其中如果没有including contents and datafile,则表空间的内容及 数据文件需要手工删除： SQL>alter database drop tablespace 表空间名 including contents and datafile; 不能删除数据库的默认表空间和默认临时表空间 不能删除SYSTEM表空间和SYSAUX表空间 alter database datafile 'c:\oracle\oradata\app_data.dbf' resize 200m; col tablespace format a20 ; select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name; select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner' group by segment_name; select tablespace_name,count(blocks) blocks,sum(bytes/1024/1024) max_chunk from sys.dba_free_space group by tablespace_name; 其中，CHUNKS列表示表空间中有多少可用的空闲块(每个空闲块是由一些连续的Oracle数据块组成),如果这样的空闲块过 多，比如平均到每个数据文件上超过了100个，那么该表空间的碎片状况就比较严重了，可以尝试用以下的SQL命令进行表 空间相邻碎片的接合：alter tablespace 表空间名 coalesce; alter tablespace 表空间名 coalesce,进行表空间相邻碎片的接合 alter tablespace 表空间名称 rename to 新名称 将数据文件从一个地方挪到另外一个地方后，需要在数据库中修改一下数据文件的名称(含文件的绝对路径)。修改方法 如下： SQL>shutdown immediate; SQL>startup mount; SQL> alter database rename file '/old path/old_filename.dbf' to '/new path/new_fi
lename.dbf'; SQL> recover database; SQL> alter database open;
ALTER DATABASE DATAFILE '/opt/HUAWEI/cgp/workshop/omu/database/undotbs01.dbf' AUTOEXTEND OFF MAXSIZE 200M; from table_name;只删除了表数据 delete truncate table table_name drop storage; 删除表定义空间 alter table table_name deallocate unused;删除表未使用的空间，即highwater-used=unused $ORACLE_BASE/diag/rdbms/DB_NAME/INSTANCE_NAME/trace/*.log alter system switch logfile; alter system checkpoint; sql> alter database add logfile [group 4] ('/disk3/log4a.rdo','/disk4/log4b.rdo') size 500m; sql> alter database add logfile member '/disk3/log1b.rdo' to group 1, '/disk4/log2b.rdo' to group 2; sql> alter database rename file 'c:/oracle/oradata/oradb/redo01.log' to 'c:/oracle/oradata/redo01.log'; sql> alter database drop logfile group 3; sql> alter database drop logfile member 'c:/oracle/oradata/redo01.log'; analyze table cbe_account_dyn compute statistics for all indexes; analyze table cbe_account_dyn compute statistics for all indexes; alter index index_name rebuild tablespace ts_name Alter index indexname rebuild SELECT ROW_NUM,EMPNO,SALARY,FIRSTNME FROM (SELECT ROW_NUMBER() OVER(ORDER BY SALARY) AS ROW_NUM,EMPNO,SALARY,FIRSTNME FROM EMPLOYEE) AS TEMP WHERE ROW_NUM BETWEEN 10 AND 20 用dbms_stats包中的存储过程gather_schema_stats,它不仅 重新分析索引，还会分析表等。执行这个存储过程可能会需 要非常长的时间。 sqlplus /nolog; SQL>connect / as sysdba; SQL>select SID, SERIAL#,USERNAME from v$session; SID SERIAL# USERNAME ---------- ---------- -----------------------------91 46790 SYSDB1 SQL>alter system kill session '91,46790'; 1.首先编写一个控制命令的脚本文件，通常以ctl结尾，内容如下： numservice.ctl: load data
exp/imp适合于同类型数据库之间数据转换 共有四种不同模式:表,用户,表空间,数据库 现分别举例说明 1.表级别 $ exp hr/hr tables=jobs direct=y file=/data/table_jobs.dmp $ imp hr/hr tables=jobs direct=y file=/data/table_jobs.dmp 2.用户级别 $ exp "'sys/sys as sysdba'" owner=hr direct=y file=/data/owner_hr.dmp $ imp "'sys/sys as sysdba'" fromuser=hr touser=hr file=/data/owner_hr.dmp 3.表空间级别 $ exp "'sys/sys as sysdba'" transport_tablespace=y direct=y tablespaces=examples file=/data/ts_examples.dmp 4.数据库级别 $ exp "'sys/sys as sysdba'" full=y file=/data/db.dmp 说明：如要使用as sysdba或as sysoper进行操作，则必须使 用如下用法： "'sys/sys as sysdba'" "'system/system as sysoper'"
exp "'sys/sys as sysdba'" owner=sysdb9 direct=y file=owner_sysdb9.dm imp "'sys/sys as sysdba'" fromuser=sysdb9 touser=zhlsysdb file=owner_sysdb9.dm commit=y ignore=y