Oracle_Dba

How will you find if there is an index created on a table. column table_owner format a15 column table_name format a20 column index_name format a20 column column_name format a20 Select index_owner, table_name, index_name, column_name FROM dba_ind_columns where INDEX_OWNER='schema' AND table_name='tablename';

Hi Team, Here is how you can deal with working on profile and creating role. A :---I have created role “ testinguserrole” on my test database; Step:-1 ----------------------------------- SQL> create role testinguserrole; GRANT CONNECT TO testinguserrole; GRANT SELECT ON ICP_P.CLAIM TO testinguserrole; GRANT SELECT ON ICP_P.CLAIM_LINE TO testinguserrole; GRANT SELECT ON ICP_P.CLAIM_EDIT TO testinguserrole; GRANT SELECT ON ICP_P.CLAIM_LINE_EDIT TO testinguserrole; GRANT SELECT ON ICP_P.CLAIM_LINE_MODIFIER TO testinguserrole; GRANT SELECT ON ICP_P.CLAIM_LINE_DIAGNOSIS TO testinguserrole; GRANT SELECT ON ICP_P.CLAIM_LINE_USER_DEFINED_FIELD TO testinguserrole; to verify the dba_tab_privs for testinguserrole select * from dba_tab_privs where grantee = 'testinguserrole'; Now i am creating user and will assign them the role. SQL> CREATE USER &username IDENTIFIED BY &username   2  DEFAULT TABLESPACE DATA1   3  TEMPORARY TABLESPACE TEMP   4  PROFILE DEFAULT   5  ACCOUNT UN

Hi, Below steps will help you to backup and delete archives 1. BACKUP ARCHIVELOG ALL delete input; This backups up and deletes archive 2. BACKUP ARCHIVELOG UNTIL TIME 'sysdate' NOT BACKED UP 1 times;. This will backup archives not backed-up yet DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'sysdate' BACKED UP 1 times to device type disk; This is amazing deletes archives backup ed one time till 'sysdate'.

I have completed my OCA 10g Certification. Working on OCP now....

Say if you have to drop the database, this can be carried out if the database is registed with RMAN. Easy steps. Verify the Database. export ORACLE_SID=khan $ sqlplus / as sysdba SQL> startup SQL>  select name from v$datafile; SQL> select member from v$logfile; SQL> select name from v$controlfile; SQL> select name , open_mode , log_mode from v$database; SQL> shutdown immediate; On RMAN CONSOLE** Connect to RMAN $ORACLE_HOME/bin/rman catalog=rcat_testdb_khant/rcat@rman RMAN> connect target/ RMAN> startup force mount RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION'; sql statement: ALTER SYSTEM ENABLE RESTRICTED SESSION RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT; This drops the database and backups and unregister the database from catalog Database.  

###Pulling out the Source code/extracting the code We have DBMS_METADATA.GET_DDL  Handy to complete the task. set pagesize 0 set long 90000 SELECT DBMS_METADATA.GET_DDL('PACKAGE',' ') FROM dual; To extract package body  definition from database: set pagesize 0 set long 90000 SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY',' ' ) FROM dual; To extract user definition: set pagesize 0 set long 100000 select dbms_metadata.get_ddl( 'USER', ' ' ) from dual UNION ALL select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', ' ' ) from dual UNION ALL select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', ' ' ) from dual UNION ALL select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', ' ' ) from dual; To extract tablespace definition: SELECT dbms_metadata.get_ddl('TABLESPACE','&&tbs_name') FROM dual; To extract table definition: SELECT dbms_metadata.get_ddl('TABLE',upper(

You can suspend and resume of your query execution without killing the session using oradebug utility. SELECT pid FROM v$process WHERE addr IN ( SELECT paddr FROM v$session WHERE sid = &sid ); ORADEBUG SETORAPID ORADEBUG SUSPEND ORADEBUG RESUME

Hello, Trace file maintenance is a part of DBA activity, to accomplish this script is given below. I have created a script to delete the trace file automatically, you can schedule this in your crontab and trace file maintenance will be taken care. Note:- You may customize this according to your need. Here is the Script code. -------------------------------- #!/bin/ksh . /home/oracle/.profile DATETIME=`date +%Y%m%d` DBA_MAIL_GRP="ghufranahmedkhan@gmail.com" #track script start time echo "" > /u01/app/oracle/admin/scripts_shop/trace_file_deletion_$DATETIME.log 2>&1 echo "$0: Script Start Time: `date`" >> /u01/app/oracle/admin/scripts_shop/trace_file_deletion_$DATETIME.log 2>&1 echo "" >> /u01/app/oracle/admin/scripts_shop/trace_file_deletion_$DATETIME.log 2>&1 #Deleting Trace Files on Production server older than 30 days echo "\nDeleting Trace Files on Production server older than 30 days.."

Having difficulty deleting control file. Use the below command to delete it. Error:-RMAN-06214: Datafile Copy   /bkp/oracle/ora_ctl.ctl Solution --------------- RMAN> crosscheck copy; RMAN> delete expired copy;

Below script will tells us if the database is running on PFILE or SPFILE  SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile'; OR SQL> show parameter spfile; NAME TYPE VALUE —— —— ———————————————— spfile string /u01/apps/oracle/10g/dbs/spfileorclkhan.ora parameter will tell you the location where the SPFILE is present as well.