mzh

Script for getting Oracle table size: There is no oracle defined function for getting size of a table. After all if it is easy with one simple query who will require a function. There will be a prompt, enter USERNAME to view tables of a particular user. SELECT owner, table_name, TRUNC(sum(bytes) / 1024 / 1024) Meg   FROM (SELECT segment_name table_name, owner, bytes           FROM dba_segments          WHERE segment_type = 'TABLE'         UNION ALL         SELECT i.table_name, i.owner, s.bytes           FROM dba_indexes i, dba_segments s          WHERE s.segment_name = i.index_name            AND s.owner = i.owner            AND s.segment_type = 'INDEX'         UNION ALL         SELECT l.table_name, l.owner, s.b...

VI Editor Commands General Commands: To use vi: vi filename To exit vi and save changes: ZZ   or   :wq To exit vi without saving changes: :q! To enter vi command mode: [esc] Cursor Movement $       last column on the current line 0       move cursor to the first column on the current line ^       move cursor to first nonblank column on the current line w       move to the beginning of the next word or punctuation mark W       move past the next space b       move to the beginning of the previous word or punctuation mark B       move to the beginning of the previous word, ignores punctuation         e       end of next word or punctuation mark         E       end of next word, ignoring punctuation         H       ...

Turning Off OS Authentication in ORACLE You can 'disable' the OS auth of sysdba by putting this line into sqlnet.ora. i.e. users will not be able to enter as sys dba without providing passwords. SQLNET.AUTHENTICATION_SERVICES=(NONE) However, it could be 'enabled' just as easy by removing it. _________________________________________________________________________________

Segment Advisor for TABLES using DBMS_ADVISOR The segment advisor performs analysis on the fragmentation of specified tablespaces, segments or objects and makes recommendations on how space can be reclaimed. The advisor is accessible from Enterprise Manager (Home) > Advisor Central > Segment Advisor) or from PL/SQL by using the DBMS_ADVISOR package: Segment Advisor Using DBMS_ADVISOR:  -- Create a segment advisor task for the  COR_TRANS_GL_HIST table. DECLARE   l_object_id  NUMBER; BEGIN -- Create a segment advisor task for the ULTIMUS.COR_TRANS_GL_HIST table. DBMS_ADVISOR.create_task (     advisor_name    => 'Segment Advisor',     task_name         => 'GL_HIST_SEGMENT_ADVISOR',     task_desc          => 'Segment Advisor For COR_TRANS_GL_HIST'); DBMS_ADVISOR.create_object (     task_name   => 'GL_HIST_SEGMENT_ADV...

Segment Advisor for TABLESPACES using DBMS_ADVISOR The segment advisor performs analysis on the fragmentation of specified tablespaces, segments or objects and makes recommendations on how space can be reclaimed. The advisor is accessible from Enterprise Manager (Home > Advisor Central > Segment Advisor) or from PL/SQL by using the DBMS_ADVISOR package: Segment Advisor Using DBMS_ADVISOR:  -- Create a segment advisor task for the BU_HIS_LOG_TBS tablespace.   DBMS_ADVISOR.create_task (     advisor_name      => 'Segment Advisor',     task_name         => 'USERS_SEGMENT_ADVISOR4',     task_desc         => 'Segment Advisor For USERS');   DBMS_ADVISOR.create_object (     task_name   => 'USERS_SEGMENT_ADVISOR4',     object_type => 'TABLESPACE',     attr1       => 'BU_HIS_LOG_TBS', --     ...

Case Sensitive Passwords in Oracle Database 11g Release 1: Case sensitive passwords (and auditing) are a default feature of newly created Oracle 11g databases. The Database Configuration Assistant (DBCA) allows you to revert these settings back to the pre-11g functionality during database creation. The SEC_CASE_SENSITIVE_LOGON initialization parameter gives control over case sensitive passwords. If existing applications struggle to authenticate against 11g, you can use the ALTER SYSTEM command to turn off this functionality. SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON; NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ sec_case_sensitive_logon             boolean     TRUE SQL> SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE; System...

Create USER: create user username identified by password default tablespace users temporary tablespace temp quota 50M on users; Create ROLE: CREATE ROLE ROLE_NAME ; Granting Privilege: GRANT SELECT, UPDATE ON table_name TO role_name ; GRANT SELECT ON table_name TO role_name ; Grant Column Privilege: GRANT SELECT, UPDATE (ISSUE_BR_ID,DRAWN_BR_ID,AMOUNT_CCY,AMOUNT_LCY,ORGN_BR_ID,RSPD_BR_ID,ADV_NO,ADV_RECEIVED_STATUS,entry_br_id,PAYMENT_STATUS) ON COR_BRM_INFO TO HELP_DESK; GRANT SELECT, UPDATE (ORGN_BR_ID,RSPD_BR_ID,ADVICE_NO,NARRATION) ON COR_IBT_ORGN_REG TO HELP_DESK; The following query will help you to generate a set of query of a database user 'ULTIMUS': select 'GRANT SELECT ON ULTIMUS.' || t.table_name ||        ' to ZAKIR;'   from all_tables t  where t.owner = 'ULTIMUS'    --and t.table_name like 'TFN_%'  order by t.table_name The query can help you to find out the table privilege of any given user...