oracle创建数据库、表空间、用户、赋权限等基本操作.docx

/etc/init.d/init.crs startcrs_stat -t增加表字段altertable ICC_TERMINAL_CONF add GC_TYPE char(1);SELECT T.GI_SERIAL_NUM,(MAX(T.GROW_WEIGHT )-MIN(T.VEHICLE_WEIGHT)) ABC FROM ICC_GI_TRANS T WHERE T.GI_SERIAL_NUM=000001GROUPBY T.GI_SERIAL_NUM1. 查看被锁的表SELECT p.spid, a.serial#, c.object_name, b.session_id, b.oracle_username, b.os_user_name FROM v$process p, v$session a, v$locked_object b, all_objects c WHERE p.addr = a.paddr AND a.process = b.process AND c.object_id = b.object_id查看锁表进程SQL语句2：select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;杀掉锁表进程：如有記錄則表示有lock，記錄下SID和serial# ，將記錄的ID替換下面的738,1429，即可解除LOCKalter system kill session 738,1429;修改字段长度altertable ICC_GC_TRANS modify SOURCE_CODE char(9);查表结构select COLUMN_NAME,DATA_TYPE from USER_TAB_COLS where TABLE_NAME=ICC_DC_TRANS查看表select * from user_tables;查表表结构select column_name,data_type ,data_length,data_precision,data_scale from user_tab_columns where table_name=ICC_GI_TRANSoracle 启动sqlplus /as sysdbastartupshutdown immediatelsnrctl startoracle 服务端监听文件配置/opt/oracle/10gR2/db/network/admin/listener.oralistener.oraLISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.148)(PORT = 1521)) ) )SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=zhangrui) (ORACLE_HOME=/home/oracle/app/oracle/product/11.1.0/db_1) (SID_NAME=orcl) ) )oracle 客户端连接配置/opt/oracle/10gR2/db/network/admin/tnsnames.ora ZHANGRUI = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.148)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zhangrui) ) )sqlnet.ora配置文件//sqlnet.ora//NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)CREATESEQUENCE zrseq INCREMENTBY1STARTWITH1NOMAXVALUENOCYCLENOCACHE系统权限 grant create session to test; grant create table to test; grant unlimited tablespace to test;grant create sequence to zhangrui revoke cr