- 作者: 三十而立
- 时间：2009年11月15日 11:58:53
- 请尊重原创作品。转载请保持文章完整性,并以超链接形式注明原始作者“inthirties（三十而立）”和出处”http://blog.csdn.net/inthirties/archive/2009/11/15/4816655.aspx”,深入讨论可以联系inthirties@gmail.com。
常用DBA的命令
以后持续收集，更新。
引用:
sqlplus "/ as sysdba"
sqlplus /nolog
--show the initialing parameter
引用:
show parameters;
show parameter PGA_AGGREGATE_TARGET;
-- flush share pool in order to re-parse the sql or others.
引用:
alter system flush shared_pool;
-- reset the initializing parameter value
引用:
alter system set SGA_MAx_size=1000M SCOPE=SPFILE;
-- gather schema statistic
引用:
EXEC dbms_stats.gather_schema_stats(ownname=> 'USERA' , cascade=> TRUE);
-- gather table statistic
引用:
EXEC dbms_stats.gather_table_stats('USERA', 'TABLEA');
-- create tablespace
引用:
create tablespace TESTTS01
logging
datafile 'F://Synchrophy/Server/oracle/userdata/DATAFILE01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
-- drop tablespace
引用:
drop tablespace TESTTS01 including contents and datafiles;
-- add a datafile into a tablespace
引用:
alter tablespace TESTTS01
add datafile 'F:/Synchrophy/Server/oracle/userdata/DATAFILE02.dbf';
-- rename tablespace
引用:
alter tablespace TESTTS01 rename to TESTTS02;
-- move the datafile
引用:
alter database rename file 'F:/Synchrophy/Server/oracle/userdata/DATAFILE02.dbf' to 'F:/Synchrophy/Server/oracle/userdata/DATAFILE02_1.dbf';
-- create user
引用:
create user ORATEST
identified by "ORATEST"
default tablespace TESTTS01
temporary tablespace TEMP
profile DEFAULT;
-- create role privileges
引用:
grant connect to ORATEST;
grant resource to ORATEST;
引用:
drop user oratest cascade;
-- drop table
引用:
drop table tableA cascade constraints;
truncate table tableA reuse storage;
truncate table tableA deallocate unused keep 100M;
-- exp
引用:
exp oneuser/oneuser@ora9i owner=twouser
file=("F:/Synchrophy/Server/oracle/userdata/dump/oneuser.data1.dmp",
"F:/Synchrophy/Server/oracle/userdata/dump/oneuser.data2.dmp",
"F:/Synchrophy/Server/oracle/userdata/dump/oneuser.data3.dmp",
"F:/Synchrophy/Server/oracle/userdata/dump/oneuser.data4.dmp",
"F:/Synchrophy/Server/oracle/userdata/dump/oneuser.data5.dmp",
"F:/Synchrophy/Server/oracle/userdata/dump/oneuser.data6.dmp")
rows=y statistics=none l
og="F:/Synchrophy/Server/oracle/userdata/dump/oneuser.dump.log" filesize=400k
-- imp
引用:
imp oneuser/oneuser@ora9i fromuser=oneuser touser=twouser
file=("F:/Synchrophy/Server/oracle/userdata/dump/oneuser.data1.dmp",
"F:/Synchrophy/Server/oracle/userdata/dump/oneuser.data2.dmp",
"F:/Synchrophy/Server/oracle/userdata/dump/oneuser.data3.dmp",
"F:/Synchrophy/Server/oracle/userdata/dump/oneuser.data4.dmp",
"F:/Synchrophy/Server/oracle/userdata/dump/oneuser.data5.dmp",
"F:/Synchrophy/Server/oracle/userdata/dump/oneuser.data6.dmp")
rows=y filesize=400k ignore=y constraints=n buffer=100M commit=y
-- startup
引用:
startup pfile='F:/Synchrophy/Server/oracle/admin/ORA9i/pfile/init.ora'
create pfile='F:/Synchrophy/Server/oracle/admin/ORA9i/pfile/init.ora' from spfile;
create spfile from pfile='F:/Synchrophy/Server/oracle/admin/ORA9i/pfile/init.ora';
startup nomount;
startup mount;
alter database open;
-- instance status
引用:
select status from v$instance;
-- shutdown
引用:
shutdown immediate;
-- statspack snapshot list
引用:
select SNAP_ID, STARTUP_TIME from stats$snapshot;
-- statspack
引用:
exec statspack.snap;
-- install statspack
引用:
@?/rdbms/admin/spreport.sql
@?/rdbms/admin/spcreate.sql
-- select currrent
引用:
select to_char(sysdate, 'yyyy-MM-dd HH24:mm:ss') from dual;
--- seach the Long column table.
引用:
SELECT * FROM
(SELECT TABLE_NAME, OWNER, count(*) NUM
FROM DBA_TAB_COLUMNS
WHERE DATA_TYPE='LONG'
OR (( DATA_TYPE='VARCHAR2'
or DATA_TYPE='CHAR'
or DATA_TYPE='NVARCHAR2'
or DATA_TYPE='NCHAR')
AND DATA_LENGTH > 1333)
AND OWNER NOT IN
('SYS','SYSTEM','SH','OLAPSYS','MDSYS','WKSYS','ODM','XDB','WMSYS') and owner = 'ORATEST'
GROUP BY TABLE_NAME, OWNER)
WHERE NUM > 1
---- create controlfile
引用:
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'F:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO01.LOG' SIZE 50M,
GROUP 2 'F:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG' SIZE 50M,
GROUP 3 'F:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO03.LOG' SIZE 50M
DATAFILE
'F:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/SYSTEM01.DBF',
'F:/DATAFILE/TESTTS/TESTTS01.DBF',
'F:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/SYSAUX01.DBF',
'F:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/USERS01.DBF',
'F:/DATAFILE/TESTTS/TESTTS02.DBF',
'F:/DATAFILE/TESTTS/TESTTS03.DBF',
'F:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/UNDOTBS02.DBF'
CHARACTER SET ZHS16GBK
;