Database start time:
++++++++++++++++
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM sys.v_$instance
check the tablespace size
--------------------------------------
set linesize 200
set pagesize 300
select t.tablespace, t.totalspace as " Totalspace(MB)",round((t.totalspace-fs.freespace),2) as "Used Space(MB)",
fs.freespace as "Freespace(MB)",round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used",
round((fs.freespace/t.totalspace)*100,2) as "% Free" from
(select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace
from dba_data_files d group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f
group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace;
select min(creation_time),df.ts#,ts.name,sum(df.bytes)/1024/1024 curr_size_mb,
(sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) growth_per_day,
(sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) * 120 growth_120_days,
(sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) * 240 growth_240_days,
(sum(bytes)/1024/1024)+((sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time))*365) projection_365_days_mb
from v$datafile df ,v$tablespace ts where df.ts#=ts.ts# group by df.ts#,ts.name order by df.ts#;
check the tablespace size in percentages
-----------------------------------------------------------
select a.tablespace_name, a.alloc_megs, b.total_megs, ( ( b.total_megs - a.alloc_megs ) / b.total_megs) * 100 as "Percentage Used" from alloc_space a,avail_space b where a.tablespace_name = b.tablespace_name;
check the database size
-------------------------------------
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size",
round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space",
round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used,
(select sum(bytes) as p
from dba_free_space) free
group by free.p;
SQL>select 'Complete Database Size is', sum(bytes/1024/1024/1024) tt, 'Gb.' from sys.dba_data_files;
find the datafiles details of tablespaces
--------------------------------------------------------
select file_name,tablespace_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024,maxblocks,increment_by from dba_data_files where tablespace_name='SYSTEM';
other activities in database
--------------------------------------
select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;
select profile,resource_name,limit from dba_profiles order by profile;
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;
select table_name,constraint_name,status from dba_constraints where constraint_name='SYS_C008302';
select count(*),OBJECT_TYPE,owner from dba_objects where owner NOT IN ('SYSTEM','SYS','QUEST') group by OBJECT_TYPE,owner;
select owner,last_analyzed,num_rows from dba_tables where table_name=''
select bytes/1024/1024,segment_name from dba_segments where segment_type='INDEX' and owner='QUEST' order by 1;
select TABLESPACE_NAME,SEGMENT_NAME,EXTENTS from dba_segments where segment_name='AUD$';
select 'alter system kill session '||''''||sid||','||serial# ||''''||' immediate ;' from v$session where trunc(logon_time)<sysdate;
select sid||','||username||','||osuser||','||to_char(logon_time,'DD-MON-YY HH24:MI') from v$session where program like '%TOAD%' and logon_time < sysdate-4 order by logon_time;
select sid,serial#,logon_time from v$session where upper(program) like '%TOAD%';
select holding_session from dba_blockers a where not exists (select 'x' from dba_waiters b where b.waiting_session = a.holding_session)
select default_tablespace from dba_users ;
select username,account_status from dba_users where username like ''
select PRIVILEGE from dba_sys_privs where GRANTEE=MULLIGAN_N';
select GRANTED_ROLE from DBA_ROLE_PRIVS where GRANTEE='HSERVER';
select PRIVILEGE from dba_sys_privs where GRANTEE='HSERVER';
select sum(bytes/1024/1024) from dba_segments where owner='O2OMDML';
select * from v$access where object=''
select tablespace_name, username, bytes, max_bytes from dba_ts_quotas where username='MKALDAS';
select count(*),trunc(first_time) from v$log_history group by trunc(first_time);
select sum(bytes/1024/1024/1024) from dba_segments;
select sum(bytes/1024/1024) from dba_segments where segment_name='AUD$';
select sum(bytes/1024/1024) from dba_segments where owner='EPIMM';
select tablespace_name,bytes/1024/1024/1024 from sm$ts_avail order by 1;
select tablespace_name,bytes/1024/1024/1024 from sm$ts_used order by 1;
select tablespace_name,bytes/1024/1024/1024 from sm$ts_free order by 1;
select bytes/1024/1024 as mb,tablespace_name from sm$ts_free where tablespace_name='';
select bytes/1024/1024,file_name from dba_data_files where tablespace_name='UNDOTBS1'
select bytes/1024/1024 as mb,tablespace_name from sm$ts_avail;
select bytes/1024/1024 as mb,tablespace_name from sm$ts_used;
select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;
select sid,serial#,status,logon_time from v$session where program like 'rman%';
select count(*) from v$session where type='USER';
select count(*),trunc(logon_time) from v$session where type='USER' group by trunc(logon_time);
select fuzzy,count(*) from v$datafile_header group by fuzzy;
select sid,failures,last_date,this_date from dba_jobs_running where job in (' ');
select sid,serial#,username from v$session where lockwait is not null;
select * from gv$session where lockwait is not null;
select * from v$session where lockwait is not null;
s
select HOLDING_SESSION from dba_blockers where HOLDING_SESSION not in (select WAITING_SESSION from dba_waiters);
select inst_id from gv$instance;
select inst_id,username,count(*) from gv$session group by inst_id,username;
select hash_value from v$sql where sql_text like ' ';
select hash_value,sql_text from v$sqltext where hash_value = '&1' order by piece;
select * from gv$lock where block <1;
select TABLESPACE_NAME,
2 ONLINE_STATUS from dba_data_files where TABLESPACE_NAME='amo_msoffline_std';
select file_name from dba_temp_files;
select memeber from v$logfile;
select name from v$controlfile;
select open_mode from v$database;
select instance_name from v$instance;
select name from v$tablespace;
select name,status from v$datafile;
select temporary_tablespace from dba_users;
select * from dba_synonyms where owner='';
select username,owner,action_time,tiemstamp from dba_audit_objects where object_name='';
select max(sequence#) from v$log_history;
select host_name from v$instance;
select host from dba_db_links;
select dbid from v$database;
select * from registry$history;
select * from dba_registry;
select * from v$backup;
select * from v$recoverfile;
select distinct status from v$datafile;
select TEXT from dba_source where name='VERIFY_FUNCTION';
select * from V$PWFILE_USERS;
select * from ejbtimer;
===========================================================================================================================
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
alter user username identified by
alter database datafile '' offline drop;
alter database datafile 'PATH' resize ;
alter database open;
alter database default temporary tablespace '';
alter user QUEST default tablespace QUEST_INDEX;
alter tablespace LARGE_FACT_X add datafile '/u20/oradata/GDWQ/LARGE_FACT_x02.dbf' size 2850m;
alter database backup controlfile to trace;
alter profile O2_USER_PROFILE limit PASSWORD_VERIFY_FUNCTION NULL
alter user SRIRAM_S PASSWORD EXPIRE;
ALTER USER C_NALABOTHU_R QUOTA 10M ON USR;
alter view STAGING.TD_D_SCORE_V compile;
CREATE INDEX "IX_sms_sentstate_3" ON "sms_sentstate"(CustomerID) TABLESPACE "O2OM_DATA_INDEX_01";
===========================================================================================================================
drop tablespace tablespace_name including contents and datafiles;
drop temporary tablespace tablespace_name including contents and datafiles;
drop user username;
drop user username cascade;
drop table tablename purge;
drop table tablename cascade constraints;
drop table tablename;
====================================================================================================
find tablespaces is online or offiline :
select tablespace_name, online_status from dba_data_files where tablespace_name='tablespace_name';
find tablespaces is autoexten, tbalespace name :
select file_name , tablespace_name, autoextensible from dba_data_files;
find the privilege in database;
select privilege||' '||table_name "Privileges for " from dba_tab_privs where grantee='&data'
union
select privilege from dba_sys_privs where grantee='&data'
union
select granted_role from dba_role_privs where grantee='&data';
10:33:13 AM: Lakshmidevi V: this is to check all the privs for a user
SQL> alter user ISMDBA11 account unlock;
User altered.
SQL> select USERNAME,PASSWORD,ACCOUNT_STATUS,PROFILE from dba_users;
SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc;
SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc;
Use this query to find the session information from OS Process id
----------------------------------------------------------------------------------------------------------------------
select sid,serial#,status,machine,program,sql_hash_value,to_char(sysdate,'DD/MM/YYYY HH24:MI') from v$session
where paddr in (select addr from v$process where spid=&spid)
/
select spid from v$process where addr =(select paddr from v$session
where sid=&sid)
/
====================================================================================================
find . -name "030609_GSM.txt" -print (for searching a file from the filesystem)
find . -print|xargs grep -i v\$session
find . -mtime -1 -print (To find Recently created files)
find . -size +10000 -print (find largest files on the server)
find . -mtime +7 (Delete files in bulk)
find . -size +100000 -exec ls -alrt {} \;
find . -size +10000 -exec ls -alrt {} \;
find / -name Chapter1 -type f -print
find /usr -name Chapter1 -type f -print
find /usr -name "Chapter*" -type f -print
find /usr/local -name "*.html" -type f -print
find /usr/local -name "*.html" -type f -exec chmod 644 {} \;
find . -type f -exec ls -l {} \;
find . -type d -exec ls -ld {} \;
find . -type f -name "Poop*" -exec rm {} \;
find . -type d -name CVS -exec rm -r {} \;
find . -type f -name -exec chmod 644 {} \;
find . -type d -name -exec chmod 755 {} \;
locate lost-file
====================================================================================================
Memory Check
$prtconf | grep "Mem" (Solaris)
$prtconf|grep -i mem (Solaris)
$lsdev -C|grep mem (AIX)
$ipcs -pmb (Allocated memory Segments)
$lsdev -C|grep Process|wc -l (Dispays the number of CPU's)
$psrinfo -v|grep "Status of processor"|wc -l (Dispays the number of CPU's in Solaris)
$kmtune|grep sem (Displays values for semaphores)
$grep error /var/adm/syslog/syslog.log|more (Show server log in HP-UX)
$errpt -a|more (Show server log in AIX)
$lsps -a (Displays swap usage in AIX)
$swapinfo -tam (Displays swap usage in HP-UX)
====================================================================================================
Script to create DB link
select 'create database link '||DB_LINK||' connect to '||USERNAME||' identified by d3vf0rsys# using '''|| HOST||''';' from dba_db_links
where DB_LINK IN (SELECT SID||'_'||SERVER FROM database_list where USAGE='PROD');
select 'create database link '||DB_LINK||' connect to '||USERNAME||' identified by ' ||
substr(a.SERVER,0,1)||substr(a.server,(length(a.SERVER)),1)||'br0adc#st using '''|| HOST||''';' from dba_db_links,ops$oracle.database_list a
where DB_LINK IN (SELECT SID||'_'||SERVER FROM ops$oracle.database_list where USAGE='PROD')
and substr(DB_LINK,1,instr(DB_LINK,'_')-1)=a.sid
Script to Drop DB link
select 'drop database link '||DB_LINK||';' from dba_db_links
where DB_LINK IN (SELECT SID||'_'||SERVER FROM database_list where USAGE='DEV');
select 'create database link '||DB_LINK||' connect to '||USERNAME||' identified by ' ||
substr(a.SERVER,0,1)||substr(a.server,(length(a.SERVER)),1)||'d3cis1#n using '''|| HOST||''';' from dba_db_links,ops$oracle.database_list a
where DB_LINK IN (SELECT SID||'_'||SERVER FROM ops$oracle.database_list where USAGE='DEV')
and substr(DB_LINK,1,instr(DB_LINK,'_')-1)=a.sid
===================================================================================================
SNAPSHOT_FEATURE PROCEDURE (REBUILD)
select OWNER,NAME,MASTER_OWNER,MASTER,to_char(LAST_REFRESH,'dd/mm/yyyyhh24:mi') from DBA_SNAPSHOT_REFRESH_TIMES where name like '%SERVICE_FEATURE%'
select host,OWNER from dba_db_links where db_link='CUSTDB.WORLD';
select OWNER,NAME,RNAME,REFGROUP,JOB,NEXT_DATE from DBA_REFRESH_CHILDREN;
nohup ./run.sh @snapshot_rebuild.sql &
BEGIN
DBMS_REFRESH.ADD(
name => '"DBLUSGO"."MPSGUIDE_SNAPS"',
list => '"DBLUSGO"."SERVICE_FEATURE_SNP"',
lax => TRUE);
END;
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'DBLUSGO', tabname => 'SERVICE_FEATURE_SNP', cascade => TRUE, estimate_percent => 5, method_opt => 'FOR ALL COLUMNS SIZE 1');
====================================================================================================
This script provides the backup status of the datafiles in the database.
This is a good check to perform on an oracle database before the start of every business day to make sure that none of the oracle data files are left in a backup mode.
set lines 132
col name format a50
select * from (
select f.name, to_char(b.time,'mm/dd hh24:mi'), b.status
from v$backup b, v$datafile f
where b.file# = f.file#
order by b.time desc)
where rownum < 11
/
====================================================================================================
set pages 300
set linesize 3000
select rpad(a.tablespace_name,20,'.')tablespace_name,
sum(a.bytes)/(1024*1024) Allocated,
sum(a.bytes)/(1024*1024) - max(nvl(b.space,0)) Used,
max(nvl(b.space,0)) Free,
max(nvl(b.cont,0))/(1024*1024) Contiguous
from dba_data_files a,
(select tablespace_name,sum(bytes)/(1024*1024) space,max(bytes) cont
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
group by a.tablespace_name order by TABLESPACE_NAME
/
====================================================================================================
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB",
decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB",
decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by tablespace_name
/
====================================================================================================
select index_name ,column_position colp,rtrim( column_name) colu
from user_ind_columns
where index_name in (
select index_name from user_indexes
where table_name = '&tname')
order by index_name,column_position
/
select index_type from user_indexes where index_name in ('IX_sms_content_1','IX_sms_content_2');
drop index "IX_sms_content_1";
create index "IX_sms_content_1" on "sms_content" (CUSTOMERID,FOLDERID,MESSAGECONTENTID) online compute statistics tablespace O2OM_DATA_INDEX_01;
====================================================================================================
execute dbms_stats.gather_table_stats (ownname => 'TAP_USER',tabname=>'TAPOUT',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8,cascade => TRUE);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'DBLUSGO', tabname => 'SERVICE_FEATURE_SNP', cascade => TRUE, estimate_percent => 5, method_opt => 'FOR ALL COLUMNS SIZE 1');
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'EPIMM',METHOD_OPT=>'FOR ALL INDEXED COLUMNS ',DEGREE=>8,CASCADE=>TRUE);
exec DBMS_JOB.BROKEN( job=>192, broken=>FALSE );
exec dbms_system.SET_SQL_TRACE_IN_SESSION('&SID','&SR',&TF);
exec statspack.snap (i_snap_level=>7)
exec DBMS_JOB.remove (212);
exec DBMS.JOB.RUN (214);
====================================================================================================
===========================================================================================================================
select table_name from dict where table_name like '%CONS%';
===========================================================================================================================
select 'GRANT EXECUTE ON '||OWNER||'.'||OBJECT_NAME||' TO CHS_READ;' FROM DBA_OBJECTS WHERE OWNER='DSSPRIVATE' AND OBJECT_TYPE ='PROCEDURE';
select 'GRANT SELECT,INSERT,UPDATE,DELETE ON '||OWNER||'."'||OBJECT_NAME||'" TO ROK_S;' FROM DBA_OBJECTS WHERE OWNER='O2OMDDL' AND OBJECT_TYPE IN ('TABLE');
select 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO MCIVER_F;' FROM DBA_OBJECTS WHERE OWNER='OADBA' AND OBJECT_TYPE IN ('TABLE');
select distinct owner from dba_segments where segment_name in ('MPPI_USERWALLET','MPPI_INSTRUMENT','MPPI_INSTRUMENT_TYPE','WALLET_PAYMENT_INSTRUMENT','MPPI_TRANSACTION','TOPUP_TRANSACTION','MPPI_AUDIT_USERGENERAL');
select 'GRANT SELECT ON '||OWNER||'.'||SEGMENT_NAME||' TO BI_DSTAGE_EXT;' FROM dba_segments where segment_name in ('MPPI_USERWALLET','MPPI_INSTRUMENT','MPPI_INSTRUMENT_TYPE','WALLET_PAYMENT_INSTRUMENT','MPPI_TRANSACTION','TOPUP_TRANSACTION','MPPI_AUDIT_USERGENERAL');
select FREE_SPACE,MAX_FREE_SIZE,USED_SPACE,AVG_USED_SIZE from v$shared_pool_reserved;
select 'exec DBMS_JOB.BROKEN (job==> '||JOB|| ',broken==>TRUE);' from dba_jobs where LOG_USER!='SYS;
select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||' ;' from dba_role_privs where GRANTEE='C_NALABOTHU_R';
select 'execute dbms_stats.gather_table_stats (ownname =>'||''''||OWNER||''''||',tabname=> '||''''||TABLE_NAME||''''||',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8,cascade => TRUE);' from dba_tables where OWNER='STAGING' and LAST_ANALYZED IS NULL;
select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;
select profile,resource_name,limit from dba_profiles order by profile;
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;
select table_name,constraint_name,status from dba_constraints where constraint_name='SYS_C008302';
select count(*),OBJECT_TYPE,owner from dba_objects where owner NOT IN ('SYSTEM','SYS','QUEST') group by OBJECT_TYPE,owner;
select owner,last_analyzed,num_rows from dba_tables where table_name=''
select bytes/1024/1024,segment_name from dba_segments where segment_type='INDEX' and owner='QUEST' order by 1;
select TABLESPACE_NAME,SEGMENT_NAME,EXTENTS from dba_segments where segment_name='AUD$';
select 'alter system kill session '||''''||sid||','||serial# ||''''||' immediate ;' from v$session where trunc(logon_time)<sysdate;
select sid||','||username||','||osuser||','||to_char(logon_time,'DD-MON-YY HH24:MI') from v$session where program like '%TOAD%' and logon_time < sysdate-4 order by logon_time;
select sid,serial#,logon_time from v$session where upper(program) like '%TOAD%';
select holding_session from dba_blockers a where not exists (select 'x' from dba_waiters b where b.waiting_session = a.holding_session)
select default_tablespace from dba_users ;
select username,account_status from dba_users where username like ''
select PRIVILEGE from dba_sys_privs where GRANTEE=MULLIGAN_N';
select GRANTED_ROLE from DBA_ROLE_PRIVS where GRANTEE='HSERVER';
select PRIVILEGE from dba_sys_privs where GRANTEE='HSERVER';
select sum(bytes/1024/1024) from dba_segments where owner='O2OMDML';
select * from v$access where object=''
select tablespace_name, username, bytes, max_bytes from dba_ts_quotas where username='MKALDAS';
select count(*),trunc(first_time) from v$log_history group by trunc(first_time);
select sum(bytes/1024/1024/1024) from dba_segments;
select sum(bytes/1024/1024) from dba_segments where segment_name='AUD$';
select sum(bytes/1024/1024) from dba_segments where owner='EPIMM';
select tablespace_name,bytes/1024/1024/1024 from sm$ts_avail order by 1;
select tablespace_name,bytes/1024/1024/1024 from sm$ts_used order by 1;
select tablespace_name,bytes/1024/1024/1024 from sm$ts_free order by 1;
select bytes/1024/1024 as mb,tablespace_name from sm$ts_free where tablespace_name='SYSTEM';
select bytes/1024/1024,file_name from dba_data_files where tablespace_name='UNDOTBS1'
select bytes/1024/1024 as mb,tablespace_name from sm$ts_avail;
select bytes/1024/1024 as mb,tablespace_name from sm$ts_used;
select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;
select sid,serial#,status,logon_time from v$session where program like 'rman%';
select count(*) from v$session where type='USER';
select count(*),trunc(logon_time) from v$session where type='USER' group by trunc(logon_time);
select fuzzy,count(*) from v$datafile_header group by fuzzy;
select sid,failures,last_date,this_date from dba_jobs_running where job in (' ');
select sid,serial#,username from v$session where lockwait is not null;
select * from gv$session where lockwait is not null;
select * from v$session where lockwait is not null;
s
select HOLDING_SESSION from dba_blockers where HOLDING_SESSION not in (select WAITING_SESSION from dba_waiters);
select inst_id from gv$instance;
select inst_id,username,count(*) from gv$session group by inst_id,username;
select hash_value from v$sql where sql_text like ' ';
select hash_value,sql_text from v$sqltext where hash_value = '&1' order by piece;
select * from gv$lock where block <1;
select TABLESPACE_NAME,
2 ONLINE_STATUS from dba_data_files where TABLESPACE_NAME='amo_msoffline_std';
select file_name from dba_temp_files;
select memeber from v$logfile;
select name from v$controlfile;
select open_mode from v$database;
select instance_name from v$instance;
select name from v$tablespace;
select name,status from v$datafile;
select temporary_tablespace from dba_users;
select * from dba_synonyms where owner='';
select username,owner,action_time,tiemstamp from dba_audit_objects where object_name='';
select max(sequence#) from v$log_history;
select host_name from v$instance;
select host from dba_db_links;
select dbid from v$database;
select * from registry$history;
select * from dba_registry;
select * from v$backup;
select * from v$recoverfile;
select distinct status from v$datafile;
select TEXT from dba_source where name='VERIFY_FUNCTION';
select * from V$PWFILE_USERS;
select * from ejbtimer;
===========================================================================================================================
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
alter user username identified by
alter database datafile '' offline drop;
alter database datafile 'PATH' resize ;
alter database open;
alter database default temporary tablespace '';
alter user QUEST default tablespace QUEST_INDEX;
alter tablespace UNDOTBS1 add datafile '/crmc1p_db17/oradata/undotbs05.dbf' size 2000m;
alter database backup controlfile to trace;
alter profile O2_USER_PROFILE limit PASSWORD_VERIFY_FUNCTION NULL
alter user SRIRAM_S PASSWORD EXPIRE;
ALTER USER C_NALABOTHU_R QUOTA 10M ON USR;
alter view STAGING.TD_D_SCORE_V compile;
CREATE INDEX "IX_sms_sentstate_3" ON "sms_sentstate"(CustomerID) TABLESPACE "O2OM_DATA_INDEX_01";
===========================================================================================================================
drop tablespace tablespace_name including contents and datafiles;
drop temporary tablespace tablespace_name including contents and datafiles;
drop user username;
drop user username cascade;
drop table tablename purge;
drop table tablename cascade constraints;
drop table tablename;
====================================================================================================
find tablespaces is online or offiline :
select tablespace_name, online_status from dba_data_files where tablespace_name='tablespace_name';
find tablespaces is autoexten, tbalespace name :
select file_name , tablespace_name, autoextensible from dba_data_files;
find the privilege in database;
select privilege||' '||table_name "Privileges for " from dba_tab_privs where grantee='&data'
union
select privilege from dba_sys_privs where grantee='&data'
union
select granted_role from dba_role_privs where grantee='&data';
continues ....
No comments:
Post a Comment