oracle 常用命令

oracle 常用命令

 

9.use logmin analyz redo logfil

    a. in the init.ora specifi utl_file_dir = ' '

'c:/oracle/oradb/log' ;     b. sql> execut dbms_logmnr_d.build 'oradb.ora'.

    c. sql> execut dbms_logmnr_add_logfil 'c:/oracle/oradata/oradb/redo01.log'.

    sql> dbms_logmnr.new ;

    d. sql> execut dbms_logmnr.add_logfil 'c:/oracle/oradata/oradb/redo02.log'.

    sql> dbms_logmnr.addfil ;

    e. sql> execut dbms_logmnr.start_logmnr dictfilename=>'c:/oracle/oradb/log/oradb.ora' ;

v$logmnr_paramet     f. sql> select * from v$logmnr_cont v$logmnr_dictionary.

    sql> v$logmnr_log ;

    g. sql> execut dbms_logmnr.end_logmnr;

    第二章：表空间管理

    1.creat tablespac

    sql> creat tablespac tablespace_nam datafil 'c:/oracle/oradata/file1.dbf' size 100m.

    sql> 'c:/oracle/oradata/file2.dbf' size 100m minimum extent 550k [logging/nologging]

    sql> default storag initi 500k next 500k maxext 500 pctincceas 0

    sql> [online/offline] [permanent/temporary] [extent_management_clause]

    2.local manag tablespac

    sql> creat tablespac user_data datafil 'c:/oracle/oradata/user_data01.dbf'

    sql> size 500m extent manag local uniform size 10m;

    3.temporari tablespac

    sql> creat temporari tablespac temp tempfil 'c:/oracle/oradata/temp01.dbf'

    sql> size 500m extent manag local uniform size 10m;

    4.chang the storag set

    sql> alter tablespac app_data minimum extent 2m;

    sql> alter tablespac app_data default storag initi 2m next 2m maxext 999 ;

    5.take tablespac offlin or onlin

    sql> alter tablespac app_data offline;

    sql> alter tablespac app_data online;

    6.read_onli tablespac

    sql> alter tablespac app_data read only|write;

    7.drope tablespac

    sql> drop tablespac app_data includ contents;

    8.enabl automat extens of data file

    sql> alter tablespac app_data add datafil 'c:/oracle/oradata/app_data01.dbf's 200m

    sql> autoextend on next 10m maxsiz 500m;

    9.chang the size fo data file manual

    sql> alter databas datafil 'c:/oracle/oradata/app_data.dbf'res 200m;

    10.Move data files: alter tablespac

    sql> alter tablespac app_data renam datafil 'c:/oracle/oradata/app_data.dbf'

    sql> to 'c:/oracle/app_data.dbf';

    11.move data files:alt databas

    sql> alter databas renam file 'c:/oracle/oradata/app_data.dbf'

    sql> to 'c:/oracle/app_data.dbf';

    第三章：表

    1.creat a tabl

column datatype]....     sql> creat tabl table_nam column datatype.

    sql> tablespac tablespace_nam [pctfree integer] [pctuse integer]

    sql> [initran integer] [maxtran integer]

    sql> storag initi 200k next 200k pctincreas 0 maxext 50

    sql> [logging|nologging] [cache|nocache]

    2.copi an exist tabl

    sql> creat tabl table_nam [logging|nologging] as subqueri

    3.creat temporari tabl

    sql> creat global temporari tabl xay_temp as select * from xay;

    on commit preserv rows/on commit delet row

    4.pctfree = averag row size - initi row size *100 /averag row size

    pctuse = 100-pctfree- averag row size*100/avail data space

    5.chang storag and block util paramet

    sql> alter tabl table_nam pctfree=30 pctused=50 storag next 500k

    sql> minext 2 maxext 100 ;

    6.manual alloc extent

    sql> alter tabl table_nam alloc extent size 500k datafil 'c:/oracle/data.dbf' ;

    7.move tablespac

    sql> alter tabl employe move tablespac users;

    8.dealloc of unus space

    sql> alter tabl table_nam dealloc unus [keep integer]

    9.truncat a tabl

    sql> truncat tabl table_name;

    10.drop a tabl

    sql> drop tabl table_nam [cascad constraints];

    11.drop a column

    sql> alter tabl table_nam drop column comment cascad constraint checkpoint 1000;

    alter tabl table_nam drop column continue;

    12.mark a column as unus

    sql> alter tabl table_nam set unus column comment cascad constraints;

    alter tabl table_nam drop unus column checkpoint 1000;