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 ^^^^^
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.
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.
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 © 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') ;
Table count