Oracle基础命令+12541启动问题

今天开始，记录一下工作中使用过的Oracle的知识

• 自定义类型：

       CREATE OR REPLACE TYPE user_defined_type as object(first_name VARCHAR2(20),last_name VARCHAR2(30));
       CREATE TABLE DEFINED_TABLE(id NUMBER,name user_defined_type);
       insert into testtable values(1,name_format('冯','小刚'));

• 通过当前用户登录sqlplus，查看当前用户的表名：

       select table_name from user_tables;

• 通过sysdba登录sqlplus，查看用户表名：

       select * from all_tables where owner='TEST'；

• 通过sys登录sqlplus，查看当前的实例名：

       select name from v$database;

• 查看当前server端的字符集：

       select userenv('language') from dual;

• 在sqlplus中把select语句的结果存入到txt文件中的方法：

      SQL> spool /tmp/t1.txt;
      SQL> select * from schemas.table;
      SQL> spool off;

• 查看owner为user_export3的对象中数量：

       select OBJECT_TYPE,COUNT(*) from all_objects where OWNER='USER_EXPORT3' GROUP BY OBJECT_TYPE;

• 查看Oracle的编码：

       select * from nls_database_parameters where parameter ='NLS_CHARACTERSET'

• 登录到linux下，使用sqlplus的步骤：su - Oracle和sqlplus  / as sysdba

• 查看用户列表select username from dba_users;

• 查看当前正在连接的用户show user;

• 修改用户密码：alter user 用户名 identified by 新密码；

• 查看Oracle版本信息：select * from v$version;  

• 查看当前数据库名：select name from v$database;

• 查询当前数据库实例名：select instance_name from v$instance;

• 查询当前数据库实例：show parameter instance_name;

• 查看当前系统的SGA大小：show parameter sga_max_size;

• 给oracle的表添加一列：ALTER TABLE 表名 ADD 列名 VARCHAR(15);

• 给oracle的一列添加一样的内容，需要2条语句：

  update ora_dttp_char_2000_byte_ set phone='15804661187';

  commit;

• 筛选后正序导出

  select username from dba_users ORDER BY username ASC;

• 筛选后倒序导出

  select username from dba_users ORDER BY username DESC;

• 查询当前用户SCOTT所属的表空间

  select default_tablespace from dba_users where username='SCOTT';

• 查询当前的USERS表空间中有多少用户

  select distinct owner from dba_segments where tablespace_name ='USERS' ;

• 查看FRAMEWORK这个用户有多少个表select count(*) from dba_tables where owner='SCOTT';

• 如果查询FRAMEWORK用户下的数据表，可以通过FRAMEWORK登录，使用命令：select table_name from FRAMEWORK;

• 创建用户：

       create user TESTUSER identified by root;

       grant dba to TESTUSER;

       grant connect, resource to TESTUSER ;

• Oracle导入导出的虚拟目录操作

1. 在linux下通过su - oracle用户创建实体的目录，然后在进行下面的步骤

2. 创建虚拟目录：create or replace directory exp_dir as '/tmp';

3. 授权虚拟目录 ：grant read, write on directory exp_dir to eygle;

4. 查询虚拟目录 ：select * from dba_directories;

5. 删除虚拟目录：drop directory exp_dir;

• 删除用户：drop USER import_01 CASCADE;

• 创建表时Oracle没有办法添加注释，只能单独添加表注释：COMMENT ON table t1 IS '个人信息'; 

• 创建序列在创建表格时，直接创建即可

• 查询序列：SELECT sequence_name FROM user_sequences WHERE sequence_name='序列名';

• 删除序列：DROP SEQUENCE 序列名;

• 通过命令直接进入到SYS用户：sqlplus / as sysdba

• 第二种进入sqlplus的方式，linux通过命令sqlplus /nolog进入到sqlplus，然后，通过conn sys /as sysdba更换为用户SYS

• 显示当前会话连接用户：show  user;

• 在SYS用户下通过命令查看当前的实例名：show parameter instance_name;

• 在SYS用户下个某一个用户赋予权限：grant dba to SCOTT;

• 创建临时表空间和表空间

--创建临时表空间
create temporary tablespace z_temp 
tempfile '/home/oracle_11/table_place/place_temp.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local; 
--创建表空间
create tablespace z_place  
logging  
datafile '/home/oracle_11/table_place/place_data.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;
--创建用户
create user TEST1026 identified by root default tablespace z_place temporary tablespace z_temp;  
GRANT CONNECT,RESOURCE,dba to TEST1026

• 建表指定表空间，如果不指定的话，会使用默认的USERS空间

create table TEST10261926(

LOCATION VARCHAR(20),NAME VARCHAR(20) NOT NULL,

ID_CARD VARCHAR(20),E_BIRTHDAY DATE ,

PHONE VARCHAR(15) ,B_SALARY NUMBER(6,2) NOT NULL,

C_SALARY NUMBER(6,2),BANKNO VARCHAR2(20),TYSHXYDM VARCHAR2(18),

EMAIL VARCHAR2(50),FEFF VARCHAR2(100) )

tablespace z_place;

• 查看表空间的名称及大小，结果如图所示：

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name; 

• 查看所有临时表空间大小，结果如图所示：

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, 
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

 

• 查看所有临时表空间名字及文件名，查询结果如图所示：

  select f.file#,t.ts#,f.name "File",t.name "Tablespace" from v$tempfile f,V$tablespace t where f.ts# = t.ts#;  

   

• 删除临时表空间

drop tablespace YUHANG_TEMP including contents and datafiles

• 查看创建用户时的默认临时表空间

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

• 查看库里所有用户所属的表空间和临时表空间

select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;

 

• 虚拟机启动，但是Navicat的连接报错信息如下

解决方法如下(参考链接)：

1. 虚拟机上启动sqlplus：sqlplus / as sysdba
2. 查看一下当前的用户show user
3. linux查看当前启动的实例状态lsnrctl status
4. 在sys用户下执行命令：SQL>startup
5. 然后切换到oracle用户下的bin目录下执行命令lsnrctl start，然后在使用navicat登录连接，就可以啦

• 在linux启动第二个实例

1. 启动另一个的oracle的实例
2. 在linux下执行，查看实例的状态：lsnrctl status
3. 在linux下执行，是切换实例的命令：export ORACLE_SID=orcl3
4. 在linux下执行：sqlplus / as sysdba
5. 在sqlplus下执行：startup
6. 在sqlplus下执行：sqlplus dmp01/123456@192.168.5.88:1521/utf8
7. 在linux下执行，查看实例的状态：lsnrctl status

• 查看当前的目录以及权限：

SELECT * FROM user_tab_privs t, all_directories d WHERE t.table_name(+) = d.directory_name ORDER BY 2, 1;