在linux 打command 登入oracle

sqlplus "帳號/密碼@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=【host】)(Port=【port】))(CONNECT_DATA=(SID=【sid】)))"

[ORACLE] Data got commited in another/same session, cannot update row.

Try unselecting

use ORA_ROWSCN for DataEditor insert and update statement

in tools->preferences->database->object viewer

[ORACLE] UPDATE時 撘配EXISTS關鍵字

有時UPDATE時 會用到子查詢

例
update member
set  unit =(select unit from member_sync
where member.oid = member_sync.oid
and member.unit <>member_sync.unit)


腦中覺得  這樣下 就是update member裡的unit 和member_sync 有同樣oid 但unit不同的資料們

but~~~~

這樣下完  會整張member的unit都被update了 而且欄位還都變成null..........



google後 看到 http://www.techonthenet.com/oracle/update.php
寫說要加上"EXISTS"

You may wish to update records in one table based on values in another table. Since you can't list more than one table in the Oracle UPDATE statement, you can use the Oracle EXISTS clause.


所以寫法就變成
update member
set  unit =(select unit from member_sync
where member.oid = member_sync.oid
and member.unit <>member_sync.unit)

WHERE EXISTS (select unit from member_sync
where member.oid = member_sync.oid
and member.unit <>member_sync.unit)


原因在http://blog.xuite.net/twoli1102/work/65466674-Update%E8%AA%9E%E5%8F%A5%E5%84%AA%E5%8C%96
裡提到
Update語句的原理是先根據where條件查到資料後，如果set中有子查詢，則執行子查詢把值查出來賦給更新的欄位，執行更新。

 如：
update 表a set a.欄位1 = (select b.欄位1 from 表b where a.欄位2=b.欄位2) where exists(select 1 from 表b where a.欄位2=b.欄位2)。

查表a的所有資料，迴圈每條資料，驗證該條資料是否符合exists(select 1 from 表b where a.欄位2=b.欄位2)條件，如果是則執行(select b.欄位1 from 表b where a.欄位2=b.欄位2)查詢，查到對應的值更新a.欄位1中。
關聯表更新時一定要有exists(select 1 from 表b where a.欄位2=b.欄位2)這樣的條件，否則將表a的其他資料的欄位1更新為null值。

ORA-00911:字元無效

sql直接貼在sqldeveloper 不會出錯
但在程式跑 就是一直出現

有很大原因是因為sql裡有分號

網路上大部份解決是把多句的select 後面的分號拿掉就好


but 他會換ORA-00933: SQL 命令未正確結束.........或是其他奇奇怪怪


解法：在前後加上begin和end就好了


begin
update member set code='123';
update vip set sn='223';
end;

[ORACLE] 查看oracle版本

select * from v$version 

select * from product_component_version

[ORACLE]查看權限(privilege)相關語法

目前登入角色之角色權限(非以sys登入)
select * from role_sys_privs;

descr  role_sys_privs
Name         Null Type        
------------ ---- -------------
ROLE              VARCHAR2(128)
PRIVILEGE         VARCHAR2(40)
ADMIN_OPTION      VARCHAR2(3)
COMMON            VARCHAR2(3)


查看db裡有哪些使用者(以sys登入)
 select * from dba_users

descr dba_users
Name                        Null     Type                      
--------------------------- -------- ---------------------------
USERNAME                    NOT NULL VARCHAR2(128)            
USER_ID                     NOT NULL NUMBER                    
PASSWORD                             VARCHAR2(4000)            
ACCOUNT_STATUS              NOT NULL VARCHAR2(32)              
LOCK_DATE                            DATE                      
EXPIRY_DATE                          DATE                      
DEFAULT_TABLESPACE          NOT NULL VARCHAR2(30)              
TEMPORARY_TABLESPACE        NOT NULL VARCHAR2(30)              
CREATED                     NOT NULL DATE                      
PROFILE                     NOT NULL VARCHAR2(128)            
INITIAL_RSRC_CONSUMER_GROUP          VARCHAR2(128)            
EXTERNAL_NAME                        VARCHAR2(4000)            
PASSWORD_VERSIONS                    VARCHAR2(12)              
EDITIONS_ENABLED                     VARCHAR2(1)              
AUTHENTICATION_TYPE                  VARCHAR2(8)              
PROXY_ONLY_CONNECT                   VARCHAR2(1)              
COMMON                               VARCHAR2(3)              
LAST_LOGIN                           TIMESTAMP(9) WITH TIME ZONE
ORACLE_MAINTAINED                    VARCHAR2(1)  


查看user有哪些權限(以sys登入)
 select * from dba_sys_privs

descr dba_sys_privs
Name         Null Type        
------------ ---- -------------
GRANTEE           VARCHAR2(128)
PRIVILEGE         VARCHAR2(40)
ADMIN_OPTION      VARCHAR2(3)
COMMON            VARCHAR2(3)

查看user有哪些role(以sys登入)
select * from dba_role_privs

descr dba_role_privs
Name         Null Type        
------------ ---- -------------
GRANTEE           VARCHAR2(128)
GRANTED_ROLE      VARCHAR2(128)
ADMIN_OPTION      VARCHAR2(3)
DEFAULT_ROLE      VARCHAR2(3)
COMMON            VARCHAR2(3


只列出自己建立的user 有哪些role(以sys登入)
select * from dba_role_privs
where grantee in(select username  from dba_users where ACCOUNT_STATUS='OPEN') order by grantee

ORA-00955: name is already used by an existing object

表示物件名稱已被用了

要如何查出

SELECT *
FROM all_objects
WHERE object_name = 'object的名稱'


descr all_objects
Name           Null     Type      
-------------- -------- ------------
OWNER          NOT NULL VARCHAR2(30)
OBJECT_NAME    NOT NULL VARCHAR2(30)
SUBOBJECT_NAME          VARCHAR2(30)
OBJECT_ID      NOT NULL NUMBER    
DATA_OBJECT_ID          NUMBER    
OBJECT_TYPE             VARCHAR2(19)
CREATED        NOT NULL DATE      
LAST_DDL_TIME  NOT NULL DATE      
TIMESTAMP               VARCHAR2(19)
STATUS                  VARCHAR2(7)
TEMPORARY               VARCHAR2(1)
GENERATED               VARCHAR2(1)
SECONDARY               VARCHAR2(1)





如果覺得要刪掉

drop  剛查到的OBJECT_TYPE   object的名稱;

[ORACLE] 檢視user現在對表有哪些權限

以 帳號登入(非sysdba)

SELECT * FROM USER_TAB_PRIVS;




descr USER_TAB_PRIVS
Name       Null Type        
---------- ---- -------------
GRANTEE         VARCHAR2(128)  ←被grant的對象
OWNER           VARCHAR2(128)  ←table_name真正的owner
TABLE_NAME      VARCHAR2(128)
GRANTOR         VARCHAR2(128)
PRIVILEGE       VARCHAR2(40)  ←select或insert或..........
GRANTABLE       VARCHAR2(3)
HIERARCHY       VARCHAR2(3)
COMMON          VARCHAR2(3)
TYPE            VARCHAR2(24)  ←table或view或sequence或.....

[ORACLE] create user


drop user 帳號 cascade;--可忽略 但建議使用

CREATE USER 帳號 IDENTIFIED BY 密碼
  DEFAULT TABLESPACE TABLESPACE名稱通常為USERS TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT ACCOUNT UNLOCK;

GRANT CONNECT TO 帳號;
GRANT RESOURCE TO 帳號;
GRANT CREATE SESSION TO 帳號;


[ORACLE] 在sqlplus下以sys操作

1.在 CMD下 要先指定SID 才能連
set ORACLE_SID=「SID」

sqlplus "/ as sysdba"



SQL> 開始操作語法

ORA-01461：只有在將值插入資料類型為 LONG 的資料欄時, 才可以連結一個 LONG 值

白話：就是欄位不夠大........

[ORACLE] v$sql and v$sqlarea

If your queries are still in the shared_pool, you can use v$sql and v$sqlarea. They would be flushed out regularly, but you can find them till that period of time.

[ORACLE]開啟archive mode

SQL> archive log list;

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

SQL> archive log list;


SQL> select * from v$log;

SQL> alter system switch logfile;

SQL> select * from v$log;

oracle10升11

原始文件教學：http://docs.oracle.com/cd/E17781_01/install.112/e18803/toc.htm#autoId27

To import and export data between 10.2 XE and 11.2 XE, perform the following steps:

   Copy the gen_inst.sql file from the upgrade directory of 11.2 XE shiphome to your local directory.

   Connect to 10.2 XE database as SYS user and run gen_inst.sql. This will generate install.sql, gen_apps.sql and other .sql files. The files will be generated in the folder containing gen_inst.sql.

   SQL> @gen_inst.sql

   To export the data from 10.2 XE database, perform the following steps:

       Connect to 10.2 XE database as SYS user.

       Create a dump folder dump_folder on the local file system.

       Create directory object DUMP_DIR with READ and WRITE privilege to SYSTEM user.

       SQL> CREATE DIRECTORY DUMP_DIR AS '/<dump_folder>';

       SQL>GRANT read, write ON DIRECTORY DUMP_DIR TO system;

       Export data from 10.2 XE database to the dump folder.

       expdp system/system_password full=Y

        EXCLUDE=SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\"

        directory=DUMP_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

       expdp system/system_password

        TABLES=FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ directory=DUMP_DIR

        dumpfile=DB10G2.dmp logfile=expdpDB10G2.log

   Deinstall 10.2 XE if installation of 11.2 XE is planned on the same system.

   Install 11.2 XE database. For more information see Section 4, "Installing Oracle Database XE".

   To import data to the 11.2 XE database, perform the following steps:

       Connect to 11.2 XE database as SYS user.

       Create directory object DUMP_DIR with READ and WRITE privilege to SYSTEM user.

       SQL> CREATE DIRECTORY DUMP_DIR AS '/<dump_folder>';

       SQL>GRANT read, write ON DIRECTORY DUMP_DIR TO system;

       Import data to 11.2 XE database from the dump folder.

       impdp  system/system_password full=Y directory=DUMP_DIR

        dumpfile=DB10G.dmp logfile=expdpDB10G1.log

       impdp  system/system_password directory=DUMP_DIR

        TABLE_EXISTS_ACTION=APPEND TABLES=FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$

        dumpfile=DB10G2.dmp logfile=expdpDB10G1b.log

   Connect to 11.2 XE database as SYS user and run the script install.sql, which was generated in Step 2. This will trigger the execution of ws.sql, gen._apps.sql, and other .sql files.

---實際步驟—

↑下載oracle11

↑↑

SQL> conn sys/密碼 as sysdba

SQL> @C:\路徑ooxxx\gen_inst.sql

產生的檔案會在【C:\oraclexe\app\oracle\product\oracle版本\server\bin\】(複製到【路徑aabb】)

↑↑↑

SQL> drop directory DUMP_DIR;

SQL> CREATE DIRECTORY DUMP_DIR AS 'C:\路徑xyz\實際資料夾名稱';

SQL>GRANT read, write ON DIRECTORY DUMP_DIR TO system;

換開cmd

expdp system/密碼 full=Y  EXCLUDE=SCHEMA:\"LIKE \'APEX_%\'\",SCHEMA:\"LIKE \'FLOWS_%\'\"  directory=DUMP_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

expdp system/密碼 TABLES=FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ directory=DUMP_DIR dumpfile=DB10G2.dmp logfile=expdpDB10G2.log

↑↑移除10 安裝11

建議安裝完後先到管理者頁面(workspace:internal username:admin password:安裝時輸入的密碼)設定不要APEX表和DEMO表

否則之後新建的schema和下一步匯入的原schema 會多了一些table

像是

↑↑

SQL> conn sys/密碼 as sysdba

SQL> CREATE DIRECTORY DUMP_DIR AS ' C:\路徑xyz\實際資料夾名稱';

SQL>GRANT read, write ON DIRECTORY DUMP_DIR TO system;

換開cmd

impdp  system/密碼 full=Y directory=DUMP_DIR  dumpfile=DB10G.dmp logfile=expdpDB10G1.log

impdp  system/密碼directory=DUMP_DIR  TABLE_EXISTS_ACTION=APPEND TABLES=FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$  dumpfile=DB10G2.dmp logfile=expdpDB10G1b.log

SQL> @C:\路徑aabb \install.sql

完成!

匯出11G 至10G (import from 11g export to 10 g)

用原本的EXP IMP 指令匯入匯出的話會有錯

SO..  要改用EXPDP  IMPDP  指定版本


STEP1 打開SQL命令  在資料庫中建立一個別名為datapump的 directory
SQL> CREATE DIRECTORY datapump AS 'C:/DB';


STEP2 測試是否有建成功
SQL> SELECT * DBA_DIRECTORIES;


STEP3 把權限給 OOXX 這個USER
SQL> GRANT READ, WRITE ON DIRECTORY datapump to OOXX;


STEP4 打開命令提示字元
expdp OOXX/密碼@xe directory=datapump dumpfile=newdump.dmp VERSION=10.2 content=metadata_only


content=metadata_only 是只匯出schema  也可content=all|data_only

接著到 要匯入的電腦  先重做STEP1~3

STEP5 打開命令提示字元
impdp OOXX/密碼@xe directory=datapump dumpfile=newdump.dmp VERSION=10.2
content=metadata_only


[ORACLE] 時間單位

select sysdate,sysdate +1 from dual

+1←加一天

取得table裡所有column的名稱

SELECT column_name
FROM user_tab_cols
WHERE table_name=UPPER('TABLE名字')
order by column_id

如果要變一行 用「,」分隔
↓只適用11up
SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_id)
FROM user_tab_cols
WHERE table_name=UPPER('TABLE名字')


↓9 10
SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(column_name,',')) KEEP (DENSE_RANK LAST
ORDER BY curr),',') AS employees
FROM
(SELECT column_name, ROW_NUMBER() OVER ( ORDER BY column_id) AS curr, ROW_NUMBER() OVER ( ORDER BY column_id) -1 AS prev
FROM user_tab_cols
WHERE table_name=UPPER('TABLE名字')
)
CONNECT BY prev = PRIOR curr
START WITH curr = 1;


另外補充撈出 table 欄位大概資料(型態 是否null size)
DESCRIBE  TABLE名字

[ORACLE] 分組依序編號：row_number()

Base Data:
    DEPTNO ENAME
---------- ----------
        A ASMITH
        A BALLEN
        A CWARD
        B DJONES
        B EMARTIN
        C FBLAKE
        C GCLARK
        C HSCOTT


↓↓↓↓  想要分組有編號


    DEPTNO ENAME SEQ
---------- ------  ----
        A ASMITH 1
        A BALLEN 2
        A CWARD 3
        B DJONES 1
        B EMARTIN 2
        C FBLAKE 1
        C GCLARK 2
        C HSCOTT 3


SELECT DEPTNO, ENAME,row_number() over(partition by DEPTNO ORDER BY ENAME)SEQ FROM XXX

partition by DEPTNO←←依DEPTNO分組

如果沒要分組可省略partition by

[ORACLE] recyclebin

把表drop掉 會產生recyclebin 這是為了可以復原不小心刪除的表

SQL> show recyclebin

可以看到 recyclebin們

可以想成把檔案丟去資源回收桶

如果要回復：
SQL> FLASHBACK TABLE  [誤刪的表名] TO BEFORE DROP;


那如果刪的時候 要直接真的drop掉

DROP TABLE [要刪的表名] PURGE;



如果要清空所有的recyclebin們
PURGE RECYCLEBIN;


如果只要是要刪單一的
PURGE TABLE "[recyclebin名稱]";

[ORACLE] 45個有用的sql查詢

http://viralpatel.net/blogs/useful-oracle-queries/

有空再慢慢看和翻譯...