Lock and Unlock table statistics

--Control stats lock type of table.

SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'WRR$_REPLAY_UC_GRAPH_EXT' and owner = 'SYS';

--lock statistics

exec dbms_stats.lock_table_stats('SYS', 'WRR$_REPLAY_UC_GRAPH_EXT');

--unlock statistics

exec dbms_stats.unlock_table_stats('SYS', 'WRR$_REPLAY_UC_GRAPH_EXT');

How to use DBMS_CRYPTO package

• You can encrypt and decrypt string with  this package. Example: Created p_encrypt package, and encrypt and decrypt string with p_encrypt package.

CREATE OR REPLACE PACKAGE MURATK_DBA.p_encrypt
AS
  FUNCTION encrypt_ssn( p_ssn IN VARCHAR2 ) RETURN RAW;
  FUNCTION decrypt_ssn( p_ssn IN RAW ) RETURN VARCHAR2;
END p_encrypt;
/

CREATE OR REPLACE PACKAGE BODY MURATK_DBA.p_encrypt
AS
--DO NOT FORGET TO WRAP THIS BEFORE LOADING INTO DATABASE
--IF IT IS NOT WRAPPED, THE KEY WILL BE EXPOSED
--THE WRAP UTILITY IS LOCATED IN THE \BIN DIRECTORY (WRAP.EXE)
  G_CHARACTER_SET VARCHAR2(10) := 'AL32UTF8';
  G_STRING VARCHAR2(32) := '12345678901234567890123456789012';
  G_KEY RAW(250) := utl_i18n.string_to_raw
                      ( data => G_STRING,
                        dst_charset => G_CHARACTER_SET );
  G_ENCRYPTION_TYPE PLS_INTEGER := dbms_crypto.encrypt_aes256 
                                    + dbms_crypto.chain_cbc 
                                    + dbms_crypto.pad_pkcs5;
  
  FUNCTION encrypt_ssn( p_ssn IN VARCHAR2 ) RETURN RAW
  IS
    l_ssn RAW(32) := UTL_I18N.STRING_TO_RAW( p_ssn, G_CHARACTER_SET );
    l_encrypted RAW(32);
  BEGIN
    l_ssn := utl_i18n.string_to_raw
              ( data => p_ssn,
                dst_charset => G_CHARACTER_SET );

    l_encrypted := dbms_crypto.encrypt
                   ( src => l_ssn,
                     typ => G_ENCRYPTION_TYPE,
                     key => G_KEY );
                     
    RETURN l_encrypted;
  END encrypt_ssn;
  
  FUNCTION decrypt_ssn( p_ssn IN RAW ) RETURN VARCHAR2
  IS
    l_decrypted RAW(32);
    l_decrypted_string VARCHAR2(32);
  BEGIN
    l_decrypted := dbms_crypto.decrypt
                    ( src => p_ssn,
                      typ => G_ENCRYPTION_TYPE,
                      key => G_KEY );

    l_decrypted_string := utl_i18n.raw_to_char
                            ( data => l_decrypted,
                              src_charset => G_CHARACTER_SET );
    RETURN l_decrypted_string;
  END decrypt_ssn;
  
END p_encrypt;
/

• Connect to database

#sqlplus / as sysdba

SQL> select muratk_dba.p_encrypt.encrypt_ssn('şifrelenecek metin') encrypt_data from dual;

Encrypt_data

--------------------------------------------------------------------------------

991D93B1E8C581931E9CDE8A86B5EACC5102BEAA54473461FF37FCB677ECA2E2

SQL> select muratk_dba.p_encrypt.decrypt_ssn('991D93B1E8C581931E9CDE8A86B5EACC5102BEAA54473461FF37FCB677ECA2E2')  DECRYPT_DATA  from dual;

DECRYPT_DATA

--------------------------------------------------------------------------------

şifrelenecek metin

JDBC Connection of SQL Server Name Instance

• You should find port number of name instance.

http://decipherinfosys.wordpress.com/2008/01/02/finding-the-port-number-for-a-particular-sql-server-instance/

Run Script on name instance.
DECLARE @tcp_port nvarchar(5)
EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =    'SOFTWARE\MICROSOFT\Microsoft SQL Server\<Name_Instance>\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @tcp_port OUTPUT
select @tcp_port

• Then you can connect like that:

To use a port number, do the following:
jdbc:sqlserver://<database_server_name>:<name_instance_port>;integratedSecurity=true;<more properties as required>;
To use a JDBC URL property, do the following:
jdbc:sqlserver://localhost;instanceName=instance1;integratedSecurity=true;<more properties as required>;

Create external table of alert_log from alertlog file

/*
You will have to change:
- Create directory for directory of alertlog file as bdump_dir : CREATE OR REPLACE DIRECTORY BDUMP_DIR AS '/oracle/diag/rdbms/test/test/trace';
- Update <SID> to your sid in table script .
*/
create table alert_log (
  line_number number,
  text varchar2(4000)
)
organization external (
  type oracle_loader
  default directory bdump_dir
  access parameters (
    records delimited by newline
    nobadfile
    nodiscardfile
    nologfile
    fields missing field values are null
    (
      line_number recnum,
      text position(1:4000)
    )
  )
  location ('alert_<SID>.log')
)
reject limit unlimited
noparallel;


SQL> select * from alert_log where text like '%ORA-27037%' ;
LINE_NUMBER         TEXT
-----------                    -------------------------------------------------------

        254 ORA-27037: unable to obtain file status
        262 ORA-27037: unable to obtain file status
        268 ORA-27037: unable to obtain file status
        277 ORA-27037: unable to obtain file status
        285 ORA-27037: unable to obtain file status
        291 ORA-27037: unable to obtain file status
        298 ORA-27037: unable to obtain file status
        306 ORA-27037: unable to obtain file status
        312 ORA-27037: unable to obtain file status

9 rows selected.

Unknow user password is changed to open from expire - dbms_metadata.get_ddl

If application user profile has PASSWORD_LIFE_TIME, application user password could be expired. If you don't know old password, you can reopen account like that:


SQL> set linesize 1000
SQL> create user user_pass identified by "password" ;
User created.
SQL> alter user user_pass PASSWORD  expire;
User altered.
SQL> select username,ACCOUNT_STATUS from dba_users where username='USER_PASS';
USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
USER_PASS                      EXPIRED
SQL> select dbms_metadata.get_ddl('USER', username) || ';' usercreate from dba_users where username='USER_PASS';
USERCREATE
--------------------------------------------------------------------------------
   CREATE USER "USER_PASS" IDENTIFIED BY VALUES 'S:1E268627E76ACBE2A0C750D4FF86C6685514BEE0656BABDA251615E2E9BD;FDFAC641632E49E9'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE;
SQL> ALTER USER "USER_PASS" IDENTIFIED BY VALUES 'S:1E268627E76ACBE2A0C750D4FF86C6685514BEE0656BABDA251615E2E9BD;FDFAC641632E49E9';
User altered.
SQL> select username,ACCOUNT_STATUS from dba_users where username='USER_PASS';
USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
USER_PASS                      OPEN

Truncate table drop all storage - New feature 11.2

Truncate process reduce to initial value from table size before 11.2 versiyon.
New feature of truncate is drop all storage. This sytax reduce to zero from size table.


#Only truncate process:

-------------------------------------------------------------------------------------------------------------------

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 5 16:17:19 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set linesize 1000

SQL> create table muratk_dba.truncate_table as select * from scott.emp;

Table created.

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name ='TRUNCATE_TABLE';

SUM(BYTES)/1024/1024

--------------------

               .0625

SQL> truncate table muratk_dba.truncate_table;

Table truncated.

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name ='TRUNCATE_TABLE';

SUM(BYTES)/1024/1024

--------------------

               .0625

# Truncate process with drop all storage feature

-------------------------------------------------------------------------------------------------------------------------------

SQL> drop table muratk_dba.truncate_table purge;

Table dropped.

SQL> create table muratk_dba.truncate_table as select * from scott.emp;

Table created.

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name ='TRUNCATE_TABLE';

SUM(BYTES)/1024/1024

--------------------

               .0625

SQL> truncate table muratk_dba.truncate_table  drop all storage;

Table truncated.

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name ='TRUNCATE_TABLE';

SUM(BYTES)/1024/1024

--------------------

SQL> drop table muratk_dba.truncate_table purge;

Table dropped.

Privileges of deployment user

If you have deployment user, after this user creates new table, you have DML procesess in new table. Create role for deployment user, grant privileges to this role because deployment user may not grant/revoke  to/from yourself.

For Example:
oracle@dbatest> sqlplus / as sysdba
SQL> create user kapsul identified by kapsul;
User created.
SQL> grant connect,resource to kapsul;
Grant succeeded.
SQL> GRANT GRANT ANY OBJECT PRIVILEGE TO KAPSUL;
Grant succeeded.
SQL> grant CREATE ANY TABLE to kapsul;
Grant succeeded.
SQL> create role role_kapsul;
Role created.
SQL> grant role_kapsul to kapsul;
Grant succeeded.

----------------------------

oracle@dbatest> sqlplus kapsul/kapsul

SQL> create table scott.kapsul_table (sayi number);

Table created.

SQL> insert into scott.kapsul_table sayi values (3);

insert into scott.kapsul_table sayi values (3)

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> grant insert,update,select,delete on scott.kapsul_table to kapsul;

grant insert,update,select,delete on scott.kapsul_table to kapsul

ERROR at line 1:

ORA-01749: you may not GRANT/REVOKE privileges to/from yourself

SQL> grant insert,update,select,delete on scott.kapsul_table to role_kapsul;

Grant succeeded.

SQL> insert into scott.kapsul_table sayi values (3);

1 row created.

SQL> commit;

Commit complete.