【原创】Oracle管理命令集锦

Oracle管理命令集锦

创建表空间：
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K BLOCKSIZE 8K;
CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf' SIZE 25M TABLESPACE GROUP group1;
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
修改表空间：
ALTER TABLESPACE lmtbsb ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;
ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;
ALTER TABLESPACE bigtbs RESIZE 80G;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;
ALTER TABLESPACE flights READ ONLY;
ALTER TABLESPACE flights READ WRITE;
表空间数据文件管理：
ALTER TABLESPACE users ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M AUTOEXTEND ON NEXT 512K MAXSIZE 250M;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' AUTOEXTEND OFF;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;
ALTER TABLESPACE users OFFLINE NORMAL;
ALTER TABLESPACE users RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf','/u02/oracle/rbdb1/user2.dbf' TO '/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf';
ALTER TABLESPACE example DROP DATAFILE '+DGROUP1/example_df3.f';
ALTER TABLESPACE lmtemp DROP TEMPFILE '/u02/oracle/data/lmtemp02.dbf';
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;

归档路径管理：
show parameter archive;
alter system set log_archive_dest_1='location=d:\oracle\archivelog' scope=both;
create pfile from spfile;
ALTER SYSTEM SWITCH LOGFILE;

删除表空间：
DROP TABLESPACE users INCLUDING CONTENTS;
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

开启归档模式：
SHUTDOWN
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE ARCHIVELOG MANUAL;
ALTER DATABASE OPEN;
SHUTDOWN IMMEDIATE

系统进行一次归档：
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3;

导出部分用户的数据：
exp system/password@orcl owner=(user1,user2,user3) file=c:\test01.dmp
导入部分用户的数据：
imp system/password@orcl fromuser=(user1,user2,user3) touser=(eia,eia_hn,eia_test) file=c:\test01.dmp