ORACLEC常用操作命令
表空间
create tablespace ADMIN_TABLESPACE datafile '/u01/app/oracle/oradata/orcl/ADMIN_TABLESPACE.DBF' size 400 M;
ALTER DATABASE DATAFILE'/u01/app/oracle/oradata/orcl/ADMIN_TABLESPACE.DBF' AUTOEXTEND ON NEXT 100 M MAXSIZE 20480 M;
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;
select tablespace_name, bytes/ 1024 / 1024 mb from dba_data_files;
select tablespace_name, autoextensible from dba_data_files;
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;