常用DBA的命令

• 作者:​​三十而立​​

 常用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 

引用:

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 
;