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.

no-decrypt oracle user

If you don't want to lock user account in oracle database and nobody knows, gets, cracked password of user, you can use this command
SQL> alter user <username>identified by values 'no-decrypt';

For example:
#sqlplus / as sysdba
SQL>  create user passtest identified by passtest;
User created.
SQL> grant connect to passtest;
Grant succeeded.
#sqlplus passtest/passtest
SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 23 13:55:10 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.


#sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 23 13:58:06 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> alter user passtest identified by values 'no-decrypt';
User altered.


#sqlplus passtest/passtest
SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 23 13:59:08 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01017: geçersiz kullanıcı adı/parolası; oturum açma reddedildi

Avoiding ORA-01555 errors in Oracle Active Dataguard

Queries on the standby database rely on undo generated on the primary to rollback uncommitted changes. The undo_retention period set at the primary will determine how soon undo can be overwritten and thus will also determine the length of the timing window that a standby query may avoid running into an ORA-01555 error. The occurrence of ORA-01555 is rare when the active standby is operating in realtime apply mode, because the standby recovery will keep pace with the primary.

An ORA-01555 is more likely to be encountered if real-time apply is not enabled, or in situations where a network disconnect or standby outage has resulted an archive log gap. In these cases the elapsed time to complete a redo log file at the primary database can be longer than the time spent by recovery to apply it to the standby database. Lets use as an example the case where it takes the primary 10
minutes to generate 1000MB of redo and takes a standby only 30 seconds to apply (33MB/sec apply rate). If the undo_retention on the primary is set to 10 minutes, the standby has effectively reduced the retention period to 30 seconds for the standby query workload.  If a standby query runs for more than 30 seconds, it is
likely to run into the ORA-01555 error because recovery has already applied more redo that overwrites the rollback segment. If active standby queries experience ORA-01555 errors, first make sure the standby is in real-time apply mode, and if so, also increase the undo_retention period on the primary database

Resetting/Unsetting parameters in oracle database

You can reset  parameter to default. For example, you set parameter sid wrongly when you config db. Then you can reset it with this command.

SQL> alter system set fast_start_mttr_target=1200 scope=spfile sid='wrong_sid';
System altered.
-- You will see "wrong_sid.fast_start_mttr_target=1200" in pfile.

SQL> alter system reset fast_start_mttr_target scope=spfile sid='wrong_sid';
System altered.
-- Cleaned wrong parameter in parameter file.

Output of sql is different about cursor_sharing between exact and force

DB Version: Oracle 11.2.0.2.0
OS: Linux
Problem:
If cursor_sharing parameter is FORCE and there are function based index, you would get wrong output your SQL. For example:

SQL>CREATE INDEX owner.index_name ("sdate" DESC);
SQL>@script.sql


 Process   sdate
--------- -----------
  9630571 


SQL>Drop INDEX owner.index_name;
SQL>@script.sql


 Process   sdate
--------- -----------
  9630571 11/2/2011


Solutions:
--You can set  cursor_sharing to EXACT. But you should test in test db about all problem. This parameter is so effective in database.
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing string FORCE


SQL> alter system set cursor_sharing=EXACT scope=both sid='*';

-- You couldn't set cursor_sharing to EXACT. download and apply patch for Bug 10259620 and test