Trouble Shooting Script

create index IDX_COLMUN_NAME on FARES_INPUT_NATIONWIDE_V3(COLMUN_NAME);ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS1.DBF' AUTOEXTEND ON MAXSIZE UNLIMITEDALTER TABLESPACE "RDS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS4.DBF' SIZE 10000M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
/*/*/*/*/*/*11:48 AM 6/8/2007*/*/*/*/*/*/CREATE SMALLFILE TABLESPACE "FOREST" DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HLUD\FOREST.DBF' SIZE 200M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
drop index IDX_COLMUN_NAM;
create table Feb_19_FARES_INPUT_NATIONWIDE_V3 as select * from FARES_INPUT_NATIONWIDE_V3;
SELECT DBMS_METADATA.GET_DDL('INDEX',index_name) cr_index, index_name FROM user_INDEXES;SELECT dbms_metadata.get_ddl('INDEX', 'IDX_TBLTIMETRACKER_EMP_LOC') from dual;set long 600
select OWNER,object_name,object_type,status from dba_objects where status='INVALID' and OBJECT_TYPE <>'PROCEDURE';
ALTER TABLESPACE "RDS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS1.dbf' SIZE 2000M
ALTER TABLESPACE "RDS" DEFAULT STORAGE ( INITIAL 5M MAXEXTENTS UNLIMITED );
ALTER TABLESPACE "RDS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS1.dbf' SIZE 2000M
alter tablespace temp add tempfile 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\TEMP04.DBF' SIZE 9000M autoextend on ;
ALTER TABLESPACE "WH5847" DATAFILE 'D:\AAWORK\OSIIIDBF\WH5847_1.DBF' RESIZE 3000M;
ALTER DATABASE DATAFILE 'D:\AAWORK\OSIIIDBF\WVTES32.DBF' RESIZE 1000M
Wed Feb 21 11:36:23 2007Errors in file d:\oracle\product\10.1.0\admin\timeinc\udump\timeinc_ora_1708.trc:ORA-12571: TNS:packet writer failure
FARES_INPUT_NATIONWIDE_V3
ALTER TABLESPACE "TIMEINC" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED )
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TIMEINC\TIMEINC.DBF' AUTOEXTEND ON MAXSIZE UNLIMITED
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TIMEINC\TIMEINC.DBF' RESIZE 1000M/*/*/*/*/*/*/*/*/*/*/*/3:17 PM 2/22/2007/*/*/*/*/*/orakill rds 1416/*/*/**/*/*/*/**/*/*/*/*/*/*/*/3:17 PM 2/22/2007/*/*/*/
CREATE USER "TELCOMWMS" ENTIFIED BY "TELCOMWMS" DEFAULT TABLESPACE "WVTEST6" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT CONNECT,resource,dba TO "TELCOMWMS";GRANT "DBA" TO "TELCOMWMS";GRANT "RESOURCE" TO "TELCOMWMS";
CREATE TABLESPACE "WG5386" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\WG5386.dbf' SIZE 100M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER TABLESPACE "WX" ADD DATAFILE '/oradata/ilrpolydata/wx1.dbf.dbf' SIZE 500M MAXEXTENTS UNLIMITED;
ALTER TABLESPACE "WX" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "WG5386" DEFAULT TABLESPACE "WG5386";
/*/*/*/*/*/*/*/*3:17 PM 2/22/2007/*/*/*/*/*/*/*/*/*/select max(maxquerylen) from v$undostat where begin_time>sysdate-10;/*/*/*/*/*/*///*3:18 PM 2/22/2007/*/*/*/*/*/*/*/*/*/
select round(bytes/1024/1024) from dba_segments where segment_name='FARES_INPUT_NATIONWIDE_V3';
analyze table FARES_INPUT_NATIONWIDE_V3 compute statistics;
dbms_stats.gather_table_stats(RDS, 'FARES_INPUT_NATIONWIDE_V3', granularity =>'DEFAULT',estimate_percent =>1);
SELECT 'rm ' name FROM v$archived_log WHERE registrar='RFS' AND applied='YES' AND completion_time < ownname="">'MY_USER',tabname=>'MY_TABLE',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,DEGREE=>2);
In Oracle 10 It Automatic Gather Statistics for the table who is not analyzed.
/*/*/*/*/*/*/*/*/**/*/*/*//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
CREATE TABLESPACE "ATT" LOGGING DATAFILE 'D:\oracle\oradata\ilr\att1.dbf' SIZE 50M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "ATT" DEFAULT TABLESPACE "ATT";
ALTER TABLESPACE "WX" ADD DATAFILE '/oradata/ilrpolydata/wx1.dbf.dbf' SIZE 500M MAXEXTENTS UNLIMITED;
ALTER TABLESPACE "TEMP" ADD DATAFILE D:\ORACLE\ORADATA\ILR\TEMP02.DBF SIZE 500M MAXEXTENTS UNLIMITED autoextend on;
ALTER TABLESPACE "TEMP" ADD TEMPFILE 'D:\ORACLE\ORADATA\ILR\TEMP02.DBF' SIZE 500 M ;
ALTER TABLESPACE "ATT" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
CREATE TABLESPACE "att" LOGGING DATAFILE 'D:\aawork\osiiidbf\att.dbf' SIZE 5M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "att" DEFAULT TABLESPACE "att";
CREATE TABLESPACE "RDSTEST" LOGGING DATAFILE 'D:\aawork\osiiidbf\RDSTEST1.dbf' SIZE 7000M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "RDSTEST" DEFAULT TABLESPACE "RDSTEST";
ALTER DATABASE TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\TEMP02.DBF' OFFLINE
/*/*/*/*/*/*/*/*/*/12:07 PM 2/26/2007/*/*/*/**/*/*/**/*/*/*/*/**/*/*/*/*/CREATE TABLESPACE "RDS_INDEX" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS_INDEX.dbf' SIZE 100M DEFAULT STORAGE ( NEXT 5M MAXEXTENTS UNLIMITED )
/*/*/*/*/*/*
ALTER TABLESPACE "RDS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS1.DBF' SIZE 1000M AUTOEXTEND ON ;
ALTER TABLESPACE "RDS_INDEX" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS_INDEX2.DBF' SIZE 1000M AUTOEXTEND ON ;
/*/*/*/*/*/*
CREATE INDEX ord_customer_ix_demo ON orders (order_mode) nOSORT NOLOGGING;
tablespace rds_index

/*/*/*/*/*/*/*/*/*/*
i/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**/
CREATE PUBLIC DATABASE LINK ilr2databank CONNECT TO regp IDENTIFIED BY regp USING 'databank';
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**//*/*/*/*/
If you have access to the dba_users, then it's possible to brute force the password, how long it would take depends on how complicated it is.
Here is a list of brute force tools: http://www.red-database-security.com/whitepaper/oracle_password_cracker.html
Here is the best one: http://www.toolcrypt.org/index.html?orabf
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/ALTER SYSTEM SET pga_aggregate_target = 104857600 SCOPE=BOTHALTER SYSTEM DISCONNECT SESSION '158,3868' IMMEDIATE/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
exec dbms_stats.gather_table_stats(ownname='RDS',tabname='FARES_0207_WORK_V1')

DECLARE X NUMBER;BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'DELETE TBLFINALDIC;COMMIT;' ,next_date => TO_DATE('13/03/2007 12:28:15','dd/mm/yyyy hh24:mi:ss') ,INTERVAL => 'SYSDATE+05/1440 ' ,no_parse => TRUE );END;/
begin
dbms_stats.gather_schema_stats( ownname=> 'RDS', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO',options=> 'GATHER AUTO');
end;
/*/*/*/*/*5:44 PM 3/14/2007/*/*/*/*/*
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*Recover table from recyclebin /*/*/*/*/*/*/*/*/*/*/*/*/*/*
select * from user_recyclebin;
Flashback table "%^^%$^$##32333" to before drop;
ALTER TABLE TBLBUILDINGHISTORY_QC ENABLE ROW MOVEMENT;
FLASHBACK TABLE TBLBUILDINGHISTORY_QC TO TIMESTAMP to_timestamp('2007-12-14 05:00:00 PM', 'YYYY-MM-DD HH:MI:SS AM');
FLASHBACK TABLE DC_ENTRIES TO TIMESTAMP to_timestamp('2008-03-01 07:00:00 PM', 'YYYY-MM-DD HH:MI:SS AM');
196 rows selected.
SQL> create table TBLBUILDINGHISTORY_QC_1 as select * from TBLBUILDINGHISTORY_QC;
Table created.
SQL> ALTER TABLE TBLBUILDINGHISTORY_QC ENABLE ROW MOVEMENT;
Table altered.
SQL> FLASHBACK TABLE TBLBUILDINGHISTORY_QC TO TIMESTAMP to_timestamp('2007-12-14 05:00:00 PM', 'YYYY-MM-DD HH:MI:SS AM');
ALTER TABLE rds_photo.TBLRMSIFILEINFO ENABLE ROW MOVEMENT
ALTER TABLE timeinc.pra_folioinfo ENABLE ROW MOVEMENT;
ALTER TABLE RDS_RS.TBLFINALINFOTEST ENABLE ROW MOVEMENT;
FLASHBACK TABLE RDS_RS."BIN$O7606UdjIgPgRAAUTz/7kA==$0" TO BEFORE DROP
FLASHBACK TABLE RDS_PHOTO.TBLRMSIFILEINFO TO TIMESTAMP t5:56 PMo_timestamp('2007-03-21 11:30:05 AM', 'YYYY-MM-DD HH:MI:SS AM');FLASHBACK TABLE sde.TBLSTATUS TO TIMESTAMP to_timestamp('2007--13 11:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM');
select versions_startscn, to_char(versions_starttime, 'YYYY-MM-DD HH:MI:SS AM'), versions_xid, versions_operation, USERRIGHTS from RDS_TEST.BKP_TEST versions between scn minvalue and maxvalue where userrights=5 and versions_startscn is not null order by versions_startscn desc
select versions_startscn, to_char(versions_starttime, 'YYYY-MM-DD HH:MI:SS AM'), versions_xid, versions_operation, USERRIGHTS from RDS_TEST.BKP_TEST versions between scn minvalue and maxvalue where userrights=5 and versions_startscn is not null order by versions_startscn desc;
FLASHBACK TABLE RDS_TEST.BKP_TEST TO SCN 4325768411
FLASHBACK TABLE RDS_TEST.BKP_TEST TO TIMESTAMP to_timestamp('2007-04-07 03:01:05 PM', 'YYYY-MM-DD HH:MI:SS AM')FLASHBACK TABLE RDS_TEST.BKP_TEST TO TIMESTAMP to_timestamp('2007-04-07 04:15:36 PM', 'YYYY-MM-DD HH:MI:SS AM')FLASHBACK TABLE timeinc.pra_folioinfo TO TIMESTAMP to_timestamp('2007-11-22 23:30:00 PM', 'YYYY-MM-DD HH:MI:SS AM')
FLASHBACK TABLE RDS.TBLPOSTCLEANINGLOG, RDS.TBLPRECLEANINGLOG TO TIMESTAMP to_timestamp('2007-11-19 09:00:47', 'YYYY-MM-DD HH24:MI:SS')
ALTER TABLE RDS.TBLPOSTCLEANINGLOG ENABLE ROW MOVEMENT;ALTER TABLE RDS.TBLPRECLEANINGLOG ENABLE ROW MOVEMENT;impdp rds/rds@rds directory=rds_backup dumpfile=EXP_RDS_MON_MORNING.DMP tables=rds.FARES_SC_KY_0207_V3 cONTENT=DATA_ONLY
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*Flashback table "BIN$OD/gAPw9RwqZPUNt6B4z5w==$0" to before drop;Flashback table "BIN$n4V4DqACSlajRt8zXSQiNA==$0" to before drop;Flashback table "BIN$mw6Gq8vmSSaKXZszSwZBmg==$0" to before drop;Flashback table "BIN$kJnBEQnkQVSMsJuAdJFoFw==$0" to before drop;Flashback table "BIN$zijqO4wMQyOwt+iZGxHa5A==$0" to before drop;Flashback table "BIN$k3z2akzmQAKSBOElpsAoIg==$0" to before drop;Flashback table "BIN$Q/EsIWP9RAixKtEnOdMlaQ==$0" to before drop;Flashback table "BIN$l/3q25hKR9ixexbKY0k68A==$0" to before drop;Flashback table "BIN$MzW5BHK6RpSlFbq1nB1e6g==$0" to before drop;Flashback table "BIN$7AHt0aeWRY2UZ/+UAw86MA==$0" to before drop;Flashback table "BIN$/6lHeRUvTeGAsovq8NNcVw==$0" to before drop;Flashback table "BIN$F7k9GY45RZ611FCX0Deejw==$0" to before drop;Flashback table "BIN$c10hh6E2T1WgccbGB6D/Ug==$0" to before drop;Flashback table "BIN$DyUzVpAvTCyMtEema0nD3A==$0" to before drop;Flashback table "BIN$Vow8HBfDTuGMSIG70WlRmw==$0" to before drop;Flashback table "BIN$McQcdSZrTpWQU2uuGoOrjw==$0" to before drop;Flashback table "BIN$l/M5pYh6TgCG5qLg4FG1Hg==$0" to before drop;Flashback table "BIN$Inz27n++Svie2QyWdAC+5g==$0" to before drop;Flashback table "BIN$Fj01j+4jRcaIxjkQakWCjQ==$0" to before drop;/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/
declarecmd varchar2(400);begincmd := 'create index RDS.IDX$_00001 on RDS.SANBORN_BLDG_ALT_ADD("ROWNO")';EXECUTE IMMEDIATE cmd;END;
/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/**/*/*/**/*/*/12:33 PM 3/19/2007/*/*/*/*/*/*/
CREATE TABLESPACE "IR_WATER" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\IR_WATER.DBF' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
CREATE TABLESPACE "IR_WATER" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\IR_WATER.DBF' SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
ALTER USER "IR_WATER" DEFAULT TABLESPACE "IR_WATER"
/*/*/*/*/*/*/*
CREATE USER "IR_WATER" PROFILE "DEFAULT" IDENTIFIED BY "*******" ACCOUNT UNLOCKGRANT "CONNECT" TO "IR_WATER"
ALTER USER "IR_WATER" DEFAULT TABLESPACE "IR_WATER"
ALTER SYSTEM KILL SESSION '112,1250' IMMEDIATE;
CREATE USER "ENBRIDGE" PROFILE "DEFAULT" IDENTIFIED BY "enbridge" DEFAULT TABLESPACE "USERS" ACCOUNT UNLOCK;GRANT "CONNECT" TO "ENBRIDGE";GRANT "DBA" TO "ENBRIDGE";GRANT "RESOURCE" TO "ENBRIDGE";
CREATE TABLESPACE "ENBRIDGE" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\ENBRIDGE1.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
select 'create index' ' ' INDEX_NAME ' ' 'on' ' ' TABLE_NAME'('COLUMN_NAME')'';' from user_ind_columns where INDEX_NAME not like '%$%';select 'ALter index' ' ' INDEX_NAME ' Rebuild'';' from user_ind_columns where INDEX_NAME not like '%$%';
ALTER SYSTEM SET sga_max_size = 1468006400 SCOPE=SPFILE;ALTER SYSTEM SET pga_aggregate_target = 130023424 SCOPE=BOTH;ALTER SYSTEM SET shared_pool_size = 423624704 SCOPE=BOTH;select CONSTRAINT_NAME , CONSTRAINT_TYPE , R_CONSTRAINT_NAME,TABLE_NAME from user_constraints where table_name not like '%$%' and R_CONSTRAINT_NAME is not null order by table_name;
select a.owner,a.table_name,a.constraint_name,b.table_name,b.constraint_name from all_constraints a,user_constraints b where a.constraint_name=b.r_constraint_name and b.table_name='&CHILD_TABLE' ;
select a.owner,a.table_name,a.constraint_name,b.table_name,b.constraint_name from all_constraints a,user_constraints b where a.constraint_name=b.r_constraint_name and b.constraint_name='&FOREIGN_KEY'
GRANT SELECT ON "PHI"."TBLNEWPROJECT" TO "SUSER";GRANT SELECT ON "PHI"."TBLUSER" TO "SUSER";
CREATE USER "APPDEV" PROFILE "DEFAULT" IDENTIFIED BY "appdev" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;GRANT "CONNECT" TO "APPDEV";GRANT "DBA" TO "APPDEV";GRANT "RESOURCE" TO "APPDEV";/*/*/*/*/*/*/
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\DUMP_NEW2.DBF' RESIZE 10000M

CREATE TABLESPACE "EEE" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\EEE.ora' SIZE 5M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED TEMPORARY
CREATE TEMPORARY TABLESPACE dump_new_temp tempfile 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\dump_new_temp1.dbf' SIZE 1000M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED ;
ALTER USER "DUMP_NEW" DEFAULT TABLESPACE "DUMP_NEW" TEMPORARY TABLESPACE "DUMP_NEW_TEMP"/*/*/*/*/6:19 PM 4/5/2007/*/*/*/*/*/*/SQLNET.AUTHENTICATION_SERVICES= (NTS)
ALTER USER "RDS_TEST" DEFAULT TABLESPACE "RDS_TEST" TEMPORARY TABLESPACE "TEMP_RDS"/*/*/*/*/*/*/*/*/*/dbms_stats.gather_index_stats(ownname => 'DUMP_DATA', indname => 'BIN$ys9ufsdaQTWogIsk6x1UaQ==$0', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
ALTER DATABASE TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\TEMP.DBF' RESIZE 4000M;
ALTER DATABASE TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\TEMP.DBF' AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED;
ALTER TABLESPACE "TEMP" ADD TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\temp2.dbf' SIZE 1000M AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED;
/*/*/*
begin
dbms_stats.gather_schema_stats( ownname=> 'RDS', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDSUSER', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_FARES', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_PARTY', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_PHOTO', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_REMOTE', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
end;
/*/*/*/*/*/*/*/*///*/*/*/*11:30 AM 4/11/2007/*/*//*/*/*/*CREATE USER "SB_DB" PROFILE "DEFAULT" IDENTIFIED BY "sb_db" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;GRANT "CONNECT" TO "SB_DB";GRANT "DBA" TO "SB_DB";GRANT "RESOURCE" TO "SB_DB";/*/*/*/*/*/*/*/*/*/*/*/*/*CREATE TABLESPACE "SB_DB" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\SB_DB.dbf' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;/*/*/*/*/*/*/
ALTER SYSTEM SET db_cache_size = 738197504 SCOPE=BOTHALTER SYSTEM SET shared_pool_size = 423624704 SCOPE=BOTH
CREATE SMALLFILE TABLESPACE "RDS_BAVS" DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS_BAVS.DBF' SIZE 10000M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO BEGIN DBMS_SERVER_ALERT.SET_THRESHOLD(9000,NULL,NULL,NULL,NULL,1,1,NULL,5,'RDS_BAVS'); END;
ALTER USER "RDS_BAVS" DEFAULT TABLESPACE "RDS_BAVS" TEMPORARY TABLESPACE "TEMP_RDS"
/*/*/*/*//*/*/*/*/*//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*//*
ALTER TABLESPACE "RDS_BAVS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\rds_bavs1.dbf' SIZE 10000M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
begindbms_stats.gather_table_stats(ownname => 'RDS_BAVS', tabname => 'CLIENT_DATA2', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);dbms_stats.gather_table_stats(ownname => 'RDS_BAVS', tabname => 'CLIENT_DATA2_DUP1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
ALTER TABLE RDS_BAVS.CLIENT_DATA2_TEMP ADD CONSTRAINT PK_SNO_CLIENT_DATA2_TEMP PRIMARY KEY (SNO)end;/*/*/*/**/*/*/*/*/**/*/*/*/*/*/*/9:47 AM 4/15/2007/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
CREATE PUBLIC DATABASE LINK RDS_DATAVALCONNECT TO RDSIDENTIFIED BY RDSUSING 'dataval';
/*/*/*/*/*/*/**/*/*/*/*/*/*/9:59 AM 4/15/2007/*/*/*/*/
impdp system/sys@pilot directory=rds_backup dumpfile=EXP_RDS_WED_MORNING.DMP tables="USSTATE" REMAP_SCHEMA=RDS_PHOTO:wg5386
select OWNER,NEXT_RUN_DATE , JOB_NAME, JOB_TYPE from dba_scheduler_jobs;
/*/*/*/**/*/*/*/*/**/*/*/*/*/*/*/12:19 PM 4/15/2007/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
oradim.exe -edit -SID ilrpoly -startmode auto -srvcstart system
You will have to edit you oracle service by using oradim and execute this command:
oradim.exe -edit -sid (YOUR SID) -startmode auto -srvcstart system
Or even delete your current oracle service and create a new one:
Example: Delete oracle service: oradim -del -sid (your sid)
Creation:
oradim.exe -new -sid (YOUR SID) -startmode manual -spfile oradim.exe -edit -sid (YOUR SID) -startmode auto -srvcstart system oradim.exe -edit -sid osiiigc1 -startmode auto -srvcstart system oradim.exe -edit -sid OMS -startmode auto -srvcstart system
Now your database will start after rebooting your windows server.
Kind regards
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/select * from dba_scheduler_jobs;
stop the job: begin dbms_scheduler.stop_job( 'HLUDLAYER2.NEW_STATUS_REPORT' , TRUE); end; /
remove the job: begin DBMS_JOB.REMOVE(id); end; /
BEGINsys.dbms_scheduler.set_attribute( name => '"SYS"."DAILY_PURGE_SCHEDULE"', attribute => 'repeat_interval', value => 'FREQ=DAILY;BYHOUR=3;BYMINUTE=0;BYSECOND=0'); END;
DBMS_SCHEDULER.disable (name => 'HLUDLAYER2.NEW_STATUS_REPORT');
Elapsed: 00:00:00.0011:33:20 SQL> exec DBMS_SCHEDULER.disable (name => 'HLUDLAYER1.NEW_STATUS_REPORT');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.0011:33:48 SQL> exec DBMS_SCHEDULER.disable (name => 'HLUDLAYER3.NEW_STATUS_REPORT');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.0211:34:00 SQL> exec DBMS_SCHEDULER.disable (name => 'HLUDLAYER4.NEW_STATUS_REPORT');
PL/SQL procedure successfully completed.
exec DBMS_SCHEDULER.disable (name => 'TIMEINC.UJB_UPDATE_TIME_INVENTORY');
exec DBMS_SCHEDULER.enable (name => 'TIMEINC.UJB_UPDATE_TIME_INVENTORY');
select dbms_xmlquery.getxml('select * from emp where rownum <= 3') from dual;DBMS_XMLQUERY.GETXML('SELECT*FROMEMPWHEREROWNUM<=3') /*/*/*/*/*/*/*/*/*/*/*/*/*/***/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/exec dbms_stats.gather_table_stats('RDS_BAVS','FARES_FINAL1')create index idx_ADDRESS_fares_f on FARES_FINAL35381098(trim(ADDRESS)) tablespaces rds_index NOLOGGING;/*/*/*/*/*/*/*/*/*/*/8*//**/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/Consider gathering system statistics with sql > exec dbms_stats.gather_system_stats('start'); after a couple of hours with representative db activity sql > exec dbms_stats.gather_system_stats('stop');
create index idx_ADDRESS_fares_final2 on FARES_FINAL2(trim(ADDRESS)) tablespace rds_index nologging;
update --+ index(idx_ADDRESS_fares_final2) FARES_FINAL2 set address=trim(address) where
CREATE BITMAP INDEX IDX_property_state ON FARES_FINAL2(property_state) tablespace rds_index nologging;
CREATE BITMAP INDEX IDX_property_state ON FARES_FINAL2(property_state) tablespace rds_index nologging;/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
begindbms_stats.gather_table_stats(ownname => 'DUMP_NEW', tabname => 'LOCCVG', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);dbms_stats.gather_table_stats(ownname => 'DUMP_NEW', tabname => 'TBLMDF_RECORD', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);dbms_stats.gather_table_stats(ownname => 'DUMP_NEW', tabname => 'EDM_MASTER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);end;exec dbms_stats.gather_index_stats('DUMP_NEW','IDX_LOCID_LOCCVG')exec dbms_stats.gather_index_stats('DUMP_NEW','IDX_MDF_UID_LOCCVG')exec dbms_stats.gather_index_stats('DUMP_NEW','IDX_MDF_UID')exec dbms_stats.gather_index_stats('DUMP_NEW','idx_labelid_loccvg')
drop index IDX_LOCID_LOCCVG; drop IDX_MDF_UID_LOCCVG; drop IDX_MDF_UID;
create bitmap index IDX_LOCID_LOCCVG on LOCCVG (LOCID);create bitmap index IDX_MDF_UID_LOCCVG on LOCCVG (MDF_UID);create bitmap index IDX_MDF_UID on EDM_MASTER (MDF_UID);opt/oracle/oracle/product/10.2.0/db_2/rdbms/admin/@/opt/oracle/oracle/product/10.2.0/db_2/rdbms/admin/spreport.sql
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*Query To know how many update is being Done.
select c.username,a.sid,b.name,a.value,c.LOGON_TIME,c.OSUSER,c.status from v$sesstat a,v$statname b, v$session c whereb.statistic#=a.statistic# and b.statistic#=191 and a.sid=c.sid and c.type!='BACKGROUND';
SELECT sofar, totalwork FROM v$session_longops/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/***/*****/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
SELECT substr(OS_USERNAME,1,20) , substr(USERHOST,1,25) , ACTION_NAME,TO_CHAR(TIMESTAMP,'DD-MON-YY hh:MI:SS') FROM DBA_AUDIT_TRAIL;SELECT substr(owner,1,10), substr(OS_USERNAME,1,10) , substr(USERHOST,1,15) , substr(ACTION_NAME,1,15),substr(OBJ_NAME,1,35),TO_CHAR(TIMESTAMP,'DD-MON-YY hh:MI:SS') FROM DBA_AUDIT_TRAIL where action_name like '%TABLE%' and obj_name not like '%$%'
col OBJ_NAME format a25col OS_USERNAME format a15col ACTION_NAME format a15`col USERHOST format a15col username format a15delete from sys.aud$ where USERID='DBSNMP';SELECT username,OS_USERNAME,USERHOST,OBJ_NAME,ACTION_NAME,EXTENDED_TIMESTAMP FROM DBA_AUDIT_TRAIL;SELECT username,OS_USERNAME,USERHOST,OBJ_NAME,ACTION_NAME,EXTENDED_TIMESTAMP FROM uks_sys_audit where ACTION_NAME like '%USER%';select USERNAME,USER_ID,PASSWORD,LOCK_DATE from DBA_USERS order by username;SELECT OS_USERNAME , USERHOST , ACTION_NAME,EXTENDED_TIMESTAMP FROM DBA_AUDIT_TRAIL;AUDIT UPDATE table,INSERT TABLE , DELETE TABLE BY sde BY ACCESS;Audit update , delete , insert on phi.tbluser by access whenever successful;audit drop any table,create any table,alter any table,truncate any table by rds by access;audit drop any table,create any table,alter any table,truncate any table by dump_data by access;NOAUDIT UPDATE table,INSERT TABLE , DELETE TABLE BY phmap BY ACCESS;NOaudit drop any table,create any table,alter any table,truncate any table by phmap by access;noaudit drop any table,create any table,alter any table by rds_bavs by access;audit drop any table,create any table,alter any table by rds by access;audit table;audit ALTER DATABASE;Audit succeeded.Elapsed: 00:00:0000.00delete from sys.aud$ where TERMINAL='CP658GIS';delete from aud$ where USERHOST='Earth';truncate table sys.aud$;
12:28:31 SQL> audit drop any table,create any table by rds_bavs by access;Audit succeeded.Elapsed: 00:00:00.0012:28:44 SQL> show userUSER is "SYS"
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/alter system set audit_trail='db' scope=spfile;
ALTER SYSTEM SET pga_aggregate_target = 207618048 SCOPE=BOTH;
REVOKE "DBA" FROM "RMSIPMS";ALTER USER "RMSIPMS" DEFAULT ROLE ALL
/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*//*/*/*/*/*/*http://cp763gis.rmsi.com:5500/emCREATE USER "VISION" PROFILE "DEFAULT" IDENTIFIED BY "*******" ACCOUNT UNLOCKGRANT "CONNECT" TO "VISION"CREATE SMALLFILE TABLESPACE "VISION" DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\OSIIIGC1\VISION.DBF' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO BEGIN DBMS_SERVER_ALERT.SET_THRESHOLD(9000,NULL,NULL,NULL,NULL,1,1,NULL,5,'VISION'); END;
emca -config dbcontrol db -repos create
dbms_stats.gather_index_stats(ownname => 'DUMP_DATA', indname => 'IDX_FLGPROCESS_TBLMFD_RECORD', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
ALTER DATABASE ADD LOGFILE GROUP 5 ( '/opt/oracle/oradata/ilrpoly/log5.ora') SIZE 512000KALTER DATABASE ADD LOGFILE GROUP 7 ( '/opt/oracle/oradata/ilrpoly/redo7.log')SIZE 512000KALTER SYSTEM SET log_buffer=2048576 SCOPE=SPFILE;
Recover database until cancel using backup controlfile;alter database open RESETLOGS; should be after the database is mounted.ALTER SYSTEM KILL SESSION '44,22298' IMMEDIATE
alter system flush shared_pool;
select /*+ parallel(
,4) */ count(1) from

;
/*************************** Space occupied by any segment Name ***********************/select TABLESPACE_NAME , to_char (sum (BYTES/(1024*1024*1024)),099999999.99999999) as "SPACE IN GB" from dba_data_files group by TABLESPACE_NAME;select table_name ,(BLOCKS*8)/(1024*1024) "Space In Gb" from dba_tables where OWNER='DUMP_DATA' order by table_name;select SEGMENT_NAME, SEGMENT_TYPE,BYTES/(1024*1024*1024) from DBA_segments where TABLESPACE_NAME like '%DATA%';SEGMENT_NAME, SEGMENT_TYPE,BYTES/(1024*1024*1024) from DBA_segments where TABLESPACE_NAME like '%DATA%' and SEGMENT_TYPE='TABLE' order by BYTES/(1024*1024*1024);
/*/*/*/*/*/*create user veer identified by veer;grant connect, resource, dba to veer;ALTER USER "VEER" DEFAULT TABLESPACE "VEER";/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/Consider running the Access Advisor to improve the physical schema design or creating the recommended index.CE.TBLTOWNLANDPOLY_PROD(TO_NUMBER("TOWNLANDID"))declarecmd varchar2(400);sname varchar2(400);begincmd := 'create index CE.IDX$$_8BA80001 on CE.TBLCOUNTY_TOWNLAND("OBJECTID")';EXECUTE IMMEDIATE cmd;END;/*/*/*/*/*/*/*/*/*/*/*/1:47 PM 5/28/2007Create sequence */*/*
GRANT CREATE ANY SEQUENCE TO "LH"GRANT DROP ANY SEQUENCE TO "LH"
CREATE SEQUENCE "LH"."TBLTOWNLANDPOLY_PROD_SEQ" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE NOCACHE NOORDER;/*/*/*/*/*/*/*/*/*/*/*ROW CHAIN */*/*/*/*/*/*/create table CHAINED_ROWS ( owner_name varchar2(30), table_name varchar2(30), cluster_name varchar2(30), partition_name varchar2(30), subpartition_name varchar2(30), head_rowid rowid, analyze_timestamp date);/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/6:19 PM 6/4/2007*/*/*/*/
Net config Assistance
NETCA/*/*/****/*/*/*/*/*/*/*/*/**/*/*/*/*LOG BUFFER*/*/*/*/*/*/*/Select name, value from v$sysstat Where name = 'redo log space requests';LOG_BUFFER =alter system set log_buffer=4000000 scope=spfile;Select name, value from v$sysstatWhere name in ('redo log space requests', 'redo log space wait time');/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/****/*/*/*/*/*/*/*/***/**/*/**/*/*/*/*/*/*/begin
dbms_stats.gather_schema_stats(ownname=> '"FOREST"' , options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> '"HLUD"' , options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> '"HLUDLAYER1"' , options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> '"HLUDLAYER2"' , options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> '"HLUDLAYER3"' , options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> '"HLUDLAYER4"' , options=> 'GATHER AUTO');end;/*/*/*/*/*/*/*/*/*/ Write on XML and XLs*/*/*/**/*/*/*/*/*/*/*/*/*/***/*/*/*/*/*/*/*/*/**/*/*/*/*/SET MARKUP HTML ON spool d:\dept.xlsdesc TBLTEMP_MANISHA spool offSET MARKUP HTML off
/*/*/**/*/*/*/*/*/*/*/*/*/*/**/*/*/*/@ opt/oracle/oracle/product/10.2.0/db_2/rdbms/admin/spreport.sql/**/**/*/*/*/*/**/*/Shrink the table./*/*/*/*/*/*/*/*/*/alter table "SL"."TBLLINE_DETAILS" enable row movement;alter table "SL"."TBLLINE_DETAILS" shrink space;***************************************************************************************************SET UNDERLINE OFFSET COLSEP ','SET LINES 100 PAGES 100SET FEEDBACK off--(If you don’t want column headings in CSV file)SET HEADING off Spool C:\Export\EMP.csv--Now the actual querySELECT * FROM EMP;Spool OFF***************************************************************************************************SELECT 'PGA: pid 'p.spid pid,p.pga_alloc_mem bytes, p.username ps_user,p.program ps_program,s.logon_time,s.sid,s.serial#,s.username,s.machine,s.osuser,s.programFROM v$session s ,v$sesstat pcur,v$process p WHERE pcur.statistic#=20 AND s.paddr=p.addr AND pcur.sid=s.sid UNION ALL SELECT 'SGA: 'name pid,value bytes,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM v$sgaORDER BY 2 DESC;****************************************************************************************************Where 'X' is the number of the job to be removed. Transfering Stats It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA:
SQL> EXEC DBMS_Stats.Create_Stat_Table('DBASCHEMA','STATS_TABLE'); SQL> EXEC DBMS_Stats.Export_Schema_Stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
This table can then be transfered to another server using your preferred method (Export/Import, SQLPlus Copy etc.) and the stats imported into the data dictionary as follows:
SQL> EXEC DBMS_Stats.Import_Schema_Stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA'); SQL> EXEC DBMS_Stats.Drop_Stat_Table('DBASCHEMA','STATS_TABLE'); ***************************************************************************************************select substr(HOST_NAME,1,10) Host_name ,INSTANCE_NAME,STARTUP_TIME, SHUTDOWN_PENDING, DATABASE_STATUS from v$instance;
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create select sum(waits)*100/sum(gets) "Ratio" , sum(waits) "WAITS" , sum(gets) "GETS" from v$rollstat;
select 'alter system kill session ''' SID ',' SERIAL# ''';', USERNAME , MACHINE,substr (program,1,100) from v$session where machine ='cp900gis';
select 'alter system kill session ''' SID ',' SERIAL# ''';' from v$session where machine ='cp900gis';select segment_name, bytes/1024/1024,blocks from user_segments;
select owner ,SEGMENT_NAME , BYTES/(1024*1024) from dba_segments where OWNER not in ('SYS','SYSTEM','SYSMAN','WKSYS','MDSYS','WMSYS','DBSNMP','OLAPSYS','XDB','ORDSYS') and SEGMENT_NAME not like '%$%' and segment_type='TABLE' order by owner;
select table_name,(num_rows*avg_row_len)/(1024*1024) MB from user_tables where table_name='T1';
select sum(BYTES/(1024*1024*1024)) from dba_data_files;
select USERNAME,substr(MACHINE,1,25),substr (program,1,100) from v$session;select SID,serial#,status,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS'),substr(USERNAME,1,10),substr(MACHINE,1,25),substr (program,1,24),osuser from v$session where username is not null order by osuser;
select substr(index_name,1,50),substr(table_name,1,50),substr(column_name,1,50) from user_ind_columns where index_name not like '%$%' order by table_name;**********************1:50 PM 1/7/2008*************************************select substr(TABLE_NAME,1,50) , substr(COLUMN_NAME,1,50) , substr( INDEX_NAME,1,50) from user_ind_columns;select TABLE_NAME,COLUMN_NAME from user_tab_columns where COLUMN_NAME like '%GEO%' order by COLUMN_NAME;
select substr(TABLE_NAME,1,50) , substr(COLUMN_NAME,1,50) , substr( INDEX_NAME,1,50) from user_ind_columns;select TABLE_NAME,COLUMN_NAME from user_tab_columns where COLUMN_NAME like '%GEO%' and TABLE_NAME not in (select TABLE_NAME from user_ind_columns);
select 'SELECT dbms_metadata.get_ddl(''TABLESPACE'',' ''''username''''')'' from dual;' from dba_users ;
set long 100000SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE',u.TABLESPACE_NAME) FROM dba_tablespaces u;