====== Oracle ======
* [[oraclescripts | Oracle Scripts]]
* [[Oracle login]]
* [[createdb| Create DB]]
* [[delete10g |Delete 10g db ]]
* [[ orafiles | ora files]]
To invoke Export as SYSDBA, use the following syntax, adding any desired parameters or parameter filenames:
exp \'username/password AS SYSDBA\'
Oracle is now **CaseSensitive**
Optionally, you could also specify an instance name:
exp \'username/password@instance AS SYSDBA\'
name of ayeone db is JAS10
exp PARFILE=filename
exp jwars/password PARFILE=filename
exp userid= "'sys/jwars@jas10 as sysdba'" full=y log=jas10exp.log**<---- dump jas10 on ayeone**
C:\Documents and Settings\dmm>exp userid=jwars/password@jas10 tables=(ENV_SCENAR
IO);
Export: Release 10.2.0.1.0 - Production on Sun Oct 2 11:32:52 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table ENV_SCENARIO 2 rows exported
EXP-00011: JWARS.; does not exist
Export terminated successfully with warnings.
C:\Documents and Settings\dmm>
>exp JWARS/PASSWORD@JAS10 file=new.dmp rows=n optionally tables=(someTableName)
-------------------------------------------------------------- note parens ^^^^^
====== Find tablespace usage ======
SQL> SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes, b.free_byte
s FROM dba_data_files a, (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_sp
ace b GROUP BY file_id) b WHERE a.file_id=b.file_id ORDER BY a.tablespace_name;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
ALLOCATED_BYTES FREE_BYTES
--------------- ----------
JWARS_ENVIRONMENT
C:\ORACLE\ORADATA\JAS10\JAS_ENVIRONMENT001.DBF
1.0486E+10 2192179200
10 alloc 2 gig free
JWARS_INDEX
C:\ORACLE\ORADATA\JAS10\JAS_INDEX001.DBF
1048576000 954793984
2 gigs
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
ALLOCATED_BYTES FREE_BYTES
--------------- ----------
JWARS_INSTRUMENTS
C:\ORACLE\ORADATA\JAS10\JAS_INSTRUMENTS001.DBF
8388608000 8388542464
8 gigs
JWARS_JAR
C:\ORACLE\ORADATA\JAS10\JAS_JAR001.DBF
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
ALLOCATED_BYTES FREE_BYTES
--------------- ----------
2097152000 2096234496
2 gigs
JWARS_PRIMARY
C:\ORACLE\ORADATA\JAS10\JAS_PRIMARY001.DBF
2097152000 1993408512
2 gigs
JWARS_SCENARIOCOMPONENT
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
ALLOCATED_BYTES FREE_BYTES
--------------- ----------
C:\ORACLE\ORADATA\JAS10\JAS_SCENARIOCOMPONENT001.DBF
2097152000 1398210560
2 gigs
SYSAUX
C:\ORACLE\ORADATA\JAS10\SYSAUX01.DBF
325058560 13041664
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
ALLOCATED_BYTES FREE_BYTES
--------------- ----------
SYSTEM
C:\ORACLE\ORADATA\JAS10\SYSTEM01.DBF
513802240 7929856
UNDOTBS1
C:\ORACLE\ORADATA\JAS10\UNDOTBS01.DBF
104857600 90112000
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
ALLOCATED_BYTES FREE_BYTES
--------------- ----------
USERS
C:\ORACLE\ORADATA\JAS10\USERS01.DBF
5242880 4784128
10 rows selected.
C:\temp>imp jwars/password@xe file=expat.dmp data_only=y
Import: Release 11.2.0.2.0 - Production on Sun Sep 25 18:15:01 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
IMP-00002: failed to open expat.dmp for read
Import file: EXPDAT.DMP >
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing JWARS's objects into JWARS
. . importing table "ENV_AREA" 2 rows imported
Import terminated successfully with warnings.
sqlplus jwars/password@xe works on chuck
sqlplus sys/jwrs@xe as sysdba works on chuck
SELECT table_name, num_rows FROM all_tables where owner in ('JWARS'); <--- count rows
C:\temp\jas\Oracle>exp JWARS/PASSWORD@XE file=dump.dmp log=dump.log <- dumps the jwars schema
XE here is the tns name; jwars put us in the jwars schema. i think.
====== Reset Password ======
C:\Windows\system32>sqlplus sys/jwars@xe as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sun Sep 25 17:26:42 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
SQL> alter user jwars account unlock;
User altered.
SQL> alter user jwars identified by password;
User altered.
====== Ayeone Jwars tables ======
C:\Documents and Settings\dmm>sqlplus jwars/password@jas10
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 19 09:58:19 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> SELECT owner, table_name FROM all_tables where owner in ('JWARS') ;
- JWARS EXCURSION_SCENARIO_COMPONENT
- JWARS JAR_TABLE
- JWARS JAR_TABLE_COLUMN
- JWARS EXCURSION_SCENARIO
- JWARS JWARS_SIMREPINFO
- JWARS JWARS_SIMRUNINFO
- JWARS JWARS_DB_INSTALL
- JWARS SCENARIO_COMPONENT_REF
- JWARS SCENARIO_COMPONENT_LOG
- JWARS SCENARIO
- JWARS SCENARIO_LOG
- JWARS SCENARIO_COMPONENT
- JWARS JWARS_PREFERENCES
- JWARS US_JWARS_LINS
- JWARS DESCRIPTOR_CLASSIFICATION
- JWARS DESCRIPTOR_RELATIONSHIPS
- JWARS DESCRIPTOR_DEFINITION
- JWARS DESCRIPTOR_ASSOCIATIONS
- JWARS JWARS_REPORTDEFINITIONS
- JWARS RUN_SETUP_COMPONENTS
- JWARS JAR_TABLE_STRUCTURE
- JWARS CHEMICAL_CLOUDS_TABLE
- JWARS CHEM_CLOUDS_MUNITION_AGENT
- JWARS COMM_MSG_TYPE
- JWARS COMM_MSG_TYPE_ASSOC
- JWARS MOB_CNTRY
- JWARS MOB_GEOFILE
- JWARS MOB_GEO_LOC_TYP
- JWARS MOB_STATE
- JWARS ENV_SEASON
- JWARS ENV_LVL
- JWARS ENV_PARAM
- JWARS ENV_AREA
- JWARS ENV_SCENARIO
- JWARS ENV_SCENARIO_PARAM
- JWARS ENV_SCENARIO_PARAM_LAYER
- JWARS ENV_SCENARIO_GRID
- JWARS ENV_SEASONAL_GRID
- JWARS ENV_STATIC_GRID
- JWARS TL_AREA
- JWARS TL_SCENARIO
- JWARS TRANMISSION_LOSS
- JWARS TL_TRACK_TO_LOSS_CURVE
- JWARS TL_LOCATION_TO_TRACK_GRID
- JWARS TL_LOC_TO_TRACK_TIME_INDEX
- JWARS TL_PARAMS_TO_TL_TIME_INDEX
- JWARS TL_LOCATION_TO_TRACK_ID_GRID
- JWARS ENV_REVERBERATION_AREA
- JWARS ENV_PROVINCE_GRID
- JWARS ENV_SS_VOLUME
- JWARS AMBIENT_NOISE_AREA
- JWARS AB_OMNI_NOISE
- JWARS AB_SPECTRAL_CURVE
- JWARS AB_SHIPPING_NOISE_GRID
- JWARS ENV_SHIPPING_NOISE_LEVEL_AREA
- JWARS ENV_SHIPPING_NOISE_LEVEL
- JWARS GEO_AREA_TRIANGULATION_REF_NEW
- JWARS GEO_AREA_TRIANGULATION_NEW
- JWARS GEO_AREA_TRIANGULATION_REF
- JWARS GEO_AREA_TRIANGULATION
- JWARS AAA
- JWARS JWARS_SIMREPINFO_HISTORY
- JWARS JWARS_SIMRUNINFO_HISTORY
- 63 rows selected.
**Table count**
* . about to export JWARS's tables via Conventional Path ...
* . . exporting table AAA 5 rows exported
* . . exporting table AB_OMNI_NOISE 1 rows exported
* . . exporting table AB_SHIPPING_NOISE_GRID 1 rows exported
* . . exporting table AB_SPECTRAL_CURVE 1 rows exported
* . . exporting table AMBIENT_NOISE_AREA 1 rows exported
* . . exporting table CHEMICAL_CLOUDS_TABLE 0 rows exported
* . . exporting table CHEM_CLOUDS_MUNITION_AGENT 0 rows exported
* . . exporting table COMM_MSG_TYPE 105 rows exported
* . . exporting table COMM_MSG_TYPE_ASSOC 0 rows exported
* . . exporting table DESCRIPTOR_ASSOCIATIONS 24408 rows exported
* . . exporting table DESCRIPTOR_CLASSIFICATION 1 rows exported
* . . exporting table DESCRIPTOR_DEFINITION 2191 rows exported
* . . exporting table DESCRIPTOR_RELATIONSHIPS 22 rows exported
* . . exporting table ENV_AREA 2 rows exported
* . . exporting table ENV_LVL 4 rows exported
* . . exporting table ENV_PARAM 97 rows exported
* . . exporting table ENV_PROVINCE_GRID 1 rows exported
* . . exporting table ENV_REVERBERATION_AREA 1 rows exported
* . . exporting table ENV_SCENARIO 2 rows exported
* . . exporting table ENV_SCENARIO_GRID 364599 rows exported
* . . exporting table ENV_SCENARIO_PARAM 116 rows exported
* . . exporting table ENV_SCENARIO_PARAM_LAYER 172 rows exported
* . . exporting table ENV_SEASON 4 rows exported
* . . exporting table ENV_SEASONAL_GRID 8 rows exported
* . . exporting table ENV_SHIPPING_NOISE_LEVEL 12 rows exported
* . . exporting table ENV_SHIPPING_NOISE_LEVEL_AREA 1 rows exported
* . . exporting table ENV_SS_VOLUME 172 rows exported
* . . exporting table ENV_STATIC_GRID 2 rows exported
* . . exporting table EXCURSION_SCENARIO 0 rows exported
* . . exporting table EXCURSION_SCENARIO_COMPONENT 0 rows exported
* . . exporting table GEO_AREA_TRIANGULATION 1 rows exported
* . . exporting table GEO_AREA_TRIANGULATION_NEW 9 rows exported
* . . exporting table GEO_AREA_TRIANGULATION_REF 1 rows exported
* . . exporting table GEO_AREA_TRIANGULATION_REF_NEW 9 rows exported
* . . exporting table JAR_TABLE 0 rows exported
* . . exporting table JAR_TABLE_COLUMN 0 rows exported
* . . exporting table JAR_TABLE_STRUCTURE 0 rows exported
* . . exporting table JWARS_DB_INSTALL 1 rows exported
* . . exporting table JWARS_PREFERENCES 179 rows exported
* . . exporting table JWARS_REPORTDEFINITIONS 186 rows exported
* . . exporting table JWARS_SIMREPINFO 0 rows exported
* . . exporting table JWARS_SIMREPINFO_HISTORY 17 rows exported
* . . exporting table JWARS_SIMRUNINFO 1 rows exported
* . . exporting table JWARS_SIMRUNINFO_HISTORY 23 rows exported
* . . exporting table MOB_CNTRY 0 rows exported
* . . exporting table MOB_GEOFILE 0 rows exported
* . . exporting table MOB_GEO_LOC_TYP 0 rows exported
* . . exporting table MOB_STATE 0 rows exported
* . . exporting table RUN_SETUP_COMPONENTS 775 rows exported
* . . exporting table SCENARIO 757 rows exported
* . . exporting table SCENARIO_COMPONENT 12708 rows exported
* . . exporting table SCENARIO_COMPONENT_LOG 2955 rows exported
* . . exporting table SCENARIO_COMPONENT_REF 44095 rows exported
* . . exporting table SCENARIO_LOG 1587 rows exported
* . . exporting table TL_AREA 1 rows exported
* . . exporting table TL_LOCATION_TO_TRACK_GRID 535 rows exported
* . . exporting table TL_LOCATION_TO_TRACK_ID_GRID 0 rows exported
* . . exporting table TL_LOC_TO_TRACK_TIME_INDEX 6 rows exported
* . . exporting table TL_PARAMS_TO_TL_TIME_INDEX 6 rows exported
* . . exporting table TL_SCENARIO 1 rows exported
* . . exporting table TL_TRACK_TO_LOSS_CURVE 6 rows exported
* . . exporting table TRANMISSION_LOSS 78 rows exported
* . . exporting table US_JWARS_LINS 0 rows exported
* . about to export JAR001's tables via Conventional Path ...
* . . exporting table ASSET_BIN 0 rows exported
* . . exporting table BSE_BIN 0 rows exported
* . . exporting table SCENARIO 0 rows exported
* . . exporting table SCENARIO_COMPONENT 0 rows exported
* . . exporting table SCENARIO_COMPONENT_REF 0 rows exported
* . exporting synonyms
* . exporting views
* . exporting referential integrity constraints
* . exporting stored procedures
* . exporting operators
* . exporting indextypes
* . exporting bitmap, functional and extensible indexes
* . exporting posttables actions
* . exporting triggers
* . exporting materialized views
* . exporting snapshot logs
* . exporting job queues
* . exporting refresh groups and children
* . exporting dimensions
* . exporting post-schema procedural objects and actions
* . exporting user history table
* . exporting default and system auditing options
* . exporting statistics
* Export terminated successfully without warnings.
*
* C:\temp>