How to login to sqlplus with username which has special characters in password??

Hi friends,

Today I will show you small demostration of how to connect to the database with username which has special character in their password.

When your password has special character like "@" which we use for connect discriptor then see the following solution.

C:\Users\oradb>sqlplus hr/"hr@"@orcl
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 12 18:02:38 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

C:\Users\oradb>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 12 18:02:53 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL> conn hr/"hr@"@orcl
Connected.
SQL>

How to recover dropped table in 10g onwords?

Today, will look into how to recover deleted table?
10G Onwords Oracle introudces new feature of recycbin. ( Same concept as windows recyclebin)
Suppose from XYZ schema accidentely you dropped the table which is one of the important table in your database.
the table and its data still availble in "bin".
What Oracle does when you delete the table?
Oracle Simply rename table with differnt tablename start with "BINsomevalue".
Make sure you enable this recyclebin
SQL> show parameter recyclebin;
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
recyclebin                           string      on

Simply login to the scheama & fire command "show recyclebin;"


Here is demo for you.
-
SQL> conn scott
Enter password:
Connected.
SQL> desc emp1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> drop table emp1;
Table dropped.

SQL> desc emp1;
ERROR:
ORA-04043: object emp1 does not exist

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP1             BIN$nChBttgnS/6uj2vZgTB9Ow==$0 TABLE        2012-09-07:17:58:13

SQL> flashback table emp1 to before drop;
Flashback complete.


SQL> desc emp1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

This way you recovered your dropped table.

How to Empty undo tablespace & reclaim space?

Hi,
Over the period of time Undo tablespace size (OS level) grow but actual size of it is small
then How to Empty undo tablespace & reclaim space?
empty undo tablespace
--follow steps if undo tablespace growing

--Create New UNDO tablespace UNDOTBS2

create undo tablespace UNDOTBS2 datafile 'D:\app\Administrator\oradata\DMS\undotbs101.dbf' size 1024M;

--Set default undo_tablespace to new undo tbs

alter system set undo_tablespace='UNDOTBS2' scope=both;

--Drop UNDOTBS1

drop  tablespace UNDOTBS1 including contents and datafiles;

--Create UNDO tbs UNDOTBS1

create undo tablespace UNDOTBS1 datafile 'D:\app\Administrator\oradata\DMS\undotb101.dbf' size 1024M;

--Set default undo_tablespace to UNDOTBS1

alter system set undo_tablespace='UNDOTBS1' scope=both;

--Drop UNDOTBS2

drop  tablespace UNDOTBS2 including contents and datafiles;

How to Empty Temp tablespace?

Hi,
Over the period of time if temp tablespace size (OS level) grow but actual size of it is small.
then How to Empty temp tablespace?

--Create another temporary tablespace temp2

SQL> CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/shreyas/temp02.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

--Make temp2 as default temp tbs

alter database default temporary tablespace temp2;

--Drop temp tablespace

 drop tablespace temp including contents and datafiles;

--Create temp tablespace

SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE '/shreyas/temp01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

--Make temp as default temp tbs

alter database default temporary tablespace temp;

--Drop temp2 tablespace

drop tablespace temp2 including contents and datafiles;

Basic Scripts

Check Database Status

set lines 200
col startup_time for A25
col host_name for A25
alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss';
select sysdate,instance_name,startup_time,open_mode,log_mode,host_name from v$instance, v$database;







Total database size,free space,used space on db level

select
sysdate "DATE",
i.instance "INSTANCE",
a.TOTAL_GB,
u.USED_GB,
f.FREE_GB
from (select instance_name instance from v$instance) i,
(select round(sum(bytes/1024/1024/1024),2) TOTAL_GB from dba_data_files) a,
(select round(sum(bytes/1024/1024/1024),2) USED_GB from sm$ts_used) u,
(select round(sum(bytes/1024/1024/1024),2) FREE_GB from sm$ts_free) f;







 Total database size,free space,used space on TBS level

select
sysdate "DATE",
i.instance "INSTANCE",
f.tablespace_name,
a.total_mb,
u.used_mb,
f.free_mb,
f.maxfree_mb
from (select instance_name instance from v$instance) i,
(select tablespace_name, sum(bytes/(1024*1024)) total_mb from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) used_mb from dba_extents group by tablespace_name) u,
(select tablespace_name, round(sum(bytes/(1024*1024))) free_mb,round(max(bytes)/1024/1024) maxfree_mb from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name
order by f.free_mb;

 





 Free space of temp tablespace.

select tablespace_name,bytes_used/1024/1024 "USED_MB",bytes_free/1024/1024 "Free Mbs",(bytes_used+bytes_free)/1024/1024 "TOTAL MB"  from v$TEMP_SPACE_HEADER;

 

 

 

Find top 5 segments size in DB

select * from (select owner,segment_name,segment_type,bytes/1024/1024"Mb" from dba_segments order by bytes/1024/1024 desc) where rownum<5;