Hot answers tagged

20

To backup: mysqldump -u user -p database > backup.sql To import: mysql -u user -p database < backup.sql


17

impdp will create the user if it's not present yet, so you don't have to worry about it unless that's not what you want. Do not run impdb or expdp as sysdba, only do that if Oracle support requests it in specific circumstances. Use an ordinary user for that - one that has been granted the dba role for instance. (There are the [IMPORT|EXPORT]_FULL_DATABASE ...


13

Ask and you shall receive. Try here for a basic intro to PostgreSQL and JSON. Also, if all else fails, try the documentation here. Check out the pretty_bool option. Your original question was "Is there a way to export postgres table data as JSON?". Fairly clear question to which I hope that I have provided a clear answer. From my first link: SELECT ...


11

No, it does not export indexes. Indexes are rebuilt upon loading the mysqldump back into mysql. The options you found "--disable-keys" cause the the mysqldump to write something like this before the table's load via INSERTs: DROP TABLE IF EXISTS `tblAccountLinks`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE ...


10

Tom Kyte has a number of different solutions to generating flat files from Oracle on his site. There is a PL/SQL implementation using UTL_FILE as well as a Pro*C SQL unloader application.


10

To be honest, this is easiest: backup/restore a copy locally remove unwanted data from the copy (with DELETE or TRUNCATE TABLE, not DROP...) ship the copy I wouldn't bother with filegroups because of the added complexity you noted...


10

You could do that in Oracle 11gR2 with expdp and the REMAP_DATA option. Create a function in a package that takes a blob as argument, and returns null (or an empty blob perhaps). Call expdp as usual, adding: REMAP_DATA=SCHEMA.TAB.BLOB_COLUM:SCHEMA.PACKAGE.YOUR_FUNCTION Short example (schema: mat): create or replace package remap as function ...


10

Please run this query: SELECT Data_BB / POWER(1024,1) Data_KB, Data_BB / POWER(1024,2) Data_MB, Data_BB / POWER(1024,3) Data_GB FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')) A; This will give you a ballpark figure. The column index_length is ...


9

Instead of export/import, you should use alter table t shrink space; which is much less dangerous & keeps the table even accessable during the reorganization. After the shrink, you may use below script (originally from Tom Kyte) to find out to what size you can resize (making them smaller) the datafile(s): set verify off column file_name format a50 ...


9

Yes, by using # at the start of a line. Actually everything after the # sign will be ignored, so this can be used after a parameter as well. This is a bit hidden in the manual: Table names specified on the command line cannot include a pound sign (#), unless the table name is enclosed in quotation marks. Similarly, in the parameter file, if a table ...


9

I'll answer this at a high level for you. The two backup methods work at different levels. An RMAN backup is a physical backup and a Data Pump backup is a logical backup. A database dump using expdp is a 1-time export of one or more database schemas. It backs up DDL (table structures, views, synonyms, stored procedures, packages, etc), plus data. An RMAN ...


9

Since your source server is SQL Server 2000, you can't restore directly to SQL Server 2012. You will need to restore to another instance first (2005, 2008, or 2008 R2). On 2000: BACKUP DATABASE dbname TO DISK = 'D:\backups\db.bak' WITH INIT; On 2005 or 2008 or 2008 R2: RESTORE DATABASE dbname FROM DISK = 'D:\restores\db.bak' WITH REPLACE, RECOVERY, ...


8

If you've got access to the original database, I'd go with DBMS_METADATA.GET_DDL. You can script it with UTL_FILE so that it goes through each object (from USER_OBJECTS), uses the name and type to extract the object then write it to a file that has the appropriate naming convention. It will be a lot cleaner than trying to split a single file.


8

There are many ways to export data from Oracle and automate the functionality. Be sure to understand exactly what the data export is being used for, though. If it is for interop between systems, then export in a format your receiving system can understand. If it is for backup purposes, go for the exp/expdp (data pump) method because a database backup needs ...


8

You should look at the built in UTL_FILE package. There are several ways you could use it. You could write any number of procedures in packages that use the UTL_FILE package to write to any number of files. These procedures can then be called from almost any application including SQL*Plus. You could write a PL/SQL script to do the same work and call the ...


8

You really just have to get creative. As we all know, there are many places that settings are stored, depending on what exactly you're looking to compare. For instance, to compare instance-wide configuration settings, you can simple do an EXCEPT query (you may have to create a linked server, or export/import the data depending on how you want to approach ...


8

Looping through all those articles is an option if you want to get old watching it execute. Some different options that you should try are: the bcp utility (mentioned above, too, it's a command line tool to import and export data to/from an SQL Server); BULK INSERT statement - the T-SQL language sugar to work with data imports from scripts; SqlBulkCopy ...


8

A .bacpac file = Schema + Data. Note that Data is BCP'ed out using Native format (not readable by Human). You can rename the .bacpac to .zip to see the actual contents. You can use ...DAC\bin\sqlpackage.exe commandline to extract the .bacpac contents programatically. It is a snapshot that includes User data + Schema from SQL Server or Azure SQL ...


7

There really is an abundance of documentation and tools to do MS SQL Server to MySQL migrations - likely there would be no need to re-invent the wheel. Exporting your database tables' data to CSV just to re-import it to MySQL seems like a rather bad idea since you are going to lose all metadata (data types, constraints, indexes, views, ...). Look at this ...


7

Rather than doing this, I'd advise on getting the character set info from the nls_database_parameters view & then using DBCA to create a new database, along with dbms_metadata.get_ddl to handle tablespace creation. - Much easier and less prone to error. The character set and national character set are really the only things that are a pain to change when ...


6

Ionice can limit disk usage Take a look at the ionice utility for linux, as it seems to suit your needs quite well.


6

Both the approaches already suggested appear to be unnecessarily complicated. Just use psql's built-in \copy command, which works just like server-side COPY but does a copy over the wire protocol to the client and uses client paths. Because it's a psql backslash command you omit the trailing semicolon, eg: \copy products TO '/tmp/products.csv' CSV ...


6

I'm sorry you haven't had a response since yesterday; here's at least a starting point for you. You can try pulling the pieces you need out of. As always, read the discussion threads (I was, regrettably, unable to find a script endorsed by the big names I recognize, so test thoroughly! Schema, object, server, and column level permissions are often ...


6

You could use Flashback to return the table data back to its original state. Is Flashback enabled? Database needs to be in archive mode: select log_mode from v$database; Should return "ARCHIVEMODE" Check if flashback is enabled: select flashback_on from v$database; Should return "YES" If "NO", you can enable flashback by runing: alter database ...


5

The way you speak of only needing the primary file group, it sounds like to me that you only want the database structure and a very small amount of data. I would suggest if you just want an update of the database structure (objects, tables, etc) to simply script the database out with all the objects. This can be done quickly and easily with PowerShell. Then ...


5

The reason you are having problems with dbms_metadata.get_ddl is that it outputs CLOBs which can be up to 4GB in size. By default, SQL*Plus and Oracle SQL Developer truncate long text so they don't trash the client with large gobs of text. It's very easy to override this behavior in SQL*Plus with a few SET commands and get clean DDL. The script you need ...


5

Yes, no problem. Use BCP (which is a command line tool) to export the data. Once you have the parameters the way you like, schedule this as a SQL Agent job.


5

Your method will work fine, I would pre-allocate the data file on the new database so it won't need to autogrow. A backup would work just as well as detaching. And the destination server will need the same or higher version of SQL.



Only top voted, non community-wiki answers of a minimum length are eligible