ORACLE常用操作命令

ORACLEC常用操作命令

表空间

-- 创建表空间：
create tablespace ADMIN_TABLESPACE datafile '/u01/app/oracle/oradata/orcl/ADMIN_TABLESPACE.DBF' size 400M;
-- 修改表空间自增，最大20480M
ALTER DATABASE DATAFILE'/u01/app/oracle/oradata/orcl/ADMIN_TABLESPACE.DBF' AUTOEXTEND ON NEXT 100M MAXSIZE 20480M;
-- 删除表空间
drop tablespace jbpm including contents and datafiles; 
-- 开启表空间自动扩展功能
alter database datafile '/u01/app/oracle/oradata/sktest/skuser.dbf' autoextend on;
-- 关闭表空间自动扩展功能
alter database datafile '/u01/app/oracle/oradata/sktest/skuser.dbf' autoextend off;
--  查询表空间的目前使用大小
select tablespace_name,sum(bytes/1024/1024) mb from dba_segments where tablespace_name='USER1' group by tablespace_name;
-- 查询aaa表空间的分配大小
select tablespace_name,bytes/1024/1024 mb from dba_data_files;
-- 查询aaa表空间是否自动扩展
select tablespace_name,autoextensible from dba_data_files;
-- 查询aaa表空间最大扩展大小（注：当表空间开启了自动扩展特性，表空间会一直扩展到操作系统支持的最大大小）
select tablespace_name,maxblocks*8 from dba_data_files; 
-- 查看表空间和物理文件路径
select tablespace_name, file_id, file_name from dba_data_files order by tablespace_name;
-- 修改物理表所属表空间
alter table ES_RUNNINGSTATE move tablespace ADMIN_TABLESPACE;
select index_name from user_indexes where status = 'UNUSABLE' ;
alter index PK_ES_RUNNINGSTATE rebuild;

时间LIST拼接

-- 小时拼接
select lpad(level-1, 2, 0) datevalue from dual connect by level < = 24;
select sysdate || lpad(level-1, 2, 0) datevalue from dual connect by level < = 24;
-- 天拼接
SELECT TO_CHAR( (TRUNC((to_date('"结束时间"', 'yyyy-mm-dd') - "天数长度") + ROWNUM)), 'YYYY-MM-DD') AS DAYNAME FROM DUAL CONNECT BY ROWNUM <= "天数长度"

oracle 连接配置

-- 小时拼接
HLW_ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 121.36.59.233)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )

用户

-- 创建用户
create user admin identified by Passw0rd;
create user admin identified by Passw0rd default tablespace ADMIN_TABLESPACE quota unlimited on ADMIN_TABLESPACE;
-- 用户授权
grant connect,resource,imp_full_database,exp_full_database to 用户名;

系统环境变量

ORACLE_BASE=/u01/app/oracle;
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1;
export ORACLE_HOME
ORACLE_SID=orcl;
export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH;
export PATH

表

-- 查看创建表语句
select dbms_metadata.get_ddl('TABLE','TABLE_NAME') from dual;
-- 修改时间
update  WR_POWER set currenttime = to_date('2019-11-03 10:30:00','yyyy-mm-dd hh24:mi:ss');
-- 查看所有表中的记录数
select t.table_name,t.num_rows from user_tables t order by t.NUM_ROWS desc;

系统变量

-- 查看数据库字符集
select userenv('language') from dual;