Tablespaces used by a user ?

Tablespaces used by a user ?

select    tablespace_name
,    ceil(sum(bytes) / 1024 / 1024) "MB"
from    dba_extents
where    owner like '&USERNAME_IN_CAPS'
group by tablespace_name
order by tablespace_name

*******************

Count No .Of Rows in table

 

Count No .Of Rows in tables under a schema (anonymous proc).

run as sys:



declare
--
-- Anonymous pl/sql code to count rows in tables of interest for v8.1+
--
-- basic logic
-- create list of target tables (cursor)
-- while more tables in list
-- dynamically generate select count
-- print or store results
-- ---------------------------------------------------------------------

v_ct number := 0 ;
v_sqlcode number := 0 ;
v_stmt varchar2(90) ;
-- modify cursor select for tables of interest, order by.
cursor c_tbl is
select owner, table_name
from sys.dba_tables
where owner = '&OWNER';
--
r_tbl c_tbl%rowtype;
--
begin
open c_tbl;
loop
fetch c_tbl into r_tbl;
exit when c_tbl%notfound;
v_stmt := 'select count(*) from '||r_tbl.owner||'.'||r_tbl.table_name;
execute immediate v_stmt into v_ct;
v_sqlcode := SQLCODE;
if v_sqlcode = 0
-- An insert into a row count history table should probably be here
then dbms_output.put_line('Table '||r_tbl.owner||'.'||
rpad(r_tbl.table_name,30)||
' count is '||to_char(v_ct,'999999999990')
);
else dbms_output.put_line('Bad return code'||v_sqlcode||
' on select of '||r_tbl.owner||
'.'||r_tbl.table_name
);
end if;
end loop;
close c_tbl;
end;

******************

Schema info

How to get details of a schema including objects and their counts?

rum as the user:


select object_type,count(*)
from user_objects
group by object_type
order by object_Type desc;

How to check the size and total no. of objects owned by all schemas in a db?

run from sys:


set pages 999
col "size MB" format 999,999,999
col "size GB" format 999,99
col "Objects" format 999,999,999
select    obj.owner "Owner"
,    obj_cnt "Objects"
,    decode(seg_size_gb, NULL, 0, seg_size_gb) "size GB"
,decode(seg_size, NULL, 0, seg_size) "size MB"
from     (select owner, count(*) obj_cnt from dba_objects group by owner) obj
,    (select owner,ceil(sum(bytes)/1024/1024/1024) seg_size_GB, ceil(sum(bytes)/1024/1024) seg_size
    from dba_segments group by owner) seg
where     obj.owner  = seg.owner(+)
order    by 3 desc ,2 desc, 1;
/

 /****   To find size of a Table or table partition  ********/..

run as sys:



  select
  owner as "Schema"
  , segment_name as "Object Name"
  , segment_type as "Object Type"
  , round(bytes/1024/1024,2) as "Object Size (Mb)"
  , tablespace_name as "Tablespace"
from dba_segments
where owner like 'SCHEMA' 
and segment_type like '%TABLE%'
and segment_name like 'Name of the table'
order by segment_name

*****************

Import data from Excel to Oracle table using TOAD

 How to import data from Excel file to Oracle table using Toad

 

In some cases developers need to import excel file date into a oracle table. Here in this article i will explain step by step how one can import excel data into oracle table using TOAD.

The following are the steps to follow import data from Excel sheet to a Oracle Table:

First the Table must exist with the same or equal number of colums as that in the excell sheet

.

Now from TOAD go to:
1. Database-->Import-->Table Data
2. Select the proper schema and Table name where data should be inserted
3. Click "Execute Wizard" and specify the type of file to be imported
4. Select "Excel File (.xls)" and click next
5. Give the path of the file, and again click next
6. Finally click execute..

**************

users account locked.

IF your oracle user account get locked frequently here is the solution

In 11g onwards there is a small limitation of the password life time in default prfiles.
The users who use default profiles will get affected(locked) because of this.

In previous verions (<11g) is it by default set to unlimited, but in 11g onwards as a 
security measure its value limited to 180 days. Users a/c will lock when they reach 180
days after when they created. 

we can change the value as we want.


To find user and his profile
SELECT username, account_status,profile FROM dba_users WHERE username= 'APPUSR';

To see the profile contents/limitaions
SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';



All resource limits for DEFAULT profile is set to UNLIMITED,
but only for FAILED_LOGIN_ATTEPTS attribute, it’s set to some value (10).

Due to this the user account keeps getting locked(timed).When we check in
the Oracle Documentations, it’s stated that FAILED_LOGIN_ATTEPTS attribute
for DEFAULT profile has been changed from 10.2.0.1 from UNLIMITED to 10.



What we can do is, either we may need to change the resource limit for FAILED_LOGIN_ATTEPTS attribute in DEFAULT profile,
or 
create a new profile for that user with FAILED_LOGIN_ATTEPTS attribute value set to UNLIMITED. But for security reasons,

we will not tamper the DEFAULT profile, which is not recommended too. Then let’s go for creating a new profile and assign
that profile to the user.

Creating a profile.


SQL>CREATE PROFILE APPUSR_DEFAULT LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED;

Profile created.

Assign the newly created profile to the user as default profile.

SQL> ALTER USER appusr PROFILE appusr_default;

User altered.


later we can chenge the values like

alter profile appusr_default set failed_login_attempts = 4;


Some parameters & their descriptions
===========================
failed_login_attempts -  This is the number of failed login attempts before locking the Oracle user account.
                                 The default is three failed attempts.

password_grace_time - This is the grace period after the password_life_time limit is exceeded. 


password_life_time -    This is how long an existing password is valid. The default here forces a password        
                                    change every 60 days.


password_lock_time –  This specifies how long to lock the account after the failed login attempts is met.
                                   Most DBA’s set this value to UNLIMITED.

password_verify_function - This allows you to specify the name of a custom password verification function.
                                      
Most confusing parameters are..

password_reuse_time  &  password_reuse_max

These two parameters must be set in conjunction with each other.
PASSWORD_REUSE_TIME :specifies the number of days before which a password cannot be reused.
PASSWORD_REUSE_MAX :specifies the number of password changes required before the current   
                                                 password can be reused.

If you specify an integer for both of these parameters,

then the user cannot reuse a password until the password has been changed the password 
the number of times specified for PASSWORD_REUSE_MAX 
during the number of days specified for PASSWORD_REUSE_TIME.

For example, if you specify

PASSWORD_REUSE_TIME       to 30
and PASSWORD_REUSE_MAX to 10,

then the user can reuse the password after 30 days if the password has already been changed 10 times.

**************

Invalid Objects

To know about invalid objects

Select * from user_objects where status like 'INVALID';

 

Invalid objects  with their compilation commands with in a schema

      select     'ALTER ' || object_type || ' ' || object_name ||' COMPILE' || '; '    
                    SCRIPT_FOR_INVALID_COMPILATION
      from       user_objects
      where     status like 'INVALID';


a small thing we have to note is:

For  packages it will show commands to compile package & package body seperatley.

There is no seperate command for compiling package body.

For compiling PACKAGES use the below command

"alter package packag_name compile" (is enough).

*****************

How to add new lines in oracle pl/sql

 

​​We can add new lines in plsql by using  ​

DBMS_OUTPUT.PUT ( CHR(10));​

​Exapmle:​

​

SQL> set serveroutput on;

declare

c1 number;

c2 number;

begin

select count(*) into c1 from dba_users;

select count(*) into c2 from dba_objects;

DBMS_OUTPUT.PUT ( CHR(10));

DBMS_OUTPUT.PUT ( CHR(10));

DBMS_OUTPUT.PUT ( CHR(10));

DBMS_OUTPUT.put_line('****** ********************** *****');

dbms_output.put_line('USERS:> '||c1);

dbms_output.put_line('OBJECTS:> '||c2);

DBMS_OUTPUT.put_line('****** ********************** *****');

end;

/

*****************

Rman commands

Some RMAN Queries..

FULL DATABASE BACKUP USING RMAN
================================

os> rman connect target /


run {
allocate channel disk_1 type disk;
backup format '/SAN/rman_backup/inc_0_datafiles.dbf' database;
sql 'alter system archive log current';
backup  format '/SAN/rman_backup/archives_backup.arc_bkp'  archivelog all;
backup format '/SAN/rman_backup/cntrl_backup.cntrl_bkp'  current controlfile;
release channel disk_1
     }



With Example

I started to take full rman database backup with the previous commands.

For cheking the status of rman operation, how much it is completed & approximatley when will finish etc.
by using below command.


SQL> alter session set nls_date_format='dd/mm/yy hh24:mi:ss';                              

Session altered.


SQL> select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 PERdone,
          sysdate + TIME_REMAINING/3600/24 Expected_to_end_at
          from v$session_longops
          where totalwork > sofar
          AND opname NOT LIKE '%aggregate%'
          AND opname like 'RMAN%';



will get o/p as like this

For knowing Sesion id and wait time,spid we can use below query

wait time:  Time waiting to get resource
spid       :  os process id, in Linux we can see by "top" command
                If needed we can kill from os level by using "kill -9 spid"



   select
       sid, spid, client_info, event,seconds_in_wait, p1, p2, p3
       from  v$process p, v$session s
       where p.addr = s.paddr
       and client_info like 'rman channel=%';

Inorder to get a over all status of rman backups use the below query

select command_id,operation,status,input_bytes,
mbytes_processed,start_time,output_device_type
from   v$rman_status order by start_time;

Tablespaces & Datafiles

Tablespaces & Their Datafiles information..

No. of Datafiles in each Tablespaces.

SELECT  Tablespace_name,COUNT(file_name) no_OF_DATAFILES
FROM dba_data_files
GROUP BY TABLESPACE_NAME
ORDER BY TABLESPACE_NAME;



Tablepsaces & Their datafiles info.

SELECT  Tablespace_name,file_name, ROUND(bytes/1024000) USED_MB
FROM dba_data_files
ORDER BY 1;

Querry to f ind  Tablespaces , datafiles, auto extensiblity, usage statitics

SELECT    tablespace_name, file_name file_id, BYTES / 1024 / 1024 AS mb,
autoextensible, increment_by * 8192 / 1024 / 1042 AS next_mb,
maxbytes / 1024 / 1024 AS max_mb 
FROM    dba_data_files
ORDER BY tablespace_name, file_id;