第25個冬天

PL-SQL筆記1_OracleOracle Database 21c Express Edition下載安裝筆記 https://coolmandiary.blogspot.com/2023/04/oracle-database-21c-express-edition.html 在個人電腦上練習Oracle時候 發覺太久沒登入忘記密碼多次輸入後被鎖住了QQ Step1.以管理員身份連接到數據庫 sqlplus / as sysdba Step2.確查目前有哪些帳號被鎖(或指定該帳號名稱)確認當前帳戶的LOCK狀態 SELECT username, account_status FROM dba_users WHERE username = 'username' ; Step3.解鎖用戶帳戶 ALTER USER 用戶名 ACCOUNT UNLOCK; Step4.忘記了密碼，修改用戶密碼 ALTER USER username IDENTIFIED BY new_password; Step5.重登一次試試看就皆大歡喜。

File > New > Diagram Window 將table物件給拖曳進來會自動顯示表與表之間關聯 Ref: https://forums.allroundautomations.com/ubb/ubbthreads.php?ubb=showflat&Number=39589 https://origin2.cdn.componentsource.com/sites/default/files/resources/allround-automations/680211/plsqldeveloper1306-manual.pdf

方法1.(最全面) select * FROM all_VIEWS where VIEW_NAME = '<view_name>' ; 方法2. select DBMS_METADATA.GET_DDL ( 'VIEW' , 'view_name' , 'owner' ) from dual; 方法3.某view有reference到哪些table跟view呢? select owner as view_schema, name as view_name, referenced_owner as referenced_schema, referenced_name as referenced_name, referenced_type from sys.all_dependencies where type = 'VIEW' and referenced_type in ( 'TABLE' , 'VIEW' ) -- and owner = 'SCHEMA_NAME' -- put schema name here -- and name = 'VIEW NAME' -- put view name here order by owner , view_name; Ref: Get Oracle View Definition with limited control https://stackoverflow.com/questions/31104012/get-oracle-view-definition-with-limited-control Find tables and views used by specific view in Oracle database https://dataedo.com/kb/query/oracle/find-tables-used-by-specific-view

  方法1.藉由V$VERSION 的View SELECT BANNER, BANNER_FULL FROM v $ version ; 方法2.藉由 PRODUCT_COMPONENT_VERSION 的View SELECT VERSION , VERSION_FULL FROM PRODUCT_COMPONENT_VERSION; Ref: https://www.beekeeperstudio.io/blog/how-to-check-oracle-version-and-edition https://database.guide/7-ways-to-check-your-oracle-version/