常用DBA的命令

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。