oracle基本的操作命令,oracle基本操作命令

连接方式:

sqlplus "/as sysdba"

sqlplus /nolog

conn userk/userk

./sqlplus userk/userk@192.168.1.218:1521/ORCL

./sqlplus c##kbj/kbj@192.168.1.235:1539/cdb1

sqlplus sys/oracle as sysdba

基本命令:

desc TEST_ORN_SIMPLE;(查看表结构)

select name from v$datafile查询表空间中数据文件具体位置

select table_name from user_tables;(查询当前用户下的表名)

create table test_osuck (id int not null primary key, na varchar(30), addr varchar(30));

select * from TEST_ORN_SIMPLE;(查表内容)

create tablespace TEST_ORN_SIMPLE datafile '/usr/local/yrm/oracle_data/my_space.dbf' size 50M autoextend on next 50m maxsize 2048m extent management local;

create temporary tablespace kbj_tempspace tempfile '/u01/app/oracle/oradata/orcl12c/kbj_tmp.dbf' size 50m autoextend off;

insert into TEST_osuck values('27','bmw','x5')

登陆并创建命名空间,用户，表。

创建表空间

create tablespace my_space datafile '/u01/app/oracle/oradata/CDB1/kbj_space.dbf' size 50M autoextend on next 50m maxsize 2048m extent management local;

创建临时表空间

create temporary tablespace my_space_temp tempfile '/u01/app/oracle/oradata/CDB1/kbj_space_temp.dbf' size 32m autoextend on next 32m maxsize 1024m extent management local;

创建用户

create user kbj identified by kbj default tablespace my_space temporary tablespace my_space_temp;

授权

grant connect,resource to c##my_user_1;

grant dba to c##my_user_1;

切换用户

conn my_user_1/123456 as sysdba;

oracle-65175错误：

grant sysdba to c##kbj container=all;

查看归档日志路径：

select name from v$archived_log;

对日志归档：

alter system switch logfile;

oracle运行sql文件：

@/home/fu/oracle_table.sql

@/home/fu/oracle_testcase.sql

创建字典：create directory "kbj_dir" as '/home/oracle/kbj/logmand';

每次新创建表名之后需要重新操作一下

EXECUTE dbms_logmnr_d.build(dictionary_location=>'kbj_dir',dictionary_filename=>'dictionary.ora',options => dbms_logmnr_d.store_in_flat_file);(oracle服务端上设置)

commit;

手动从字典查找语句：

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'/home/oracle/backup/archive_1_252_998958668.log',OPTIONS =>DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.START_LOGMNR(dictfilename=>'/home/oracle/kbj/logmand/dictionary.ora');

select operation, sql_redo from v$logmnr_contents where seg_name='TEST_BILL';